Solved

EXPORT REPORT TO EXCEL

Posted on 2003-11-12
5
5,777 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
[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
  • 3
  • 2
5 Comments
 
LVL 32

Expert Comment

by:jadedata
ID: 9732372
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
ID: 9732573
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
ID: 9732659
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
ID: 9732801
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
ID: 9732840
thanx for the question!
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

623 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