[AccessD] Problems Exporting Access Summary Report to Excel

Rocky Smolin rockysmolin at bchacc.com
Wed Oct 30 14:54:42 CDT 2013


Can you use a summation query as the source of your TransferSpreadsheet to
get the grouping totals?

When faced with more an a simple flat file export I've often had to resort
to creating the spreadsheet with automation, and pushing the data directly
into the cells where I want them to be.  It's more work but the advantage is
that you have total control of the format - column width, row height, font,
color, formulas, etc.

Rocky


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Wednesday, October 30, 2013 11:10 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Problems Exporting Access Summary Report to Excel

All,

In the past, I have experimented with exporting Access Reports to Excel, but
I have never had much success.  

Most of the reports that I have worked with in the past were fairly complex
(dynamic sorts, dynamic filters, conditional formatting, sub-totals, etc.)

Recently, one of our users has requested that a rather simple Access report
be exported to Excel.
This report reads data from a purchased package's General Ledger tables.

The detail lines of the report are not visible.  There are groupings on
Month/Year and on the General Ledger Account Number.  Here is a small
example of what the report looks like.


Account-AAA  Total $1,000
Account-BBB  Total $2,000
Account-CCC  Total $3,000
   01 2013 Month Total $6,000

Account-AAA  Total $1,100
Account-BBB  Total $2,200
Account-CCC  Total $3,300
   02 2013 Month Total $6,600


There are many records in the underlying GL table.  The use of Access Report
"Groups" makes it very easy to obtain the sub-totals at the two levels (by
Account within Month).

When I try to export the report to Excel, however, things do not turn out
very nicely. For example, the Month and Year fields are not shown.
Instead, "AccessTotalsAmount:" is shown for each cell in this Excel column.

What I am really trying to ask, is if there is a way to employ report
groupings and then be able to successfully export such a summary report to
Excel.

Perhaps there is another way to do this.

Thanks,
Brad   

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list