Click here to Skip to main content
15,920,896 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi - We have a requirement to export data from access table to .xls sheet where the source table has more than 75000 records but we are able to export only 65000 records(Copied the code snippet below)

Do we have any option to get export all 75000 + records to .XLS sheet?

What I have tried:

<pre>   strFileName4 = strFileName + "\XYZ.xls"
'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "tablename", strFileName4 
DoCmd.OutputTo acOutputTable, "tablename", "Excel 97 - Excel 2003 Workbook (*.xls)", strFileName
Posted
Updated 23-Mar-18 8:07am

1 solution

If you are using a .xls file then you are hitting the limit on the number of rows that can exist in a single worksheet within Excel.

You have a few options:

1. If possible upgrade your Excel installation to the latest version that uses .xlsx format

2. Split the data export to spread the results across two (or more) worksheets

3. Output as a CSV file - note you still won't be able to open it in older version of Excel

Quote:
Excel 2003 supports a maximum of 65,536 rows per worksheet
Microsoft Docs[^]
 
Share this answer
 
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900