• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1452
  • Last Modified:

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.
0
heyday2004
Asked:
heyday2004
  • 7
  • 4
  • 2
9 Solutions
 
hnasrCommented:
DoCmd.OutputTo acSendReport, "Table1", acFormatXLS, "c:\Table1.xls"
0
 
hnasrCommented:
Table1=Report name
0
 
heyday2004Author Commented:
Thanks, but it's not working:

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!
0
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.

 
hnasrCommented:
Display file properties.
Show Details tab.
Type: Microsoft Office Excel 97-2003 Worksheet.
0
 
Rey Obrero (Capricorn1)Commented:


test this

sub XlReport()

dim xlobj as object

DoCmd.OutputTo acOutputReport, "rptTemplate", acFormatXLS, "C:\sample.xls"
doevents

set xlobj=createobject("excel.application")
      xlobj.workbooks.open "C:\sample.xls"

     ' save as Excel 97-2003 format
     xlObj.activeworkbook.saveas "C:\sample.xls", FileFormat:=56, CreateBackup:=False
     xlobj.quit

end sub


0
 
hnasrCommented:
As you mentioned in your code: acOutputReport instead of acSendReport
acSendReport=3 and acOutputReport=3
0
 
hnasrCommented:
I am using Office 2007.
You pointed out using 2003, hence the earlier version of xls format in output to.
Try capricorn1's comment and report.
0
 
heyday2004Author Commented:
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!
0
 
Rey Obrero (Capricorn1)Commented:
is the format the same.. does the size matter?
0
 
heyday2004Author Commented:
I got error on line:  
xlObj.activeworkbook.saveas "C:\sample.xls", FileFormat:=56, CreateBackup:=False

"Run-time error '1004', SaveAs method of Workbook class failed. "

The size does matter. Thanks for the help!
0
 
hnasrCommented:
Change the output file name.
This is an issue to investigate.

If you add reference to Microsoft Excel Type library, you can use the FileFormat constants.
xlExcel8  = 56
Other formats, check FileFormat Property in excel help.
0
 
heyday2004Author Commented:
Ok, I found an answer from internet and seems to be working:

Create a new Registry Key:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\11.0\Access\Report Formats]
"MyOwnFormat"="xls,SOA_RptToBIFF, Biff8,Microsoft Excel Biff8 (*.xls),0"

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.
0
 
hnasrCommented:
Tried capricorn1's comment to save as access 5 version, but still showing 97-2003 in properties.
xlExcel5 instead of xlExcel8.

Stick to your registry method.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now