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