在Excel VBA環境下,如何抓取Access查詢的欄位?

在Excel VBA環境下,可以抓取Access查詢的資料至Excel,但是這只能抓取資料,如果想要抓取欄位名稱和資料,該怎麼去改以下的程式嗎?

請大家協助,謝謝~~~

Sub test()

Set cnn = CreateObject("ADODB.Connection")

Set rst = CreateObject("ADODB.Recordset")

Set rs = CreateObject("ADODB.Recordset")

Dim strWorkbook As String

Dim strsql As String

strWorkbook = ThisWorkbook.Path & "\比率計算.mdb"

With cnn

.Provider = "Microsoft.ACE.OLEDB.12.0"

.ConnectionString = "Data Source=" & strWorkbook & ";"

.Open

End With

strsql = "Select * from T_Grade_交叉資料表"

Set rst = cnn.Execute(strsql)

Sheets("Sheet1").Select

Sheets("Sheet1").Cells(2, 1).CopyFromRecordset rst

Set cnn = Nothing

Set rst = Nothing

End Sub

2 個解答

評分
  • 冰淇
    Lv 6
    7 年前
    最佳解答

    ....

    Set rst = cnn.Execute(strsql)

    Set pt = Sheets("Sheet1").Range("a1")

    With rst

    For i = 1 To .Fields.Count

    pt.Offset(0, i - 1).Value = .Fields(i - 1).Name

    Next

    pt.Offset(1, 0).CopyFromRecordset rst

    End With

    Set cnn = Nothing

    Set rst = Nothing

    2013-12-25 21:56:24 補充:

    ....

    Set rst = cnn.Execute(strsql)

    Set pt = Sheets("Sheet1").Range("a1")

    With rst

    For i = 1 To .Fields.Count

    pt.Offset(0, i - 1).Value = .Fields(i - 1).Name

    Next

    pt.Offset(1, 0).CopyFromRecordset rst

    End With

    Set cnn = Nothing

    Set rst = Nothing

  • 感謝您的協助,謝謝~~~~~

還有問題?馬上發問,尋求解答。