Access Export to Excel (2007)

Problem:

When I tried to export a table/query from Access 2007 to Excel 2007, I was getting this annoying error:

You selected more records than can be copied onto the Clipboard at one time. Divide the records into two or more groups, and then copy and paste one group at a time. The maximum number of records you can paste at one time is approximately 65,000.

Access 2007 Export to Excel Error

Access 2007 Export to Excel Error

I was unable to understand the reason, because I thought Excel 2007 did not limit the number of rows to 65,536. It turns out, however, that this error is caused by limitations of the clipboard and no because of Excel.

Solution:

Un-check the “export data with formatting and layout” option, and see Access export data to Excel successfully.

Another option to consider, use Transferspreadsheet (VBA) function.

Solution Update:

Thanks to Brett, it looks like that if you right-click on a table or a query, select Export, and in the Export dialog box, keep  “export data with formatting and layout” option un-checked, then Access will export successfully.

About the Author

The author of Tableau Data Visualization Cookbook and an award winning keynote speaker, Ashutosh R. Nandeshwar is one of the few analytics professionals in the higher education industry who has developed analytical solutions for all stages of the student life cycle (from recruitment to giving). He enjoys speaking about the power of data, as well as ranting about data professionals who chase after “interesting” things. He earned his PhD/MS from West Virginia University and his BEng from Nagpur University, all in industrial engineering. Currently, he is leading the data science, reporting, and prospect development efforts at the University of Southern California.

Leave a Reply 15 comments