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