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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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.
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.
ASKER
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.
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.
ASKER
I agree
/gustav