Solved

Access 2013 Export Query to Excel with Totals

Posted on 2015-02-11
7
876 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 35

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 35

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Create report using crosstab query 11 29
"Fit to Drawing" different Layers 7 24
Access Open Report with SQL Parameter 11 30
Problem with Excel array 11 28
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

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