Export to Excel 2010 from Access 2010 retuns invalid file format

I have  table that I want to export, simple, but when I use the below incode, it creates the workbook, but when I try to open the file, I get a the error message that the file format or file extension is not valid.

            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, _
            "tblExcelExportData", "C:\TEMP" & "\TestDataExport.xlsx", True

Not sure what to do as it looks fine to me

Who is Participating?
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
use this

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12xml, _
            "tblExcelExportData", "C:\TEMP" & "\TestDataExport.xlsx", True


DoCmd.TransferSpreadsheet acExport, 10, _
            "tblExcelExportData", "C:\TEMP" & "\TestDataExport.xlsx", True
ssmith94015Author Commented:
First version worked, thank you.
This is a late addendum to the issue, but you'll also sometimes get that error if you try to export to an Excel file that already exists, because Access will try to append a new worksheet tab into an existing .xlsx file. If the .xlsx file was in an earlier version of Excel, for example, then the resulting .xlsx will throw that error.

We usually add code to ensure the .xlsx is a new file, or delete it if it already exists before continuing the export.

If(Len(Dir(filename))>0 Then Kill filename
Docmd.TransferSpreadsheet acExport,,filename,queryname

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.