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

Problem exporting Access report with subreport to Excel

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
gmapdc
Asked:
gmapdc
4 Solutions
 
als315Commented:
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
 
gmapdcAuthor Commented:
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
 
als315Commented:
Yes, you can't place subreport exactly under main report without VBA.
And look at Sub Export in my sample.
1
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Jeffrey CoachmanMIS LiasonCommented:
<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
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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
 
gmapdcAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

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