Excel Tip: Export More Than 65000 Lines From MS Access to MS Excel


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?

 

Barbara

Barbara is an experienced data analyst with over 20 years of experience. Author of the How To Excel At Excel Top 50 Excel tips Ebook, YouTuber and Excel Blogger.

Recent Posts