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
11 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!
Hallo Alex,
thank you for your advise. It helps me a lot. Peter
Fortunately I found your site.
Thanks a lot for this trick!
Thanks for the trick, really need it.
I am using access97 and using acSpreadsheetTypeExcel9 is giving me an runtime error 2282.The message is format that enables you to output data as excel is missing from windows regisistry.Any idea will be helpful
First Google entry for "runtime error 2282"
http://support.microsoft.com/default.aspx/kb/226526
This is an extraordinary trick !!! Thanks ..
I also was getting the runtime error 2282 but if you leave the output format blank and test it once you are prompted to select the format you want. These are your options is plain terms and if you just copy the one you want (in my case "Excel 97 - Excel 2003 Workbook (*.xls)") as the outputFormat you should be good to go.
You just saved me a looot of time ..
Post a Comment
<< Home