By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,664 Members | 1,986 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,664 IT Pros & Developers. It's quick & easy.

Exporting large query (over 66K records) from Access 2007 to Excel 2007

newnewbie
P: 54
Hi,

Short version: trying to export more than 65K of data from Access 2007 into Excel 2007 (important) through VBA. Access gives me 65K row limitation error, though I thought that was no longer an issue in Access/Excel 2007. Any hope?

Long story:

I have several databases with 400+ queries that I export into a folder as Excel files (with VBA). I then have some Excel macros that run through all the exported files and format them into pivots, delete the source data, etc. Till recently I exported everything into Excel 2003. Approximately 6 months into the fiscal year I have to limit the number of months of data that's exported due to the 65k row limitation that Excel 2003 had.

This year, since we now have Excel 2007, I thought I would update the code to export into Excel 2007 and thought the problem with the row limitation would be gone. I updated the code to export the query into Excel 2007, but it still gives me an error that I am copying more records that can be copied, etc.

Here's my code to export. If I let it run past the 65K error it exports exactly 65536 records, though into a new Excel *.xlsx file.


Expand|Select|Wrap|Line Numbers
  1.  Function Revenue_Export()
  2.  
  3. 'South Region
  4. DoCmd.OutputTo acOutputQuery, "All", acFormatXLSX, "C:\All Revenue.xlsx", False, ""
  5.  
  6. Revenue_Export_Exit:
  7.     Exit Function
  8.  
  9. End Function
Please help....

Thank you!

Lena
Apr 28 '10 #1
Share this Question
Share on Google+
1 Reply


newnewbie
P: 54
Got it working!
Maybe it'll helpo somebody else:

Expand|Select|Wrap|Line Numbers
  1. Function Macro1()
  2. On Error GoTo Macro1_Err
  3.  
  4.     DoCmd.TransferSpreadsheet acExport, 10, "All", "C:\All Revenue.xlsx", True, ""
  5.  
  6.  
  7. Macro1_Exit:
  8.     Exit Function
  9.  
  10. Macro1_Err:
  11.     MsgBox Error$
  12.     Resume Macro1_Exit
  13.  
  14. End Function 
Apr 28 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.