Solved

Problem exporting Access report with subreport to Excel

Posted on 2013-05-09
7
1,502 Views
1 Endorsement
Last Modified: 2013-05-16
I have an Access report which includes a subreport.  It prints fine in Access, but when I export it to Excel, the subreport begins in the next row and column of the main report.....example: the main report data is in Excel rows A1 thru Q111 - then the subreport begins in row R112 and uses as many columns as needed to the right (ends in AI113).

Is there any way to make the subreport align directly under the main report - that is to begin in cell R1?????   Than x for your help!

p.s.  I am NOT exporting using a VBA command - I am merely using the External Data icon to export the report to the Excel file.  I could use the VBA command if I could append the subreport to the main report?????
1
Comment
Question by:gmapdc
[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
7 Comments
 
LVL 40

Accepted Solution

by:
als315 earned 300 total points
ID: 39155898
It is normal behaviour when you are exporting complex reports. You should fill excel file from VBA if you like to have nice excel file. You can prepare template and fill it. Look at sample DB here:
#a38465860
0
 

Author Comment

by:gmapdc
ID: 39156144
I tried your example and got the same exact output - by normal behaviour are you saying that it is not possible to get the subreport to align with the main report?????
0
 
LVL 40

Assisted Solution

by:als315
als315 earned 300 total points
ID: 39156155
Yes, you can't place subreport exactly under main report without VBA.
And look at Sub Export in my sample.
1
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!

 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 150 total points
ID: 39156213
<Is there any way to make the subreport align directly under the main report - that is to begin in cell R1????? >
You did not post a sample of your database/report so it is hard to say...

But if the main report is left aligned, and the subreport is left aligned, then this should work.

Try als315's suggestion first.

Then perhaps you can post a simple sample of db, along with a clear graphical example of the exact output you need in Excel.

JeffCoachman
0
 
LVL 21

Assisted Solution

by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 50 total points
ID: 39166081
p.s.  I am NOT exporting using a VBA command - I am merely using the External Data icon to export the report to the Excel file.

This only works well for simple reports that do not have any sub reports.


I could use the VBA command if I could append the subreport to the main report?????

Not sure if that  is the best way to do it with VBA code. The best success I have doins this was to use VBA and Excel Automation to insert the data into the Worksheet in the desired format.   See: http://www.databasejournal.com/features/msaccess/article.php/3563671/Export-Data-To-Excel.htm
0
 

Author Closing Comment

by:gmapdc
ID: 39172529
I did have to use VBA code - in a procedure from a button, I ran sql queries of each report and subreport and appended the data to a common temp table.  The trick was to be sure the queries had the same number of columns, that there was no conflict of data types, and the columns were aligned the way I wanted.    I then exported the table , using TransferSpreadsheet, and finished by deleting the data from the temp table.  

This worked really nicely - thanx for pointing me in the right direction.
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

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.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

752 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