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

[newnewbie]

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.

 Function Revenue_Export()

'South Region
DoCmd.OutputTo acOutputQuery, "All", acFormatXLSX, "C:\All Revenue.xlsx", False, ""
                 
Revenue_Export_Exit:
    Exit Function

End Function

Please help....

Thank you!

Lena

[newnewbie]

Got it working!
Maybe it'll helpo somebody else:

Function Macro1()
On Error GoTo Macro1_Err

    DoCmd.TransferSpreadsheet acExport, 10, "All", "C:\All Revenue.xlsx", True, ""


Macro1_Exit:
    Exit Function

Macro1_Err:
    MsgBox Error$
    Resume Macro1_Exit

End Function