excel通过脚本抓取access的数据

首先打开excel，然后右键点击“sheet1”，选择菜单最下方的“查看代码”，复制下边的脚本到vbscript编辑器内，之后點“工具-引用”再找到Microsoft ActiveX Date Objects 2.8勾选一下，点击确定，之后运行代码就行了。

Public Function daadfa()
'首先引用ado --------- 點“工具-引用”再找到Microsoft ActiveX Date Objects X.X Library

Dim conn As New ADODB.Connection, connstr As String, db As String, rs As New ADODB.Recordset, i As Long, rows As Long
db = "C:\1\db.mdb"
connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & db
On Error Resume Next
conn.Open connstr
rs.Open "select * from userinfo", conn, 1, 3
If rs.EOF Or rs.BOF Then
     MsgBox "It is nothing."
Else
    '如果access中username的值等于Seven，那么就把當前記錄的userid值放入excel表的第10行第10列內
    'If rs("username") = "Seven" Then
        'Worksheets("sheet1").Cells(10, 10).Value = rs("userid")
    'End If

    '將access中沒一列的標題用循環抓取過來，并將其存放到excel的sheet1工作表的第一行內
    rows = 1
    For i = 0 To rs.Fields.Count - 1
        Worksheets("sheet1").Cells(1, rows).Value = rs.Fields(i).Name
    Next i

    '將access中每一列對應的數據用循環抓取過來
    Do Until rs.EOF
        For i = 0 To rs.Fields.Count - 1
            Worksheets("sheet1").Cells(i + 2, rows).Value = rs(i)
        Next i
    rows = rows + 1
    rs.MoveNext
    Loop
End If

End Function