Sum Exported data automatically from Access database

[microsoft]

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

 

[joel]

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]

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]

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]

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]

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]

Hi

This line of code is giving an error

"LastRow = .Range("A" & 65536).End(xlUp).Row"

Error - Run time error 1004
Application defined or object defined error

Hope to hear from you soon.

Thanks again

 

[joel]

You need to add the excel refernce to Access. From Access VBA window

Tools - Reference - Microsft Excel 11.0 object library.

Or latest version on your PC.
11.0 is excel 2003
10.0 is excel 2002
9.0 is excel 2000

I figured out what was wrong at work. I was getting a read-only workbook
using createobject. I changed to getobject and the code is working except I
get a Pop up window asking if I want to save in Excel 5.0. I have excel 2002
at home where I'm working. the pop up window may be hidden behind other
window. If the code looks like it has stopped close some of the windows
until you find the pop up. I have to leave now. I'm not sure if you get the
pop up window since you are using excel 2000. I you get the pop up and want
me to fix the problem let me know and I will work on it tomorrow. I'm
SUMMING column G. You can change the column to any column and add additional
columns. I' also using column A to determine the last row. This can also be
changed.

Private Sub Command0_Click()


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

Set bk = GetObject(pathname:=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

 

[joel]

Remove the line

MyQuery = "AC_WC_DEMOG_CRSTAB Query"


I added this to test with one of my databases. You just neeed the line

MyQuery = "Excel Exportb"

 

[microsoft]

Thanks for all your help, I got the issue resolved.