在ACCESS中用VBA代码直接读取Excel表中的数据

Foresttu

已于 2023-10-09 09:38:35 修改

阅读量2.6k

点赞数 1

分类专栏：笔记文章标签：经验分享

于 2023-10-08 11:16:09 首次发布

本文链接：https://blog.youkuaiyun.com/Foresttu/article/details/133673281

版权

笔记专栏收录该内容

19 篇文章

订阅专栏

在Microsoft Access中，可以使用ADODB对象来连接Excel表并读取其中的数据。下面是一个基本的示例，说明如何实现这一点：

首先，你需要创建一个ADODB.Connection对象，然后使用该对象的 Open 方法打开一个到Excel文件的连接。一旦连接被打开，你就可以使用Recordset对象来读取数据。
实例Excel表和Access数据库保存在同一个文件夹内，Excel表的名字为“作业人员职业病.xlsx”
以下是详细步骤：

添加ADO库。
在Visual Basic编辑器中选择【工具】->【引用】选项，打开【引用-VABProject】对话框，在列表框中选中【Microsoft ActiveX Data Objects 6.x Library】复选框。
然后，创建ADODB.Connection对象：

Dim conn As New ADODB.Connection
dim i,j as integer

使用Open方法连接到Excel文件：

j=0
strPath = CurrentProject.Path
pool: conn.Open "Provider= Microsoft.ACE.OLEDB.12.0;" & _
          "Extended Properties='Excel 12.0 Xml; HDR=Yes';" & _
          "Data Source= " & strPath & "\作业人员职业病.xlsx"
j=j+1

语句中HDR=yes，表示Excel表中第一行是列名而不是真正的数据。
4. 创建一个ADODB.Recordset对象来访问数据。

Dim rs As New ADODB.Recordset
Dim strSQL As String
strSQL = "select * from [上岗和离岗$] where 年龄>=35"
rs.Open strSQL, conn, adOpenKeyset, adLockOptimistic

注意：Excel工作表名后一定要后缀$符号。上述语句将获取工作表中所有年龄大于等于35岁的人员的数据。
5. 通过循环遍历Recordset对象，读取目标数据插入到数据库的指定表中。

rs.MoveFirst
If conn.State = adStateOpen Then
    For i = 1 To rs.RecordCount
        strInsertSQL = "insert into 体检登记表_大龄 (姓名, 性别, 身份证号, 年龄, 部门, 岗位, 体检日期) " & _
                       "values ('" & rs(1) & "', '" & rs(2) & "', '" & rs(3) & "', '" & rs(4) & "', '" & rs(6) & "', '" & rs(7) & "', #2022-07-28#)"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strInsertSQL
        DoCmd.SetWarnings True
        If i < rs.RecordCount Then rs.MoveNext
    Next
Else
    If j < 5000 Then
        GoTo pool
    Else
        MsgBox "长时间无法打开与Excel表的连接，请确认网络连接是否正常，然后重新执行数据导入", vbCritical, "提示"
        Exit Sub
    End If
End If
MsgBox "已经从源Excel表中读取" & rs.RecordCount & "条记录，并写入本项目的“体检登记表_大龄”表中"

最后，不要忘记关闭连接和Recordset

rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing