Sum a Spreadsheet Column from Access
 

How do I get this to work when calling it from Access?

I'm calling a sql server stored procedure from access and placing the
recordset into excel. I want to total columns "H","I","J" and "K". This
code works in a macro I have in excel but doesn't work when called from
Access.

When i try to compile it , I get a compile error and the word "Sum" is
highlighted in the columntotal sub procedure

xlApp.Workbooks(1).Worksheets(4).Select
ColumnTotal "H"
ColumnTotal "I"
ColumnTotal "J"
ColumnTotal "K"

End Sub

Sub ColumnTotal(ByVal strColumn As String)
Cells(Rows.Count, strColumn).End(xlUp).Offset(2, 0).Value = _
Application.Sum(Columns(strColumn))
End Sub
--
Billy Rogers

Dallas,TX

Currently Using Office 2000 and Office 2003

Sum a Spreadsheet Column from Access
 

In access, create a summation query referencing the worksheet.
Grab the value of the field.
One step.
Done.


HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"BillyRogers" wrote:

How do I get this to work when calling it from Access?

I'm calling a sql server stored procedure from access and placing the
recordset into excel. I want to total columns "H","I","J" and "K". This
code works in a macro I have in excel but doesn't work when called from
Access.

When i try to compile it , I get a compile error and the word "Sum" is
highlighted in the columntotal sub procedure

xlApp.Workbooks(1).Worksheets(4).Select
ColumnTotal "H"
ColumnTotal "I"
ColumnTotal "J"
ColumnTotal "K"

End Sub

Sub ColumnTotal(ByVal strColumn As String)
Cells(Rows.Count, strColumn).End(xlUp).Offset(2, 0).Value = _
Application.Sum(Columns(strColumn))
End Sub
--
Billy Rogers

Dallas,TX

Currently Using Office 2000 and Office 2003