Solved

Export to Excel 2010 from Access 2010 retuns invalid file format

Posted on 2013-12-18
3
1,234 Views
Last Modified: 2015-09-14
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

Sandra
0
Comment
Question by:ssmith94015
3 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
use this

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


or


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

Author Closing Comment

by:ssmith94015
Comment Utility
First version worked, thank you.
Sandra
0
 

Expert Comment

by:Bdecker9
Comment Utility
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.

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

Open in new window

0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now