Export spreadsheet method in 2007 Access

[Manuelauch]

When I use the export spread sheet method in Access 2007 as below

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
tblApplicationBroadcast, "C:/TEMP/Test.xlsx", True

it transfers OK, with no error, but when I try to open the Excel file I get
a warning that says that the 'file format or extension is not valid' and the
file will not open. What is wrong. This used to work in Access 2003

 

[Paul Shapiro]

I didn't try this but, maybe make the file name "C:/TEMP/Test.xls", instead
of the .xlsx file type extension.

 

[Douglas J. Steele]

You'll telling Access to export in Excel9 format (which is Excel 2000), yet
you're using an extension of .xlsx (which is the extension for Excel 2007).

Either change the extension to .xls, or see whether you can use
acSpreadsheetTypeExcel12 (sorry, I don't have Office 2007 installed on this
machine so I can't check)

 

[Manuelauch]

If I save it as Excel 2003 extension .xls it works> Even if the database is
Access 2007, saving it as .xls works. But I want to migrate every thing I
have in the office to office 2007. This is way i want it to save as .xlsx

 

[Manuelauch]

I have tried every thing I know including acSpreadsheetTypeExcel7, but not
acSpreadsheetTypeExcel12. I tried your suggestion and it did not work. I get
the same error warning when opening the Excel file.

 

[Paul Shapiro]

As Douglas suggested, you have to use the correct setting for the
spreadsheet type. Just changing the file type extension won't change the
file format. The Access object model viewer shows AcSpreadSheetType values
of acSpreadsheetTypeExcel12 and acSpreadsheetTypeExcel12Xml. Try one or both
of those. Unfortunately the Access 2007 help does not appear to have been
updated in this area, so it does not explain the meaning of those format
types. But one of them is likely to do what you want.

 

[Manuelauch]

Thank you very much. The right value is 'acSpreadsheetTypeExcel12Xml'. It
worked just perfect.