Link to home
Start Free TrialLog in
Avatar of G Scott
G ScottFlag for United States of America

asked on

Subtotal Excel worksheet from Access generated XLS file

Hope that title makes sense. I am doing a:

DoCmd.OutputTo acOutputForm, "TESTTABLEFORMEXPORT", acFormatXLS, "c:\TestForm.xls", False

I then want to take that TestForm.xls and Subtotal the data in it by Director. Here is what I have:

Dim xlObj As Object, xlFile As String, j As Long
    xlFile = "c:\TestForm.xls"
    Set xlObj = CreateObject("excel.application")
    xlObj.workbooks.Open xlFile
    With xlObj
    
    .cells.Select
    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4, 5, 6, 7, _
        8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20), Replace:=True, PageBreaks:=False, _
        SummaryBelowData:=True
        
    End With
    xlObj.Quit

Open in new window


But it errors saying 'Object Needed' on the line:
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4, 5, 6, 7, _
        8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20), Replace:=True, PageBreaks:=False, _
        SummaryBelowData:=True

Open in new window


What am I missing?  Thanks for any assistance on this.  
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of G Scott

ASKER

Hey capricorn, I tried it and now:

Error 1004 - Subtotal Method of Range class failed. No clue.

Same block of code.
Avatar of G Scott

ASKER

That actually worked capricorn, just add to add the Excel reference. Whoops.

Thanks for the help.