Sutotalling a column from Access to Excel?

  • Thread starter Thread starter Deville
  • Start date Start date
D

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.
 
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.
 
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...
 
Sure... but to give you an idea, you need to tell me how you'd know what the
start of the column would be?
 
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.
 
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 _
& ")"
 
Back
Top