Excel Subtotal from Access/VBA

[kwvh]

This is eating my lunch. I am trying to calculate a subtotal in an Excel spreadsheet via automation/VBA

Code: Select all

    With ApXL
        .Range("A2").Select
        .Selection.Subtotal GroupBy:=4, Function:=xlSum, TotalList:=Array(12), _
            Replace:=True, PageBreaks:=False, SummaryBelowData:=True
        .ActiveSheet.Outline.ShowLevels RowLevels:=2
        .Columns("L:L").Style = "Currency"
        .Rows("1:1").Font.Bold = True
    End With

I keep getting an error on the ".Selection.sub . .." line. The error message is "The command could not be completed by using the range specified. Select a single cell within the range and try the command again."

Any help is greatly appreciated.

Ken

[HansV]

In a quick test, your code works without problems for me. There is no need to select a range, you can replace

Code: Select all

        .Range("A2").Select
        .Selection.Subtotal GroupBy:=4, Function:=xlSum, TotalList:=Array(12), _
            Replace:=True, PageBreaks:=False, SummaryBelowData:=True

with

Code: Select all

        .Range("A2").Subtotal GroupBy:=4, Function:=xlSum, TotalList:=Array(12), _
            Replace:=True, PageBreaks:=False, SummaryBelowData:=True

and you might try the following to see if it solves the problem:

Code: Select all

        .Range("A2").CurrentRegion.Subtotal GroupBy:=4, Function:=xlSum, TotalList:=Array(12), _
            Replace:=True, PageBreaks:=False, SummaryBelowData:=True

If not, I'd need to see (a copy of) the worksheet.