Exporting Query Results to Excel

[MrAustin]

Hi all,

What is the most efficient way to export query results to excel? Right now I'm using the command line:

DoCmd.OutputTo

But when the record count goes above 3000, it stalls and doesn't finish the export. Any advice to a better track? Thanks!

 

[Hayley Baxter]

Have you tried using the transfer spreadsheet option?

The following code will export a query to an excel sheet.

Sub export_to_excel()
Dim mfile
myfile = "Your path name/spreadsheet name.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "your query name", myfile
End Sub

Hay

 

[MrAustin]

Thanks a lot, that works very well! Is there any way to have the header row of the spreadsheet formatted? Right now it just has the field names from the table design. When I used DoCmd.OutputTo it formatted the headers as well. If not I can deal with that, but it would be nice

Thanks again!