Export Access table to Excel that has more than 65000 records

[duketter]

Office 2007. I cannot seem to export an Access 2007 table into Excel 2007
that has more than 65,000+ records. It stops and gives me an error.
Shouldn't I be able to export all 95,000 rows in my Access 2007 table into
Excel 2007 with the new row limits?

 

[bhicks11 via AccessMonster.com]

There is a 65,536 row limit in Excel. See this thread.

http://www.accessmonster.com/Uwe/Fo...t-How-to-export-more-than-65535-rows-to-Excel


You will have to split it.

Bonnie
http://www.dataplus-svc.com

 

[duketter]

I am using Excel 2007 which has a 1,000,000+ row limit. The older Excel had
the row limit of 65,536.

 

[bhicks11 via AccessMonster.com]

Your right - I bet it has something to do with Excel compatibility mode. In
your export from Access are you selecting 07? I'm just tossing ideas out.

Bonnie
http://www.dataplus-svc.com

I am using Excel 2007 which has a 1,000,000+ row limit. The older Excel had
the row limit of 65,536.

There is a 65,536 row limit in Excel. See this thread.

[quoted text clipped - 9 lines]

 

[Jerry Whittle]

How exactly are you exporting the records to Excel. As you said, Excel 2007
can hold a million rows. However hitting a limit of 65000 records looks
suspiciously like the export is in something like Excel 2003 or earlier.

 

[Rick Brandt]

I am using Excel 2007 which has a 1,000,000+ row limit. The older
Excel had the row limit of 65,536.

OutputTo always assumes Excel 97 format. Use the TransferSpreadsheet method
instead which lets you specify the Excel version.

 

[bhicks11 via AccessMonster.com]

Good catch Rick. Wondered if it was something like that but couldn't put my
finger on it.

Bonnie
http://www.dataplus-svc.com

 

[Cbold]

Do you know if Microsoft plans to fix this so that you can export directly to
Excel 2007?

Also, where do you find the TransferSpreadsheet - I've looked in the Macro
settings, but was unable to locate.

 

[Douglas J. Steele]

I think you're find TransferSpreadsheet if you select Show All Actions. Jeff
Conrad explains at http://accessjunkie.com/faq_02.aspx

Given that you CAN export to Excel 2007 using TransferSpreadsheet (the
preferred way of dealing with spreadsheets), I doubt you'll find any change
is planned.