"too many rows to output" exporting query to Excel

[Mo]

Receive message "There are too many rows to output, based
on the limitation specified by the output format or by
Microsoft Access". I'm trying to export an Access Query
(same problem exporting a Table) to Excel 97. I've found
that even though I'm specifing Excel 97 format it seems to
save in a lower version of Excel. I can export up to
16384 rows fine. Excel 97's limit is supposed to be
65536. When I open a smaller excel worksheet that has
been exported from Access 97, make a change and then Save
I get a message re: the file being a lower version of
Excel and do I want to update it. Why does Access 97
appear to save to a lower version of Excel 97 even though
I've specified '97 and is there anything I can do to
change this?

 

[John Nurick]

Hi Mo,

Use File|Export (or DoCmd.TransferSpreadsheet) and specify Excel 97
format (or a later version). Don't select the Save Formatted option.

Doing it that way should let you export up to 65536 rows. As far as I
can mek out, if you use the Save Formatted option, DoCmd.OutputTo or
Tools|Analyze it with Excel, Access will stop at 16384 rows even if you
have specified Excel 97 or later.

 

[Mo]

I was selecting the Save Formatted option. Not selecting
it appears to do the trick, thanks John.

-----Original Message-----
Hi Mo,

Use File|Export (or DoCmd.TransferSpreadsheet) and specify Excel 97
format (or a later version). Don't select the Save Formatted option.

Doing it that way should let you export up to 65536 rows. As far as I
can mek out, if you use the Save Formatted option, DoCmd.OutputTo or
Tools|Analyze it with Excel, Access will stop at 16384 rows even if you
have specified Excel 97 or later.

Receive message "There are too many rows to output, based
on the limitation specified by the output format or by
Microsoft Access". I'm trying to export an Access Query
(same problem exporting a Table) to Excel 97. I've found
that even though I'm specifing Excel 97 format it seems to
save in a lower version of Excel. I can export up to
16384 rows fine. Excel 97's limit is supposed to be
65536. When I open a smaller excel worksheet that has
been exported from Access 97, make a change and then Save
I get a message re: the file being a lower version of
Excel and do I want to update it. Why does Access 97
appear to save to a lower version of Excel 97 even though
I've specified '97 and is there anything I can do to
change this?

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.