See if this works. I having problems witth my PC and I can't test
evverything. I,m opening the workbook and adding SUM to column g.

Private Sub Command0_Click()


FName = "c:\temp\file.xls"
MyQuery = "Excel Exportb"
DoCmd.OutputTo _
ObjectType:=acOutputQuery, _
ObjectName:=MyQuery, _
OutputFormat:=acFormatXLS, _
OutputFile:=FName, _
AutoStart:=True

Set obj = CreateObject("excel.application")
Set bk = obj.Workbooks.Open(FileName:=FName)

With bk.Sheets(MyQuery)
LastRow = .Range("A" & 65536).End(xlUp).Row
NewRow = LastRow + 1
.Range("G" & NewRow).Formula = _
"=SUM(G2:G" & LastRow & ")"
End With
bk.Close savechanges:=True
Set obj = Nothing
End Sub


"microsoft" wrote:

Hi Joel

Thanks for your assistance thus far. Kindly see the code below. This is
being done in Access 2000.

Private Sub Command0_Click()

DoCmd.OutputTo acOutputQuery, "Excel Exportb", acFormatXLS, "c:\file.xls",
True

Set Sht = activeworksheet
LastRow = Sht.Range("A" & Rows.Count).end(Xlup)
NewRow = LastRow + 1
Sht.Range("A" & NewRow).formula = _
"=SUM(A1:A" & LastRow & ")"

End Sub

I await your response.

"joel" wrote:

My code should be placed at the end of the macro you already have behind the
button which moves the data to excel. There should be an excel object in the
access macro where you would add the new code. If you post the access macro
I can add the changes.

"microsoft" wrote:

Hi

Thanks for your response. As I am a novice at this, kindly confirm where
this code would have to be placed. Should it be placed behind the button
which intiates the export from access to excel

Thanks again

"joel" wrote:

The code below will add a fomula into the worksheet with the total.

Set Sht = activeworksheet
LastRow = Sht.Range("A" & Rows.Count).end(Xlup)
NewRow = LastRow + 1
Sht.Range("A" & NewRow).formula = _
"=SUM(A1:A" & LastRow & ")"




"microsoft" wrote:

Hi

I created an Access database which exports data to Excel. I would like
assistance in creating the required code which would allow me to sum some
selected columns automatically after the data is exported (kindly note that I
do not want any additional columns created to capture the total figure).

Note that the rows from the database may vary hence the excel rows will also
vary. Code to determine the row count would need to be included.

Thanks in advance