Error-Too many rows to output to Excel

[StayAtHomeMom]

I am using VBA to run queries, output them to Excel, and run a subroutine to format the results in Excel. I'm having a problem with one of them that I do not know how to fix. I am getting a run time error (#2306) that states:

"There are too many rows to output, based on the limitation specified by the output format or by Microsoft Access"

It's true that the results of this query include about 8000 records. What can I change to handle that, if anything? I am including the code to show the syntax of my calls to output the query results to Excel and the open command.
Thanks in advance for any clues!

DoCmd.OutputTo acQuery, "MyR352Query", acFormatXLS, "C:\ClientName\RPT352-", False, "", 0

Workbooks.Open "C:\ClientName\RPT352-"
GoSub RepExcelFormat_R352

 

[KenReay]

Hi

There are limits to how many rows and columns can be in an Excel spreadsheet, the exact limits depend upon th version you are using, look in help under specification to check teh limits in your particualr case.

In terms of what you can do, well that depends on if you are running the latest version, or not, if necessary you could upgrade, or if not you need to change teh query to limit further the number of rows selected.

However, the latter may not suit you needs, if there are (say) more than 8000 rows matching you criteria, then that is how it is, so you need perhaps to output the data in a different format (Report, csv whatever) or perhaps split the data in someway to put it into two (or more) spread sheets.

Hope that helps



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[MichaelRed]

Use the transferspreadsheet method.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[StayAtHomeMom]

MichaelRed,

I tried your suggestion with this code:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "RPT352-MyQueryName", "C:\RPT352-"

yet I'm getting a Run-Time Error #3027
"Cannot update. Database or object is read-only"

Now neither my database nor this query are read only (I don't even know how to make a query read-only!). I've tried the different SpreadsheetTypes available, even using the default. I manually exported this query to an Excel file, so I know it's working. Any suggestions? I'm using Access 2002.

 

[KenReay]

Hi

Are you using Access2000 (or possibly XP)?

If yes it may be a silly as the file extension on your output file.

This is a guess, but when importing text files etc, the file extension must be one of those recorded in the registry as an extension for import files, otherwise you get the error message you experienced, which appears to be totally unrelated to the task in hand. It could be the same problem exists when exporting?

Hope this helps


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK