Link to home
Create AccountLog 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
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
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.