Buck Beasom
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_Proces s\dbo_mhfs isu\MHFSIS U" & 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.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_dbo_MHFSISU", "L:\Monthly_Ratings_Proces
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.
If you open the query directly from access, does it include all the records?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Use acSpreadsheetTypeExcel12Xm l 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 acSpreadsheetTypeExcel12Xm l rather than 10 because it documents the export file type.
I prefer to use acSpreadsheetTypeExcel12Xm
for those who does not know the difference for acSpreadsheetType
AcSpreadSheetType Enumeration (Access)
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 acSpreadsheetTypeExcel12XM L 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.
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.
ASKER
Thanks!
You are welcome!