Sutotalling a column from Access to Excel?

[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.

 

[Ken Snell [MVP]]

You'll need to get the number of records from your recordset, and then use
Automation to go to the appropriate cell and put a formula in that cell:

xlsApp.Workbooks("workbookname").Worksheets("worksheetname"). _
Cells(RecordsetName.RecordCount + 4, NumberOfColumn).Formula = _
"=Sum(ColumnLetter3:ColumnLetter" & RecordsetName.RecordCount +2 _
& ")"

Note that I'm using NumberOfColumn and ColumnLetter as generic values for
the real values.

 

[Deville]

Thanks Ken I got it to work. But I have 1 more
issue...Instead of hard coding the Column3:ColumnLetter do
you know of a way to dynamically retreive the start of the
column and end position...

 

[Ken Snell [MVP]]

Sure... but to give you an idea, you need to tell me how you'd know what the
start of the column would be?

 

[Deville]

The start of the New Column will always be in the 3rd row.
It starts there because I create a Header Section then add
the field headings. Then the data is dropped in to the
next Row using copyrecordset. I can get the number of the
column going left to right by adding 1 to a variable until
I reach the name of the column I want to sum up.

 

[Ken Snell [MVP]]

I've now made ColumnLetter a variable in the original code:

xlsApp.Workbooks("workbookname").Worksheets("worksheetname"). _
Cells(RecordsetName.RecordCount + 4, NumberOfColumn).Formula = _
"=Sum(" & ColumnLetter & "3:" & ColumnLetter & RecordsetName.RecordCount
+2 _
& ")"