This is a problem that I have asked about many times. From Excel 2007 onwards the capacity of Excel Rows is waaaaay more than previous versions (in fact it is 1,048,576!). So, why is Access limiting me to 65000 lines?!!! Pre 2007 times. Not quite pre- historic but its sure as heck feeling like it.
Well, it is probably because you have been doing something like this…….
The issue is that we have selected Export data with formatting and layout.
Because when we export with formatting the Excel clipboard is used and you then get this Microsoft Access warning…
Access will send the data to the Windows clipboard when you tick the Export data with formatting option, so that all the formatting and layout can be copied. The problem is that the old windows clipboard limits you to only 65,000 lines of data.
So, by leaving this options ticked, you are enabling this restriction. Just un tick the option and you can by pass the Windows clipboard removing the restriction.
So whilst the answer is simple, its not really an obvious one either.
Have you come across this issue before?. – let me know in comments
Want To Watch This Tip On Video?
Mike M says
I tried this method and found that it works…. sort of. It did export more than 65000 lines for me, but I found that about 30% of the data was missing. When I did a sample it appears to be random lines, with no noticeable pattern. Any idea how to fix this problem?
Don Bosi, Jr, says
The best is to link the data from access to excel then select all records then copy it to another sheet as values.
Abhaya says
Great,
Its work for me and save a lot of time.
Thanks for sharing your idea
Girmay says
thank you for your explanation.
Beki says
By unchecking the option to export data with formatting and layout, the issue I got is that one field format did not export correctly. Needed all 4 decimals and it only exported 2 decimals into a text file. Any idea how to fix this?
Thanks!
Andrew says
So simple, but had no idea it was possible. Thanks for the tip.
Barbara says
Glad you like the tip Andrew