Solved

EXPORT REPORT TO EXCEL

Posted on 2003-11-12
5
5,768 Views
Last Modified: 2011-08-18
To export an Orders report to Excel, I use:
DoCmd.OutputTo acOutputReport, "report_ORDERS", acFormatXLS, "C:\ORDERS.xls".

When I open the Excel file I get the error message: "REPAIRS TO ORDERS.XLS"

In the error log, it logged "Renamed invalid sheet name"

What should the sheet name be, or is there an extra formatting routine required?

John
0
Comment
Question by:jjjtuohy
  • 3
  • 2
5 Comments
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
In a default built book this should be "Sheet1", "Sheet2" and "Sheet3"

The fact that you are outputting a report format to a spreadsheet might be part of the problem.
Normally you would output a table or query.
0
 
LVL 3

Author Comment

by:jjjtuohy
Comment Utility
That's a clean result Jack; I output the underlying query and used acOutputQuery. I'll use that in future.

The technical puzzle still stands though:
(1) The Output-Object-Type list does include acOutputReport.
(2) The original report data did correctly export to Excel. Excel only moaned about having to rename an invalid sheet name to "Recovered-Sheet 1".
John
0
 
LVL 32

Accepted Solution

by:
jadedata earned 125 total points
Comment Utility
Just because you CAN do a thing, doesn't mean you SHOULD do a thing....

To keep your excel output looking pretty, investigate the use of CopyFromRecordset.  I have found this method to be the BEST way to build new, or fill templated XLS worksheets from a pre-built query.  It is used against an Excel Object, which exposes all the properties and methods of Excel during the operation for things like adding a summary total row across the top of a worksheet, a custom column header row, using the autofilters and autofit methods to really "spank" the book into the shape I want to see it in.
0
 
LVL 3

Author Comment

by:jjjtuohy
Comment Utility
Jack,
That was a Eureka moment! I didn't think of extracting from the outside. I'm assuming that a DAO database cannot be accessed externally without password.
On a side note I'm puting up 300 on a separate question "Suppress Conflict Viewer" and hope that you can help.
Cheers,
J
0
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
thanx for the question!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now