Critical Issue exporting to xls format

[JohnG]

If you export a table to the older xls format with more than 16384 rows, the
process does complete, tells you that it did it successfully, but will
trucate the output. (I need to get my data into the earlier format so I can
import it into my analysis program). This does not happen if you export to
the newer format. This is a nasty error, for it can lead to erroneous
analysis if you do not notice the data is trucated.

 

[Jeff Boyce]

I'm sure you've already looked into this, but in problem-solving mode, it
sounds like export to the "current" Excel first would all all the rows. Do
you get the same (no error) condition when exporting from the current Excel
to the earlier version?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

(should have read "... would allow all ...")

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[JohnG]

When I export from Access 2007 to Excel 2007, all records come through. Then
I can export from Excel 2007 to Excel 2003, and all records come through. I
only loose records when I export from Access 2007 to Excel 2003. This is when
the file is trucated.

Best Regards,

John

 

[Jeff Boyce]

John

I haven't run across the issue you've reported, but it sounds like you have
a solution to the issue...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[JohnG]

Thre reason I posted this was not to find a solution, but to warn everyone of
the problem. If you are not aware of it, this can really mess up some of your
analysis. The only way I noticed it was that the data analysis showed up with
a solid block of zeros. Although a zero was a valid result, the high
frequency was not. As it was I had to go back and redo over a weeks worth of
calculations.

I have actually come up with two answers.

1) Save to Excel 2007, and then resave from Excel 2007 to Excel 2003

2) Revert back to Access 2003 and export directly to 2003.

I choose the second approach. It does save the data correctly. It also
addresses the problem I had importing data from a text file (I posted this
problem in a separte posting). What I found particularly disturbing was that
when I exported it from Access 2007 to Excel 2003, it told me that the export
of the data was successful, but it only saved half the data.

Regards,

John

 

[gllincoln]

Hi John,

Excel 5.0/95 dumps anything past 16384 rows. that is the row limit and the
behavior you can anticipate.

If you bump the export version up to Excel 97 thru 03, you will have the
65536 row limit to work with.

Hope this helps,
Gordon

 

[JohnG]

I tried what Gordon suggested, and it did resolve the problem. I had not
really noticed the two output options. I just picked the first *.xls file
output I saw. Thanks for you help.

Best Regards,

John