Solved: Subtotal Method of Range Class Failed

[shaggles]

I'm trying to add subtotals to an Excel file I'm exporting from an Access database. I'm not sure I'm doing it right but I keep getting the error message 'Subtotal method of range class failed'. After the export my code is like this:

Dim xlx As Object
Dim xlw As Object

Set xlx = CreateObject("excel.application")
Set xlw = xlx.workbooks.Open("C:\Test.xls")

Set d = xlw.worksheets(1).range("A1").currentregion

d.subtotal groupby:=3, function:=xlcount, totallist:=Array(7, 8, 9) _
, replace:=True, pagebreaks:=False, summarybelowdata:=True

xlw.Save
xlw.Close False
xlx.Quit
Set xlw = Nothing
Set xlx = Nothing

Am I even close to doing this right? I'm using Access 2003 and Excel 2003.

[Bob Phillips]

Are you sure that the currentregion extends to columns 7, 8 and 9.

[shaggles]

I think so. Isn't CurrentRegion all rows and columns that aren't empty? I've tried it other ways but I get the same error.

[Bob Phillips]

CurrentRegion is the contiguous block from the start cell. You might want usedregion.

[shaggles]

I tried UsedRange too and got the same error message but before that one popped up I got a messagebox that said something like 'Excel cannot determine which row is the header' or something. I got that one when I tried range("A1:AD1605") too.

[Bob Phillips]

Have you got a headings row, with obvious heading like Name, Date and so on?

[shaggles]

Ahh! Thanks. That was part of the problem. I was inserting a double header row before I tried to add the subtotals so I took that out. I was still getting the error message though. So I tried using the subtotals wizard in excel and that worked so I looked at the cell formula and it was Subtotal(3, E3:E8) so I tried replacing function:=xlcount with function:=3 and that sort of worked. I had to try different number codes until I found the right one. Then I tried going back to the double header row and it still erred so I guess I needed both bits. Now I just need to figure out how to get different functions in the same summary row. I need to count some columns and sum others.

[Bob Phillips]

Do it with the macro recorder on, that will show you how.