Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Export to Excel 2010 from Access 2010 retuns invalid file format

Posted on 2013-12-18
3
Medium Priority
?
1,480 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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 39727631
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
ID: 39727639
First version worked, thank you.
Sandra
0
 

Expert Comment

by:Bdecker9
ID: 40977165
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

580 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