Urgent help on how to save Access report as Excel 97-2003 file using VBA
Posted on 2010-01-10
In my Access report (Access 2003), user could open the report, right click and choose "Export", and select "Save as Type:" as either "Microsoft Excel 5-7" or "Microsoft Excel 1997-2003". Now I want to automate this process using VBA, so I used:
DoCmd.OutputTo acOutputReport, "rptTemplate", acFormatXLS, "sample.xls"
The result is perfect (report format is kept, etc.) except I found the format is "Microsoft Excel 5-7". What I really wanted is: "Microsoft Excel 1997-2003"
How to do it using DoCmd.OutputTo?
Anyway, I tried TransferSpreadsheet command (the query for rptTemplate is query1)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "query1", "C:\temp\test.xls", True
But the exported excel file is just the data of query1, and it lost all the report format (grouping, etc.) (while the report spreadsheet file DoCmd.OutputTo generated still kept the report format). Could I still use TransferSpreadsheet?
Any help on how to save Access report as Excel 97-2003 file using VBA is really appreciated. Thanks a lot.