Problems with Automating range of sum for Subtotal method in excel after access export

[Bob]

Hi all,
I've copied a recordset to excel and I want to do some formatting on
it once its in excel. I'm having a real problem with the subtotal
method - its the 'TotalList:=array(1,2,3) etc that I'm having trouble
with. I need to insert an array into that value to get totals for
however many columns there are after the 4th column. with the current
code I'm getting a 'Subtotal method of range class failed' error.
Code below:
Any help would be appreciated!

Dim rs As DAO.Recordset
Dim intMaxCol As Integer
Dim intMaxRow As Integer
Dim objXL As Excel.Application
Dim objWkb As Workbook
Dim objSht As Worksheet
Dim Row As Integer
Dim Col As Integer
Dim TotRange As String
Dim NumRange As String
Dim ArrCount As Integer
Dim ArrString As String
Dim ArrInt As Integer

Row = 1
Col = 1

Set rs = CurrentDb.OpenRecordset(TableName, dbOpenSnapshot)

intMaxCol = rs.Fields.Count + 1

If rs.RecordCount > 0 Then
rs.MoveLast: rs.MoveFirst
intMaxRow = rs.RecordCount
Set objXL = New Excel.Application

TotRange = CLetter(1) & ":" & CLetter(CLng(intMaxCol))
NumRange = CLetter(4) & ":" & CLetter(CLng(intMaxCol))


With objXL
.Visible = True
Set objWkb = .Workbooks.Add
Set objSht = objWkb.Worksheets(1)

With objSht
For FNameInt = LBound(FName) To UBound(FName)
.Cells(Row, Col) = FName(FNameInt)
Col = Col + 1
Next

.Range(.Cells(2, 1), .Cells(intMaxRow,
intMaxCol)).CopyFromRecordset rs

ArrInt = 4

For ArrCount = 4 To intMaxCol

Select Case ArrCount
Case 4
ArrString = 4 & ","
Case intMaxCol
ArrString = ArrString & ArrCount
Case Else
ArrString = ArrString & ArrCount & ","
End Select

Next

'.Range("A3").Select

'Select Case intMaxCol

.Range(.Cells(1, 1), .Cells(intMaxRow + 1,
intMaxCol - 1)).Subtotal GroupBy:=1, Function:=xlSum, _
TotalList:=Array(Split(ArrString, ",")),
Replace:=True, PageBreaks:=False, SummaryBelowData:=True


.Columns(TotRange).AutoFit



End With
End With
End If

 

[strive4peace]

Hi Bob,

you need to preface the .Cells(row,col) reference with the sheet object
variable too...

for instance:

'~~~~~~~~~~
With xlsSht.range(xlsSht.cells(5, 1), xlsSht.cells(mRow - 1, 8))
.Subtotal _
GroupBy:=1, Function:=-4157, _
TotalList:=Array(4, 5, 6, 7, 8), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End With
'~~~~~~~~~~


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*