Question Access 2007. Export query result to Excel and create SUM per column

[Petros]

Hi all,

I am using the below code to export the query result to excel.

Private Sub Excelexport_Click()

DoCmd.OutputTo acQuery, "AmountReportQuery", "MicrosoftExcel(*.xls)", "c:\MyReports\Amountreport.xls", True, ""

The query includes two fields. “Amount” (Column A in the spreadsheet) and “Amount2” (Column B in the spreadsheet). Both fields are numeric.

How do i extend my code to also create the SUM for column A and B?

Thanks!

 

[JANR]

In you current rutine you can't. If your not code savy the esiest way is to open the excel document after your output command and put in the formula manually.

[FONT=Arial][COLOR=#1c2837]Private Sub Excelexport_Click()[/COLOR][/FONT]
 
[FONT=Arial][COLOR=#1c2837]DoCmd.OutputTo acQuery, "AmountReportQuery", "MicrosoftExcel(*.xls)", "c:\MyReports\Amountreport.xls", True, ""[/COLOR][/FONT]
[FONT=Courier New][COLOR=red]Application.FollowHyperlink "c:\Myreports\Amountreport.xls",,True[/COLOR][/FONT]
[FONT=Arial][COLOR=black]End Sub[/COLOR][/FONT]

To do this automatically search this site or google for "Excel Automation"

JR

 

[Petros]

Thanks JANR, i will take a look at that..

 

[vbaInet]

Create a report > sum those two fields in the footer > export the report to Excel.

 

[Petros]

Thanks VbaInet...

Amateurs, such myself, have a tendency to complicate "things"..


Thanks!

 

[vbaInet]

We just need a nudge in the right direction sometimes