TransferSpreadsheet to .xlsx format

  • Thread starter Thread starter Ken Warthen
  • Start date Start date
K

Ken Warthen

I'm trying to use DoCmd.TransferSpreadsheet to export some Access 2007 data
to an Excell 2007 spreadsheet. I use the .xlsx file extension with my
filename, but after the code is run I try opening the newly created file from
Excel 2007 and it says it doesn't recognize the file format. Any idea on
what I may be doing wrong? TIA - Ken

strOutputFile = strPath & "\Output\UserList.xlsx"
DoCmd.TransferSpreadsheet TransferType:=acExport, TableName:="tblID",
filename:=strOutputFile, Hasfieldnames:=True
 
Ken,

When I look at the Access Help, Transferspreadsheet method, it does not give
a value for the 2nd parameter (SpreadsheetType) that corresponds to Excel
2007. All it gives is values up through Excel 2000 format, and the help
screen doesn't indicate what the default format is. But when I went into the
object browser, I did find "acSpreadsheetTypeExcel12" and
"acSpreadsheetTypeExcel12XML"

My recommendation would be to export with:

DoCmd.TransferSpreadsheet TransferType:=acExport,
SpreadsheetType:= acSpreadsheetTypeExcel12,
TableName:="tblID", filename:=strOutputFile, Hasfieldnames:=True
 
Thanks for the suggestion Dale, but I'm still getting the Excel error message
stating it doesn't recognize the file format.

Ken
 
Ken,

The xlsx extension is used with Excel's xml file format. You should be able
to use the acSpreadsheetTypeExcel12Xml file type. The code snip below
produced a valid Excel file for me.

DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel12Xml , _
"Lanes", _
"Lanes.xlsx", _
True
 
Jim,

Thanks for the help. That worked, of course. Thanks again. This was
driving me crazy.

Ken
 
Back
Top