[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

External Data Export to Excel works fine - acOutputReport not working

Posted on 2011-03-22
15
Medium Priority
?
448 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

649 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