Excel VBA Macro to copy Access Query into Excel

[JoyC]

I have an Access Database which I use for controlling and calculating large volumes of data (too big for Excel to cope with). However the summaries from this data (a series of queries and crosstabs) for this need to be put into Excel for reporting purposes. I would like to be able to click a command button in Excel and pull the data over from the database. The macro I currently have in my Excel workbook, which has been gathered through research on other forums, does not pull the headings over:

Sub Query()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sql As String
Range("A:F").Select
With Selection.ClearContents
End With
Set db = OpenDatabase("C:\Personal Data\zgd81463\Training\Training.mdb")
Set rst = db.OpenRecordset("X_ProjectGroup")
ActiveSheet.Range("A2").CopyFromRecordset rst
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing

End Sub

Could someone please help with applying the headings to the macro.

Thank you.

 

[pcc]

something like this (not tested)

Sub Query()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sql As String
Range("A:F").Select
With Selection.ClearContents
End With
Set db = OpenDatabase("C:\Personal Data\zgd81463\Training\Training.mdb")
Set rst = db.OpenRecordset("X_ProjectGroup")

For iCol = 1 To rst.Fields.Count
    ActiveSheet.Cells(1, iCol) = rst.Fields(iCol - 1).Name
    Next iCol

ActiveSheet.Range("A2").CopyFromRecordset rst
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing

End Sub

 

[JoyC]

Thank you.

Unfortunately, I am now getting a "Compile error: Type mismatch" message on the "For iCol = 1 To rst. Fields.Count" line. Any ideas?

 

[pcc]

try dimming iCol as integer.
If that doesn;t work, can';t help any more, sorry..

 

[JoyC]

Brilliant. Works a treat, thank you. That's exactly what I wanted.