Export to Excel - There are too many rows to output
I was just hit by Run-time error 2306: “There are too many rows to output, based on the limitation specified by the output format or by Microsoft Access”, produced by this line:
DoCmd.OutputTo acOutputQuery, "MyQuery", acFormatXLS, , True
Query has about 18000 records, what should not be a problem for Excel. Fortunately I found that replacing acFormatXLS with acSpreadsheetTypeExcel9 did the trick, so this line would work:
DoCmd.OutputTo acOutputQuery, "MyQuery", acSpreadsheetTypeExcel9, , True
Interesting that using acSpreadsheetTypeExcel9 is not documented in online help or object browser, and both constants has quite different values:
Const acSpreadsheetTypeExcel9 = 8
Const acFormatXLS = "Microsoft Excel (*.xls)"
Anyway – it works! If anybody have explanation to this – would be great to learn.
BTW - DoCmd.TransferSpreadsheet will also work in this case.
posted by Alex Dybenko | 18:31
3 Comments:
Good find. Just wondering: does it work in both Access 2003 and 2007?
Hey Alex, I think it has to do with specific versions of Excel. For the longest time, Access would only transfer 16,384 records to Excel even after Excel's row limit was expanded to 65,536 rows.
Even today, Access 2007 will only transfer 65,536 records to an Excel file even though Excel supports 1,048,576. If you want more than 65,000 rows from Access into Excel, you have to go into Excel and import it rather than have Access export it - at least with the built in export commands. Perhaps there is a way with VBA to get Access to export more records directly to an .xlsx file.
Thanks for information, Ed!
Post a Comment
Links to this post:
Create a Link
<< Home