Access Export to Excel (2007)


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.


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.

  • Diana Hargus says:

    Actually, this is incorrect. The solution provided by M$ is pure rubbish and does not resolve the issue. Don’t believe me? I can show a total of 8 databases I tried to export this morning alone and not one of them would export. The issue is not within office but rather in the clipboard by the looks of things

  • a7n9 says:

    Diana, thanks for your comment. I am rather surprised that it didn’t work for you. I had to scratch my head for long when I could not export big tables, but checking that option off worked for me. Can I know the details of the table? If you are exporting about eight tables, you should try and write a VBA procedure to export all the tables in your db to Excel files.

  • Brett Gustafson says:

    Oddly enough, I’ve found that if you go to the External Data -> Export -> Excel that you will experience Diana’s issue. However, If you right click on your table/query and then go to Export -> Excel, it will work if you have the formatting checkbox off.

  • a7n9 says:

    Thanks for your comment, Brett. Hmm.. now that is something. I have always exported using right click on the table/query, and that is why checking off worked for me. Let me update my post with your solution. Thanks!

  • Claude Perron says:

    I lost 4 hours trying to figure out what was wrong with the export of 380k record from Access 2007 using the export function from the main menu. However right-clicking on the Query results and export to Excel as mentionned in the thread worked beautifully. This is very annoying and time consuming. How dumber can this be ? Thanks for the hint. MS screwed up again.

  • PaulN says:

    I haven’t found the right-click method to work when the normal export doesn’t, whether or not the formatting box is checked. I think that “solution” was just luck.

    The limitations of the clipboard are also not the reason for failure.

    I’ve had the problem on occasion and other times I’ve successfully exported hundreds of thousands of records.

    I believe the problem has to do with how the database was created in the first place – at least in my case. My original database was created using Access 2003 and I opened it with Access 2007. More than 64 KB rows of records wouldn’t export. It would not export as text either.

    I “converted” the database to Access 2007 using “Save as” and I still couldn’t export over 64 KB of rows to Excel 2007 (also would not export as text).

    I think that the limitations of the Access 2003 version are carried forward to the 2007 version of Access when converted by either of these methods but this is just a guess.

    What I had to do was create a new, blank, Access 2007 database and import the tables and queries I needed to produce my export file for Excel. After doing this I was able to export several hundred thousand records to Excel by right-clicking on the query object and selecting Excel OR by using the Excel export button under the “External Data” tab.

    Hope this helps.

  • a7n9 says:

    Paul, thanks for your reply and other suggestions. I do not think it was pure luck. Formatting does take significant resources and right-click does give you that option. If exporting worked for you after creating a new database, could it be a db corruption issue?

  • Michael K says:

    I was having similar problems exporting more than 65K+ from an Access 2007 db to Excel. I did find a workaround…Export the table/query from Access as a txt file and then open that file in Excel. It worked for a 290K+ line table I had. This was the only way I was able to export the whole table at one time.

  • jon olsen says:

    I am trying to export via a query and I don’t see an option to export without formatting, any ideas?

  • Ed Burckhardt says:

    Thanks for the quick solution a7n9, I right clicked on the table as you suggested and it exported 236K records flawlessly.

  • rama says:

    I have the some issue in exporting a report excel.

    I have header as test report

    where as in excel i get this printed as “40789” in place of test report. it is kind of format error. any clue why this happens?

  • Rwill says:

    The reason this is happening is because the database is older than 2007 the database needs to be converted to MS Access 2007
    ext – accdb . Go to save as and you can save it as the new version.

  • larry says:

    i was able to build an access database in 2003 and create a macro which exported more than 65,000 records with no problem. i am trying to build a new macro and get the error of cannot export more than 65,000 records. what is the workaround for a macro.

  • Avi says:

    Excellent article. This saved me a ton load of scanning through settings to see what was causing it! Thank you…

  • Brandon says:

    I had a report with 290K lines. Leaving the formatting box unchecked did not solve my issue as it then only imported 992 lines rather than 65K. Unsure why.

    Anyway, I set my query up and ran it to make sure the results were what I expected. Then I opened a new Excel spreadsheet, Data tab, selected “From Access” and found my table in the list. Followed the prompts and all records were imported successfully.

  • >