limited excel lines available to export access report

[bwlytkr]

Whenever I have an excel report more than 65k lines I get an error message saying I need to split the report up. for example, my query has 185k lines but when I report the file I get this message. Is there a way to correct this to allow more records to export. I hope it is some option in properties I can change to corect.

You selected more records than can be copied onto the clipboard at one time. The maximum # of records you can paste is approximately 65K

 

[Joe4]

If you are choosing to export to the old "xls" Excel file format, you are limited to the 65,536 lines.
But I believe if you choose to export to the newer "xlsx" Excel file format, you should have 1,048,576 lines at your disposal.

 

[bwlytkr]

If you are choosing to export to the old "xls" Excel file format, you are limited to the 65,536 lines.
But I believe if you choose to export to the newer "xlsx" Excel file format, you should have 1,048,576 lines at your disposal.

I had heard of that before but, all the files I am exporting to and from are xlsx files. so I'm just not sure what the issue is.

 

[Joe4]

Can you show/explain exactly you are exporting the file?
Are you doing it via Macro or VBA (if so, please post the code)?
Or, if you are doing it manually, can you walk us through the exact steps?

 

[jdellasala]

Use Power Query to pull in the data, save it to the Data Model (Power Pivot), and then use it to create a Pivot Table / Pivot Chart. There is no (practical) limit to the number of rows of data Power Query / Power Pivot can handle, and in fact I have a sample that pulls in over 10 million rows of data from CSV files with no problem.