Need help with TransferSpreadsheet - Access 2010

[edsac64]

Hi,

Can someone please provide some badly needed assistance?

I'm using Access 2010 and would like to transfer a table to Excel 2010 (.xlxs). I was using Access 2003 and was able to successfully transfer to older .xls formats.

Using the .xlsx extension in the file name with the following code, produces an .xlsx spreadsheet that won't open! I get an error message saying the file format or extension isn't valid.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, strTable, strSourceFile, HasFieldNames:=True

I read somewhere that it's better to do it in .xlm format, which Excel 2010 would open easily, but can't recall, or find, how to do it.

Thanks!

 

[boblarson]

1. Unless your output spreadsheet has macros, it should not be an .XLSM file extension (you typed XLM but it is XLSM).

2. You want XLSX as the file extension. And you export to it using

acSpreadsheetTypeExcel12XML

 

[edsac64]

Hi Bob,

Once again, thanks! Unfortunately, I couldn't get your solution to work, so I ended up creating a macro and converting it to VBA.

Here it is, in case someone can benefit from it.

DoCmd.OutputTo acOutputTable, strTable, "ExcelWorkbook(*.xlsx)", strSourceFile, False, "", , acExportQualityPrint

Thanks again! You guys are still the best board out there!