Hi everyone,
I am struggling with a new problem, when I want to export a Table into excel file.
Before it worked well as I have used "CurrentDb" (forms and tables in the same Access doucment).
but when I want to export a table from another Access DB (where there are only the table) by using the trick :
Set wrk = DBEngine(0)
Set dbX = wrk.OpenDatabase(data_base_name)
This tricks has worked for all my SQL statement because I usually use the "dbX.Execute" statement.
I want to do so in order to make frontend and backend App. (the forms where it would be possible to export are completly separated from the unique database located in another path)
The sub stops everytime at code line where there is the "DoCmd.TransferSpreadsheet" statement.
Error n°3011: the object "query_to_export" doesn't exist
I have verified in the data base where the table (I want to export) is located, the query "query_to_export" has been actually created
Plus, the Excel document has been created without nothing inside, but with the asked path "C:\Users\username\Documents\testexport.xlsx"
I fear that it is because the exportation is only possible when the tables are in the same Access document as the VBA modules where I execute the code...
Does actually the statement "DoCmd" work only in the current access project?...
Is tehre another way to export a table from extern database without using the code "DoCmd.TransferSpreadsheet" but rather CurrentDb.Execute ?
I thank you a lot in advance for your help/suggestions,
Kind regards,
Laura
I am struggling with a new problem, when I want to export a Table into excel file.
Before it worked well as I have used "CurrentDb" (forms and tables in the same Access doucment).
but when I want to export a table from another Access DB (where there are only the table) by using the trick :
Set wrk = DBEngine(0)
Set dbX = wrk.OpenDatabase(data_base_name)
This tricks has worked for all my SQL statement because I usually use the "dbX.Execute" statement.
I want to do so in order to make frontend and backend App. (the forms where it would be possible to export are completly separated from the unique database located in another path)
The sub stops everytime at code line where there is the "DoCmd.TransferSpreadsheet" statement.
Error n°3011: the object "query_to_export" doesn't exist
I have verified in the data base where the table (I want to export) is located, the query "query_to_export" has been actually created
Plus, the Excel document has been created without nothing inside, but with the asked path "C:\Users\username\Documents\testexport.xlsx"
I fear that it is because the exportation is only possible when the tables are in the same Access document as the VBA modules where I execute the code...
Does actually the statement "DoCmd" work only in the current access project?...
Is tehre another way to export a table from extern database without using the code "DoCmd.TransferSpreadsheet" but rather CurrentDb.Execute ?
I thank you a lot in advance for your help/suggestions,
Kind regards,
Laura
Code:
Sub export_table_to_excel()
Dim wrk As Workspace
Dim dbX As Database
Dim qdf As QueryDef
Set wrk = DBEngine(0)
Set dbX = wrk.OpenDatabase(data_base_name)
strSQL = "SELECT * FROM [TABLE_NAME]"
Set qdf = dbX.CreateQueryDef("query_to_export", strSQL)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "query_to_export", "C:\Users\username\Documents\testexport.xlsx", True
'delete query in Access
dbX.QueryDefs.Delete "query_to_export"
Set qdf = Nothing
dbX.Close
Set dbX = Nothing
wrk.Close
Set wrk = Nothing
End Sub