Export more than 65000 rows to Excel

[matthewR]

I am trying to export a file to excel 2007 from Access. The file is large. The error I get is that the clipboard can't except more the 65000 and to break the file into smaller files. I can't export the whole file to excel at one time?

Figured out what was wrong. The person trying to export the file had checked the formatting option with exporting to Excel. For some reason you with a large file, you can't use the formatting option. File exported fine without that option.

[HansV]

The restriction is not caused by Excel but by the Windows clipboard.

When you export to Excel with formatting, Access tries to copy the entire table to the Windows clipboard, to be pasted into an Excel sheet. The clipboard, however, is limited to 65535 (=2^16 - 1) items.

When you export to Excel without formatting, Access bypasses the clipboard and writes to the Excel sheet directly, and this is only limited by the number of available rows (1,048,576 for an Excel 2007/2010 workbook).

[matthewR]

Thank you for the explanation. I will pass this along.

[Littletoot]

How and where do I adjust the "formatting option" in order to export more than 65,000 rows of data from Access to Excel?
I am using Access 2013, Excel 2013 and Windows7.

[HansV]

Welcome to Eileen's Lounge!

The screenshot below is from Access 2010 but it should look more or less the same in Access 2013.
I clicked Excel in the Export group on the External Data tab of the ribbon.

S0414.png

[Littletoot]

Hans, thank you so much. If I wasn't so thrilled with the outcome, I would feel like Danny the Dunce, what with how simple the solution. You have saved me a few hours of very boring work. Have a great day.