Solved

Access 2013 Export Query to Excel with Totals

Posted on 2015-02-11
7
795 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I agree
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…

772 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

12 Experts available now in Live!

Get 1:1 Help Now