?
Solved

External Data Export to Excel works fine - acOutputReport not working

Posted on 2011-03-22
15
Medium Priority
?
446 Views
Last Modified: 2013-11-28
So I have a formatted report that when I have the report open in Report View and go up to External Data>Export To Excel it exports fine and keeps my report groupings. If I use the DoCmd.OutputTo acOutputReport command using a button on the report it exports fine, but the groupings all have the same group name. The report has different names in the groupings.

I have attached samples of the Excel sheets as they come out of Access so you can understand what I mean.

Thanks for any help you can give me on this.

ExportExcel.xls acOutPutReport.xls
0
Comment
Question by:G Scott
[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
  • 8
  • 7
15 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35193280
Have you tried

Docmd.TransferSpreadsheet

Something like this:

DoCmd.TransferSpreadsheet acExport, , "TargetTableName", "C:\YourFolder\YourFile.xls", True
0
 
LVL 1

Author Comment

by:G Scott
ID: 35193339
I have not, let me give that a try boaq.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35193351
Besides, ...the two spreadsheets appear the same to me.

Neither one appears to have any data...?

So it might be best if you posted a sample of the Access data and report.
Along with that post a graphical representation of the exact output you are expecting based on the sample data
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:G Scott
ID: 35193365
Oh, I took the data out...company secrets and all. ; )  But if you look at the names along the left in the spreadsheets, one has 5 different names, like it does on my report, the other has repeated names.

Let me try to clean it up, it has all financial data in it. Corporate might frown upon me sharing that. : )
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35193908
OK, now I see what you mean...

OK, so how did the Transferspreadsheet code work...?

Keep in mind that OutpuTo is an "Attempt" to display the data in the same way in Excel.

It has never been, ...nor will it ever be, ...Perfect.

This is why I would like to see the Access data.
If this is "Summarized data" (crosstab query, GroupBy query) then this might be tricky...

0
 
LVL 1

Author Comment

by:G Scott
ID: 35206204
Hey boaq, the Transferspreadsheet didn't work because it wanted to export a table.

However, here is how I 'solved' it. I created an exact duplicate of the same report, named it differently. When you click the button to do the OutPutTo I point to that duplicate report and everything works fine. Is it possible it has to do with the report being open?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35207178

I have never had to create a duplicate report in order to Export to Excel...
The issue with this approach is that *every time* you run the report you must immediately make another copy of it to get the same records.
The other disadvantage is that, *every time* you make a design change to the report, you must again, immediately make the exact change to the copy.

This is why I would like to see a sample database that exhibits this issue.
Perhaps there is another alternative.

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Delete any objects that do not relate directly to the issue.
5. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
6. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
7. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
8. Compile the code. (From the database window, click: Debug-->Compile)
9. Run the compact/Repair utility.
10. Remove any Passwords and/or security.
11. Post explicit steps to replicate the issue.
12. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see the issue.


JeffCoachman
0
 
LVL 1

Author Comment

by:G Scott
ID: 35373996
Finally got a sample. Took me a while to strip it down. Not sure if there is anything to be done now.

 SampleDB.accdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35380501
...and where do I go so that you can explain the reason for duplicating the report?
0
 
LVL 1

Author Comment

by:G Scott
ID: 35484305
Sorry Jeff, been off work. If you click on the second button "Operations Director by WorkStream" and then click the "Export to Excel" button.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35488254
OK,

I'm on vacation right now.

I'll work on this Monday, ...if another Expert does not step in.

Jeff
0
 
LVL 1

Author Comment

by:G Scott
ID: 35490043
Ok, thanks Jeff. Enjoy!
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 35537898
OK

When I click the label it creates the Excel file and I see 4 distinct groupings:
Brian Fantana
Brick Tamland
Champ Kind
Ron Burgundy

So you will have to explain what you mean by:
"it exports fine, but the groupings all have the same group name."

Here is the file I get when I click your label

JeffCoachman

CI-Trac.xls
0
 
LVL 1

Author Closing Comment

by:G Scott
ID: 35926118
Hey Jeff, must have been something wrong with my install. I formatted my pc and reinstalled everything and now I get the same result as you.

Thanks for sticking with me. I really do appreciate the help.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35926554
OK

;-)
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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

800 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