Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1157
  • Last Modified:

Access 2013 Export Query to Excel with Totals

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
CMILLER
Asked:
CMILLER
1 Solution
 
Gustav BrockCIOCommented:
The queries will only export what they display when opened manually.

/gustav
0
 
Dale FyeCommented:
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
 
Jeffrey CoachmanCommented:
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
PatHartmanCommented:
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
 
CMILLERAuthor Commented:
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
 
PatHartmanCommented:
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
 
CMILLERAuthor Commented:
I agree
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now