Click here to Skip to main content
14,696,430 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have an Access export-to-Excel statement like this:
INSERT INTO [Excel 8.0;HDR=YES;IMEX=2;Database=D:\test2.xls].[Sheet1$] SELECT Description, Reals7, Reals8 FROM Components WHERE [Database Number] = 1

But it only works if I pre-create test2.xls and set it with the correct column names (Description, Reals7, Reals8).

Using the query, is there any way to:

1) Get the XLS to be created automatically?
2) Get the defined column names into the sheet automatically? (or at the least, to use some defaults like F1, F2, etc. automatically?)
3) Alias the column names? (minor)


1 solution

I have to export data from Access to Excel spreadsheets a lot here at work. I usually export from a query or table using VBA. Use the following code as a starting point and if you have any questions then reply back. Good luck.

DoCmd.OutputTo acOutputTable, "Table Name Goes Here", acFormatXLSX, SpreadsheetName, False

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900