News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Name:Alex Dybenko

Location:Moscow, Russia

Tuesday, October 07, 2008

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.

Labels: , , ,


Anonymous Anonymous said...

Good find. Just wondering: does it work in both Access 2003 and 2007?

3:45 PM  
Blogger Ed Hansberry said...

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.

1:57 PM  
Blogger Alex Dybenko said...

Thanks for information, Ed!

2:06 PM  
Anonymous Anonymous said...

Hallo Alex,
thank you for your advise. It helps me a lot. Peter

11:01 AM  
Anonymous Anonymous said...

Fortunately I found your site.
Thanks a lot for this trick!

12:04 PM  
Anonymous trepz said...

Thanks for the trick, really need it.

1:56 PM  
Anonymous Anonymous said...

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

8:06 PM  
Blogger Ed Hansberry said...

First Google entry for "runtime error 2282"

8:44 PM  
Anonymous Anonymous said...

This is an extraordinary trick !!! Thanks ..

5:59 PM  
Anonymous Anonymous said...

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.

2:06 AM  
Anonymous Chris Kuliukas said...

You just saved me a looot of time ..

7:25 AM  

Post a Comment

<< Home