Access Export Limit

  • Thread starter Thread starter Dave Stok
  • Start date Start date
D

Dave Stok

I'm bumping into a limit of 16,384 rows when exporting to
Excel from Access. I seem to recall being able to export
to Excel up to the Excel limit of 60k+ rows, but now I
can't get past 16,384. Is this lower limit for real or do
I have some other kind of problem?
TIA for any thoughts or ideas.
Dave
 
Hi Dave,

This means you (or Access) are importing to the old Excel format with
its limit of 16k rows. This automatically happens if you use
- Analyze it with Excel
- DoCmd.OutputTo or the OutputTo macro action
Avoid it by using File|Export or DoCmd.TransferSpreadsheet and the Excel
97 or later (version 8 or later) file format.
 
Thanks John! Your suggested work-around actually WORKS!

DoCmd.TransferSpreadsheet acExport,
8, "FinalTable2", "o:\testexport.xls", True

This bit of code exported all 45k rows of a table called
FinalTable2 to the file testexport.xls. the parameter 8
designates use of the Excel 97 format which extended the
row limit from 16k to 64k. I still don't understand why
the 'File / Save as Export' menu path doesn't give the
same result - but, for me it doesn't.
Dave
-----Original Message-----
Hi Dave,

This means you (or Access) are importing to the old Excel format with
its limit of 16k rows. This automatically happens if you use
- Analyze it with Excel
- DoCmd.OutputTo or the OutputTo macro action
Avoid it by using File|Export or
Click to expand...
DoCmd.TransferSpreadsheet and the Excel
97 or later (version 8 or later) file format.


I'm bumping into a limit of 16,384 rows when exporting to
Excel from Access. I seem to recall being able to export
to Excel up to the Excel limit of 60k+ rows, but now I
can't get past 16,384. Is this lower limit for real or do
I have some other kind of problem?
TIA for any thoughts or ideas.
Dave
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Just read a MS KB article today. The default in Access
2002 for exports to Excel is format 5.0. Excel 5 has a
limit of 16,384. To get around this, specify Excel
97/2000 format, or open Excel and import from Access.
Larry Hall
 
Back
Top