Hi,
I am looking to get the query table data into excel new sheet using excel vba.
I only manage to find a way to do it by inserting the module in access vba.
As it is require to insert data from excel to access table and then pulled back the query table result back to excel, if I could get it all in excel I could do a call macro to run all process from one place.
Any help is appreciated.
Code
Public Function TransferToExcel()
On Error GoTo ErrorHandler
Dim strTable As String
Dim strWorksheetPath As String
strWorksheetPath = "D:\Users\Desktop\"
strWorksheetPath = strWorksheetPath & "Test.xlsx "
strTable = "Query1 "
DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel12, _
TableName:=strTable, FileName:=strWorksheetPath, _
hasfieldnames:=True
ErrorHandlerExit:
Exit Function
ErrorHandler:
MsgBox "Error No: " & Err.Number _
& "; Description: " & Err.Description
Resume ErrorHandlerExit
End Function
Display More