TransferSpreadsheet to .xlsx format

[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

 

[Dale Fye]

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

 

[Ken Warthen]

Thanks for the suggestion Dale, but I'm still getting the Excel error message
stating it doesn't recognize the file format.

Ken

 

[Jim Danner]

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

 

[Ken Warthen]

Jim,

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

Ken