Exporting from Access to Excel 2007

  • Thread starter Thread starter Jeff Boswell
  • Start date Start date
J

Jeff Boswell

I'm trying to export a large Access file to Excel 2007, but I'm getting the
message that Excel can't hold all the lines (66,000 or so). I though Excel
2007 could hold over a million.
 
Yes 2007 can. However you need to be careful on how you are doing the export.

If you are using OutputTo in code or a macro, it exports as a very old
version of Excel which could only handle about 64,000 rows.

TransferSpreadsheet is better but even it is tricky. You must remember to
end the file name in .xlsx . Also you have to put in what seems to be an
undocumented argument of 10 for the file type. Help only talks up to 8 which
is the old Access 2000 file format.

Below is a function that I just used to export over 700,000 records from a
table into Excel 2007. You'll need to change table/query, file, and path
names.

Function fTransfer2Excel2007()
On Error GoTo fTransfer2Excel2007_Err

DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acExport, 10, "Cargo_tariff",
"c:\temp\Cargo_tariff.xlsx", False, ""
DoCmd.SetWarnings True

fTransfer2Excel2007_Exit:
Exit Function

fTransfer2Excel2007_Err:
MsgBox Error$
Resume fTransfer2Excel2007_Exit

End Function
 
Back
Top