Solved

Access 2013 Export Query to Excel with Totals

Posted on 2015-02-11
7
826 Views
Last Modified: 2016-02-10
I have the following VBA that I am using to export queries to Excel. It is working fine except for exporting the average totals from the bottom of the query.

Option Explicit

Function ExportTimelineReports()
Dim exportFile As String
exportFile = "\\Path1\filename1.xlsx"
If Len(Dir$(exportFile)) > 0 Then
     Kill exportFile
End If

DoCmd.TransferSpreadsheet acExport, , "Timeline-2014-4th Qtr", exportFile
DoCmd.TransferSpreadsheet acExport, , "Timeline-2015-1st Qtr", exportFile
DoCmd.TransferSpreadsheet acExport, , "Timeline-2015-2nd Qtr", exportFile
DoCmd.TransferSpreadsheet acExport, , "Timeline-2015-3rd Qtr", exportFile
DoCmd.TransferSpreadsheet acExport, , "Timeline-2015-4th Qtr", exportFile

MsgBox "Export Timeline Reports File Updated!"
End Function
0
Comment
Question by:CMILLER
7 Comments
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40603717
The queries will only export what they display when opened manually.

/gustav
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 40603814
I don't know what your queries look like, but when I want to add a "Totals" to a query, I create a UNION query that looks something like:

SELECT T.ID, T.Field1, T.Field2, T.Field3
FROM (
SELECT ID, Field1, Field2, Field3, 0 as sortOrder
FROM myTable
UNION
SELECT SUM(NULL) as ID, Sum(Field1) as expr1, SUM(Field2) as expr2
, Sum(Field3) as expr3, 1 as SortOrder
FROM myTable
GROUP BY 1
) as T
ORDER BY T.SortOrder, T.ID

Open in new window

This adds the summation line to the bottom of the other data because of the addition of the "SortOrder" column inside the subquery.  The other method would be to copy the data to Excel, then use Automation to open the Excel file and insert a summation funtion as the last row of those columns where you need the sum
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40603874
Note that when you "export" calculated Access data to Excel, the results are not "formulas".
Any calculations end up being "hard-coded" values.

Meaning if you changed any values in the new excel file, those changes will not be reflected in the Totals...
So if all users understand that this is just a "snapshot", then all is well.
But know that an uninformed user may start changing values and not notice the totals remain unchanged.

In some cases like this, just the raw data is imported to Excel, then you can create your own Summaries (in Excel)

JeffCoachman
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 34

Expert Comment

by:PatHartman
ID: 40604315
The usual reason for exporting to Excel rather than as a report is because the user wants "live" data so I would never export hard calculated totals.  If he sorts the sheet, the totals get merged into the data and lost.  Either the user can add his own (otherwise why does he want a spreadsheet anyway?) or you can use OLE automation to add totals yourself after the data is exported.

If the user doesn't intend to manipulate the data, the best solution is to export as a report or pdf, in which case, you would include the totals.
0
 

Author Comment

by:CMILLER
ID: 40620081
I agree with all the comments but you said the magic word "USER", they all want to push a button and have it do everything.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40621811
I understand that users like the convenience of a button click and if they insist, I will automate the exported excel worksheet to have Excel produce the totals.  Exporting hard totals is a recipe for disaster.
0
 

Author Comment

by:CMILLER
ID: 40621838
I agree
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query to summarise data Like Pivot Table 3 29
MS Access Tables Linking 6 40
Access 2010 Query Syntax 5 18
can't find file error on web browser 1 10
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

929 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now