Output Query as an Excel file

[accesshacker]

I am trying to output a query as an excel file and save it to a specific
location. Here is my code.

DoCmd.OutputTo acOutputQuery, "Qry:SKU_ALL_2", acFormatXLS, _
"C:\RH DOC\LCM_PROJECT\LCM_QRY" & "\" & "LCM_QRY" & "_" & "PERIOD" & "_" &
([Forms]![LCM_DATA]![PER]) & ".xls", False

I get a run time error '2306' that states there too many rows to output,
based on the limitation specified by the output format or by Microsoft
Access. There are 36,464 records. When I take acFormatXLS and the file path
and have Access prompt me for the output, I am able to create the file in the
folder specified.

Not sure what I am doing wrong. Any help will be greatly appreciated!

 

[Jerry Whittle]

acFormatXLS saves the Excel file in an older version, Excel 95 I believe,
that can only handle about 32,000 rows.

Look into using TransferSpreadsheet instead. Something like below can handle
about 64,000 rows.

DoCmd.TransferSpreadsheet acExport, 8, "Cargo_tariff",
"c:\temp\Cargo_tariff.xlsx", False, ""

If you have Access 2007 changing the 8 to 10 allows about 1 million rows.

 

[accesshacker]

Hi Jerry,

Thanks for the response, that worked. Also, I did come across another method
that works as well, just had to change a little of the code. It is as follows.

DoCmd.OutputTo acOutputQuery, "Qry:SKU_ALL_2", acSpreadsheetTypeExcel9, _

Changed the acFormatXLS to acSpreadsheetTypeExcel9

Thanks again for the quick response!

acFormatXLS saves the Excel file in an older version, Excel 95 I believe,
that can only handle about 32,000 rows.

Look into using TransferSpreadsheet instead. Something like below can handle
about 64,000 rows.

DoCmd.TransferSpreadsheet acExport, 8, "Cargo_tariff",
"c:\temp\Cargo_tariff.xlsx", False, ""

If you have Access 2007 changing the 8 to 10 allows about 1 million rows.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I am trying to output a query as an excel file and save it to a specific
location. Here is my code.

DoCmd.OutputTo acOutputQuery, "Qry:SKU_ALL_2", acFormatXLS, _
"C:\RH DOC\LCM_PROJECT\LCM_QRY" & "\" & "LCM_QRY" & "_" & "PERIOD" & "_" &
([Forms]![LCM_DATA]![PER]) & ".xls", False

I get a run time error '2306' that states there too many rows to output,
based on the limitation specified by the output format or by Microsoft
Access. There are 36,464 records. When I take acFormatXLS and the file path
and have Access prompt me for the output, I am able to create the file in the
folder specified.

Not sure what I am doing wrong. Any help will be greatly appreciated!