• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 451
  • Last Modified:

External Data Export to Excel works fine - acOutputReport not working

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
G Scott
Asked:
G Scott
  • 8
  • 7
1 Solution
 
Jeffrey CoachmanMIS LiasonCommented:
Have you tried

Docmd.TransferSpreadsheet

Something like this:

DoCmd.TransferSpreadsheet acExport, , "TargetTableName", "C:\YourFolder\YourFile.xls", True
0
 
G ScottAuthor Commented:
I have not, let me give that a try boaq.
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
G ScottAuthor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
G ScottAuthor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:

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
 
G ScottAuthor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
...and where do I go so that you can explain the reason for duplicating the report?
0
 
G ScottAuthor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
OK,

I'm on vacation right now.

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

Jeff
0
 
G ScottAuthor Commented:
Ok, thanks Jeff. Enjoy!
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
G ScottAuthor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
OK

;-)
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now