Too Many Rows To Output

[Guest]

I receive the message in the subject line when trying to export a query to
Excel 97-2002 format. The query is only 30,000 records and 10 columns. Why
won't it export?

I select the query, click on File, Export. Then, Save As Type, then
Microsoft Excel 97-2002, then Save In, then I use the filename of the query
in the File Name box, then Save Formatted, then Export. After that, I get
the error message. I've tried opening the datasheet and exporting using the
same procedure, except that I Export All after Save Formatted, but have the
same result. What's the issue here?

 

[Guest]

Try doing it with Save Formatted off. Sometimes it creates extra lines.

 

[John Nurick]

If you check "Save Formatted", or use Analyze it with MS Excel, the
system uses the OutputTo routine, which dates from the days when Excel
sheets were limited to 16384 rows or so. The 16384 row limit applies
even if you select a modern Excel format: you can only export up to the
65536-row limit of current versions of Excel by unchecking Safe
Formatted.

 

[Guest]

Thanks. Your suggestion worked.

Try doing it with Save Formatted off. Sometimes it creates extra lines.

 

[Guest]

Thanks. Your suggestion worked.

If you check "Save Formatted", or use Analyze it with MS Excel, the
system uses the OutputTo routine, which dates from the days when Excel
sheets were limited to 16384 rows or so. The 16384 row limit applies
even if you select a modern Excel format: you can only export up to the
65536-row limit of current versions of Excel by unchecking Safe
Formatted.

 

[Guest]

Is there a way to do export a query with over 16384 records using the
OutputTo in VBA?
I have the following code:

varDir = Format(DATE, "yyyy")
varFilename = "i:\PRICING\DATABASE\ACCESS 2003\REPORT HISTORY\" & varDir &
"\EXCEPTION" & Format(DATE, "mmyyyy") & ".XLS"
varOldName = Format(DATE - 2, "mmddyy") - this is the query that
needs to be appended on a new sheet on an existing spreadsheet (filename is
the current moth and year) - records counts flactuates between 13000 to 35000
on a given day.
varNewName = Format(DATE - 1, "mmddyy")
DoCmd.OutputTo acOutputQuery, varOldName, acFormatXLS, varFilename
(I get the row limit when it gets to this command"