Link to home
Start Free TrialLog in
Avatar of Buck Beasom
Buck BeasomFlag for United States of America

asked on

Export to Excel

I am using this code to export an Access query to Excel:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_dbo_MHFSISU", "L:\Monthly_Ratings_Process\dbo_mhfsisu\MHFSISU" & Format(i, "00") & ".xlsx", True

When I try to open the resulting file, I get a "File Corrupted" message. If I change the extension of the file name to .xls it opens but I don't get all the rows.

What is the issue with the code line?

Thanks.
Avatar of Anders Ebro (Microsoft MVP)
Anders Ebro (Microsoft MVP)
Flag of Denmark image

If you open the query directly from access, does it include all the records?
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Use acSpreadsheetTypeExcel12Xml rather than acSpreadsheetTypeExcel9.  This is the standard .xlsx format.  Don't be confused by the xml suffix.  the acSpreadsheetTypeExcel12 constant creates a different file type.  I don't recall if it is binary or macro but it is most definitely NOT an .xlsx

I prefer to use acSpreadsheetTypeExcel12Xml rather than 10 because it documents the export file type.
for those who does not know the difference for acSpreadsheetType

AcSpreadSheetType Enumeration (Access)
That chart is misleading.  People reading the chart assume that acSpreadsheetTypeExcel12 should be the value to create the .xlsx format but it is not.  You need to use acSpreadsheetTypeExcel12XML because apparently without discussing it with us, MS made the XML format the standard for the .xlsx file extension.  It also labels the "12" versions as O2010 but the .xlsx file type came out with O2007.

I ran 9 exports and here are the resultsPublic-Sub-testExcelExport.docx

I reported the documentation error to Microsoft but don't hold your breath.  It's been wrong/confusing for years and they don't seem interested in fixing it.
Avatar of Buck Beasom

ASKER

Thanks!
You are welcome!