Too many rows to export to Excel (1 Viewer)

[Autoeng]

I have too many rows (16,600+) rows in a table I am trying to export to Excel using the following code.

Option Compare Database

'------------------------------------------------------------
' Autoexec
'
'------------------------------------------------------------
Function Autoexec()

    DoCmd.SetWarnings False
    On Error Resume Next
    Kill "S:\everyone\abkgrann\tbl2009.xls"
    DoCmd.OpenQuery "qrytbl1009", acViewNormal, acEdit
    DoCmd.OutputTo acTable, "tblqry2009QA", "MicrosoftExcelBiff8(*.xls)", "s:\everyone\abkgrann\tbl2009QA.xls", False, "", 0
    DoCmd.OpenTable "tbl2009QA", acViewNormal, acEdit
    DoCmd.Maximize
    DoCmd.GoToRecord , "", acNewRec
    DoCmd.SetWarnings True

End Function

Is there another Excel format that I could use to get past the record limit?

 

[KenHigg]

If you could put that many rows in the .xls how would you open it if it exceeds the limit?

 

[Autoeng]

Excel has 65,536 rows. I think it is a limit of the export.

 

[MobiusDick]

I have come across this problem and the only way I found to get round it is to write the data to a table and use the DoCmd.TransferSpreadsheet method which will allow you to output up to the Excel limit of records.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblqry2009QA", "s:\everyone\abkgrann\tbl2009QA.xls"

 

[KenHigg]

Hum... Maybe you could export to txt/csv...

Also I noticed you were deleting tbl2009.xls but writing to tbl2009QA.xls. Is this correct?

 

[Autoeng]

Worked great. Much faster that OutputTo as well.

Thanks!

 

[crimmelcp]

Export Access Table to Excel and dbf

This command works well for exporting an access table to excel.
I cannot find the correct syntax to export an access table to a dbf file.

Thanks
Charllie Crimmel

Access to Xcel
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "exporthist", "c:\exporthist.xls"

Access to DBF
DoCmd.TransferDatabase acExport, acDatabaseTypeDBF, "exporthist", "c:\exporthist.dbf"

 

[Moniker]

All of this is because TransferSpreadsheet does not have a 16KB limitation, and OutputTo does have a 16KB limitation. You'll still hit the 65,535 row limit in Excel 2003 and earlier, meaning if you have more than 64KB worth of records, you'll still get an error even with TransferSpreadsheet. The solution is a custom function, like this one (look at response #5). Note that Excel 2007 allows 1MB of rows, but Excel 95 through 2003 limit you to 64KB.

 

[Autoeng]

Thank you Moniker for the excellent post. I'll file that away for the day when I breach 65K rows!