Subtotalling a column of data?

[Deville]

Hello all,
I am going from Ms Access to Ms Excel. Using VBA I
create the spreadsheet and create a header section. Next I
dump the data into the sheet using Copyrecordset. My
question is how can I do a subtotal of a column starting a
row 3 until the last cell containing data. Also, the
subtotal cell needs to 1 below the last cell containng
data.

Thnx

 

[Tom Ogilvy]

Dim rng as Range
Dim icol as 5 ' column E as an example.
icol = 5
set rng = Range(cells(3,icol),Cells(rows.count,icol).End(xlup))

rng(1).offset(rng.rows.count) = "=Sum(" & rng.Address & ")"


Change the 1 to the column where you want to do the subtotal.

 

[Deville]

The 1st time I tried this way it worked. But now I get an
error almost every other time I run this.
Run-time error '1004': Method '~' of object '~' failed
Would anyone happen to know how to code this in Ms Access
VBA referencing the Excel Object.

 

[Tom Ogilvy]

Look in the task manager and close any running instances of Excel. Close
Access and re open. Good chance that you have unreleased instances of
Excel.

Dim rng as Excel.Range
Dim icol as long
Dim oxlSheet as Excel.Worksheet
Dim oxlApp as Excel.Application

set oxlApp = application.GetObject( , "Excel.Application")
icol = 5
set oxlSheet = oxlApp.Workbooks("Book1.xls").worksheets(1)
set rng = oxlSheet.Range(oxlSheet.cells(3,icol), _
oxlSheet.Cells(rows.count,icol).End(xlup))

rng(1).offset(rng.rows.count) = "=Sum(" & _
rng.Address & ")"


Adjust to fit with your current code.