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.