DoCmd.TransferSpreadsheet Problems

[dsajones]

Hi All,

I'm working with Access 2010 and am trying to use the transferspreadsheet command to output data in a query to an Excel 2010 format file. Here is the line of code:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "q_calldetails_tmp", "c:\temp\testoutput.xlsx"

It works fine and produces the output file but when I try and open it with Excel I get an error saying the format is incorrect. If I change the extension to .xls it opens with no problem but I need it to be an Excel 2010 format with correct extension.

Any help gratefully received.

 

[Ranman256]

51 is for .xlsx
acSpreadsheetTypeExcel12xml

try acSpreadsheetTypeExcel9 for .xls

 

[dsajones]

51 is for .xlsx
acSpreadsheetTypeExcel12xml

try acSpreadsheetTypeExcel9 for .xls

Hi Ranman. I need the output to be Excel 2010 with .xlsx extension. I can use acSpreadsheetTypeExcel9 and .xls but for various reasons we need to be using 2010 with .xlsx

 

[dsajones]

Hmmm. Don't know what's happened by I closed Access and went to lunch. Now I've opened it up again it's working with acSpreadsheetTypeExcel12xml and .xlsx extension

Weird!!