# Solved: export more than 65000 records



## DJh6064 (Apr 20, 2009)

I am using Office 2007 and can't export more than 65,000 records from Access to Excel. Does anyone know a workaround for this problem other than using Visual Basic? I don't know how to write any viusual basic code.

Thanks


----------



## Rollin_Again (Sep 4, 2003)

Can't you break the records up by using queries and export the results of each query to it's own file before combining them? How many total records are we talking about?

Can you also explain how you are exporting the records? Are you using the *OutputTo* method or the *TransferSpreadsheet* method? The second method I mentioned allows you to specify which version of Excel you are using and since 2007 supports more rows it should work for you.

BTW....I am pretty proficient in VBA so let me know some details and I may be able to write the code for you if needed.

Just google *DoCmd.TransferSpreadsheet * if you need more info.

Regards,
Rollin


----------



## DJh6064 (Apr 20, 2009)

I'm using Access and Excel 2007. I am exporting the data from Access to Excel. We have been exporting just the 65000 rows until all the data is exported and appending the listings to each other, but want a better or easier way to accomplish this. We have over 300,000 records to export to excel.


----------



## Rollin_Again (Sep 4, 2003)

You still have not indicated how you are exporting the records. Please explain in detail how you are performing the export. My post above explains that there are two methods to do this and one of the methods should work with no row limitations.

Regards,
Rollin


----------



## DJh6064 (Apr 20, 2009)

In Access 2007, I am selectig the External Data Tab, In the Export group, I select Excel, then on the Export Window, I select The file location and the "Export data with formatting and layout" option, then OK. It then exports the data to excel, with a limit of 65,000 records.


----------



## turbodante (Dec 19, 2008)

This is uncanny, I was just doing this same thing yesterday and ran into the same problem, and was about to post the same Q.

I got round the problem by NOT checking the option given:

'Export data with formatting and layout'

to Excelworkbook.xlsx

The pain is that the export is in MS Sans Serif, and additional Excel sheets will be using this font.


----------



## OBP (Mar 8, 2005)

My question is why do you need to export the data to Excel at all?


----------



## DJh6064 (Apr 20, 2009)

To answer OBP, the person likes to manipulate the data in ways that access can't.
To fix the problem, I used a combination of what Turbodante and Rollin_again suggested and that solved the problem.

Thanks for your help. Greatly appreciated.


----------



## OBP (Mar 8, 2005)

It must be very heavy maths if Access can't do it


----------

