heyday2004
asked on
Urgent help on how to save Access report as Excel 97-2003 file using VBA
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hnasr:
Could you explain what you meant here:"As you mentioned in your code: acOutputReport instead of acSendReport acSendReport=3 and acOutputReport=3". So I still need to use acOutputReport, but the result is still Excel 5-7 file. Also, when I right click the file properties, there is no "details" tab. How could I display that tab? Thanks a lot.
capricorn1: I'm trying that, not sure if it will work because as you said, i manually saved that excel 5-7 file to format: 1997-2003, but the result file doesn't match the real exported 1997-2003. Thanks!
Could you explain what you meant here:"As you mentioned in your code: acOutputReport instead of acSendReport acSendReport=3 and acOutputReport=3". So I still need to use acOutputReport, but the result is still Excel 5-7 file. Also, when I right click the file properties, there is no "details" tab. How could I display that tab? Thanks a lot.
capricorn1: I'm trying that, not sure if it will work because as you said, i manually saved that excel 5-7 file to format: 1997-2003, but the result file doesn't match the real exported 1997-2003. Thanks!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I got error on line:
xlObj.activeworkbook.savea
"Run-time error '1004', SaveAs method of Workbook class failed. "
The size does matter. Thanks for the help!
xlObj.activeworkbook.savea
"Run-time error '1004', SaveAs method of Workbook class failed. "
The size does matter. Thanks for the help!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok, I found an answer from internet and seems to be working:
Create a new Registry Key:
[HKEY_LOCAL_MACHINE\SOFTWA
"MyOwnFormat"="xls,SOA_Rpt
DoCmd.OutputTo acReport, "rptTemplate", "MyOwnFormat", "sample.xls", False
The file generated matched the manual generated "1997-2003" format excel file.
The interesting thing is: in the registry, there is registry for format "Microsoft Excel 97-2003" exactly as "MyOwnFormat", but when I tried below lines:
DoCmd.OutputTo acReport, "rptTemplate", "Microsoft Excel 97-2003", "sample.xls", False
DoCmd.OutputTo acReport, "rptTemplate", "Microsoft Excel 5-7", "sample.xls", False
Both generated the same Excel 5-7 files, not 97-2003 file.
Any explanation on this or any simpler solution for this? If not, I guess I have to use this "new registry" solution. Thanks again for all the help i've got.
Create a new Registry Key:
[HKEY_LOCAL_MACHINE\SOFTWA
"MyOwnFormat"="xls,SOA_Rpt
DoCmd.OutputTo acReport, "rptTemplate", "MyOwnFormat", "sample.xls", False
The file generated matched the manual generated "1997-2003" format excel file.
The interesting thing is: in the registry, there is registry for format "Microsoft Excel 97-2003" exactly as "MyOwnFormat", but when I tried below lines:
DoCmd.OutputTo acReport, "rptTemplate", "Microsoft Excel 97-2003", "sample.xls", False
DoCmd.OutputTo acReport, "rptTemplate", "Microsoft Excel 5-7", "sample.xls", False
Both generated the same Excel 5-7 files, not 97-2003 file.
Any explanation on this or any simpler solution for this? If not, I guess I have to use this "new registry" solution. Thanks again for all the help i've got.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
From the file size, seems the generated excel file is still: Excel 5-7 format (not 1997-2003 format)
Here is the details:
When I manually right click the report: "Export", and choose Excel 5-7 format, the file size is 34kb
When I manually right click the report: "Export", and choose Excel 1997-2003 format, the file size is 41kb
Your line listed above generated the same file size as Excel 5-7: 34kb
I could only tell the difference from the file size, because all these files seems to be identical (format, data, line number, etc.) when I opened them in Excel 2003.
Thanks for any further help!