Solved

External Data Export to Excel works fine - acOutputReport not working

Posted on 2011-03-22
15
443 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
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
Technology Partners: 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 500 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

Independent Software Vendors: 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!

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
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 …

713 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