Solved

Access 2013 Export Query to Excel with Totals

Posted on 2015-02-11
7
964 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 50

Expert Comment

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

/gustav
0
 
LVL 48

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 37

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 37

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

710 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