It seems like one of the most basic codes involving Excel and Access, but I've been struggling to determine how to copy access query results into excel using VBA.
I would like to write an excel macro that
1) opens an access dba file
2) runs a query
3) either copy or exports the query results
4) closes the dba
#3 is elluding me. I have the following code to open a dba called "Production" which includes a existing query called "Query" and either export or copy the results to an xls called "QueryResults." It hangs on the export line.
Sub GetData()
Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase "F:\Production.mdb"
appAccess.Visible = True
appAccess.docmd.SetWarnings True
docmd.transferSpreadsheet acexport, acSpreadsheetTypeExcel9, _
"Query", "C:\QueryResults.xls", True, "Sheet1" :confused:
appAccess.CloseCurrentDatabase
appAccess.Quit
Set appAccess = Nothing
End Sub
Thank you in advance for your help and understanding!
Bookmarks