?
Solved

Urgent help on how to save Access report as Excel 97-2003 file using VBA

Posted on 2010-01-10
13
Medium Priority
?
1,370 Views
Last Modified: 2012-05-08
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
Comment
Question by:heyday2004
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 2
13 Comments
 
LVL 30

Accepted Solution

by:
hnasr earned 1556 total points
ID: 26280305
DoCmd.OutputTo acSendReport, "Table1", acFormatXLS, "c:\Table1.xls"
0
 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 1556 total points
ID: 26280313
Table1=Report name
0
 

Author Comment

by:heyday2004
ID: 26280336
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 1556 total points
ID: 26280396
Display file properties.
Show Details tab.
Type: Microsoft Office Excel 97-2003 Worksheet.
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 444 total points
ID: 26280436


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
 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 1556 total points
ID: 26280440
As you mentioned in your code: acOutputReport instead of acSendReport
acSendReport=3 and acOutputReport=3
0
 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 1556 total points
ID: 26280461
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
 

Author Comment

by:heyday2004
ID: 26280476
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
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 444 total points
ID: 26280502
is the format the same.. does the size matter?
0
 

Author Comment

by:heyday2004
ID: 26280529
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
 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 1556 total points
ID: 26280569
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
 

Author Comment

by:heyday2004
ID: 26280620
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
 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 1556 total points
ID: 26282018
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month7 days, 20 hours left to enroll

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question