Solved

External Data Export to Excel works fine - acOutputReport not working

Posted on 2011-03-22
15
439 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
  • 8
  • 7
15 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
Comment Utility
I have not, let me give that a try boaq.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
 
LVL 1

Author Comment

by:G Scott
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 1

Author Comment

by:G Scott
Comment Utility
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
Comment Utility
...and where do I go so that you can explain the reason for duplicating the report?
0
 
LVL 1

Author Comment

by:G Scott
Comment Utility
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
Comment Utility
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
Comment Utility
Ok, thanks Jeff. Enjoy!
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
OK

;-)
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

771 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

10 Experts available now in Live!

Get 1:1 Help Now