Exporting to Excel..adding Subtotals

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a need to export an access recordset to Excel. I have been asked to
now add subtotals to this Excel report. Which normally would not be an
issue, however, the row (record count) is not constant. I have search hi and
low and have tried many different things, only to hit a dead end...Does
anyone have any code that they could provide that may give me a bump in the
right direction. The actual Export works great. (Thanks to the many people
in these NewsGroups)..

The report will need to subtotal based on the value in Column A, the column
that will need to be subtotaled is column P and Column Q.

Any help or ideas is appreciated.
 
The trick is to know where the first row is and where the last row is. I
don't know how you are doing the export, so I can't give an exact answer.
The method I use is the CopyFromRecordset. Then I know for sure where the
first row is.

xlSheet.Cells(2, 1).CopyFromRecordset rstItms

Then to find the last row

Selection.End(xlDown).Select

Then a modified version of this code should get you what you need.

lngFirstDataRow = intX + 3
lngLastDataRow = lngFirstDataRow + lngItmCount + lngDetailCount
With xlSheet
.Range(.Cells(lngFirstDataRow, 1), _
.Cells(lngLastDataRow, 19)).Subtotal groupBy:=1,
Function:=xlSum, _
totalList:=Array(3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15,
16, 17, 18, 19)
End With
 
Back
Top