G Scott
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:
But it errors saying 'Object Needed' on the line:
What am I missing? Thanks for any assistance on this.
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
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
What am I missing? Thanks for any assistance on this.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That actually worked capricorn, just add to add the Excel reference. Whoops.
Thanks for the help.
Thanks for the help.
ASKER
Error 1004 - Subtotal Method of Range class failed. No clue.
Same block of code.