Run-time error 1004 Add fields method of pivot table class failed

[vba317]

I am using Access 2003. I am getting a run-time error 1004. I am not sure why it is failing. The failure point is highlighted in blue.
The range where the information is is AA2:AN52
The excel sheet name is SvcStats

Any help is appreciated

Public Sub RS_SvcStats(liCl As Long, liRPd As Long, strTitl As String, strSubTitl As String, li1stGrp As Long, li2ndGrp As Long)

' *****************
' *** CPT - HMA ***
' *****************
Dim strSQL As String
Dim rstDat As Recordset
Dim iRec As Integer
Dim iRw As Integer
Dim iCl As Integer

With goXL.ActiveSheet
    .Cells(1, 1).Value = (strTitl) & " - " & (strSubTitl)
    .Cells(2, 1).Value = "Including Data Through " & (GetFullMonthName(liRPd)) & " Billing Month"
    .Cells(1, 30).Value = (GetSubName(li1stGrp))
    .Cells(1, 31).Value = (GetSubName(li2ndGrp))
End With
iRw = 2
' Format Title
Call XLFormatFontSize(1, 2, 1, 1, 14)
Call XLFormatFontBold(1, 2, 1, 1)
' ****************
' *** ADD DATA ***
' ****************
strSQL = "SELECT uci,yr,monasdt,grpdsc1,grpdsc2,rcatdsc,cptdisplay,Sum(proctot) as sprc,Sum(rvu) as srvu" & _
                        ",Sum(chg) as schg,Sum(dr) as sdr,Sum(ca) as sca,Sum(wo) as swo,Sum(pmt) as spmt " & _
                    "FROM PROC_RptSrc_SvcStats " & _
                    "GROUP BY uci,yr,monasdt,grpdsc1,grpdsc2,rcatdsc,cptdisplay " & _
                    "ORDER BY grpdsc1,grpdsc2,rcatdsc,cptdisplay;"
Set rstDat = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
If Not rstDat.EOF Then
    With rstDat
        .MoveLast
        .MoveFirst
    End With
    For iRec = 1 To rstDat.RecordCount
        With goXL.ActiveSheet
            .Cells(iRw, 27).Value = (rstDat![UCI])
            .Cells(iRw, 28).Value = (rstDat![yr])
            .Cells(iRw, 29).Value = (rstDat![monasdt])
            .Cells(iRw, 30).Value = (rstDat![grpdsc1])
            .Cells(iRw, 31).Value = (rstDat![grpdsc2])
            .Cells(iRw, 32).Value = (rstDat![rcatdsc])
            .Cells(iRw, 33).Value = (rstDat![cptdisplay])
            .Cells(iRw, 34).Value = (rstDat![sprc])
            .Cells(iRw, 35).Value = (rstDat![schg])
            .Cells(iRw, 36).Value = (rstDat![sdr])
            .Cells(iRw, 37).Value = (rstDat![sca])
            .Cells(iRw, 38).Value = (rstDat![swo])
            .Cells(iRw, 39).Value = (rstDat![spmt])
            .Cells(iRw, 40).Value = (rstDat![srvu])
        End With
        iRw = iRw + 1
        rstDat.MoveNext
    Next iRec
End If
rstDat.Close
Set rstDat = Nothing
' *************************
' *** Build Pivot Table ***
' *************************
Range("A4").Select

'Original Code Changed 10/3/2014 TD Runtime error 1004 Pivot Table Class
'ActiveSheet.PivotTables("PivotTable1").AddFields ColumnFields:=Array("SvcMonth"), _
    PageFields:=Array("CPTDisplay", "ReportCategory", "" & (GetSubName(li2ndGrp)) & _
    "", "" & (GetSubName(li1stGrp)) & "")
'Changed Sheet name from SvcMonth to SvcStats
   
goXL.ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
                "R1C27:R" & (iRw - 1) & "C40").CreatePivotTable TableDestination:= _
                "R9C1", TableName:="PivotTable1", _
                DefaultVersion:=xlPivotTableVersion10
With goXL.ActiveSheet
[Blue] .PivotTables("PivotTable1").AddFields ColumnFields:=Array("SvcStats"), _
    PageFields:=Array("CPTDisplay", "ReportCategory", "" & (GetSubName(li2ndGrp)) & _
    "", "" & (GetSubName(li1stGrp)) & "") [/Blue]
.PivotTables("PivotTable1").PivotFields("Units").Orientation = xlDataField
.PivotTables("PivotTable1").PivotFields("Chgs").Orientation = xlDataField
.PivotTables("PivotTable1").PivotFields("Debits").Orientation = xlDataField
.PivotTables("PivotTable1").PivotFields("ContrAdj").Orientation = xlDataField
.PivotTables("PivotTable1").PivotFields("Write-off").Orientation = xlDataField
.PivotTables("PivotTable1").PivotFields("Pmts").Orientation = xlDataField
.PivotTables("PivotTable1").PivotFields("RVU").Orientation = xlDataField
.PivotTables("PivotTable1").PivotFields("SvcMonth").AutoSort xlDescending, "SvcMonth"
.PivotTables("PivotTable1").PivotFields("Sum of Units").Caption = "Total Units"
.PivotTables("PivotTable1").PivotFields("Sum of Chgs").Caption = "Total Chgs"
.PivotTables("PivotTable1").PivotFields("Sum of Debits").Caption = "Total Debits"
.PivotTables("PivotTable1").PivotFields("Sum of ContrAdj").Caption = "Total ContrAdj"
.PivotTables("PivotTable1").PivotFields("Sum of Write-off").Caption = "Total Write-off"
.PivotTables("PivotTable1").PivotFields("Sum of Pmts").Caption = "Total Pmts"
.PivotTables("PivotTable1").PivotFields("Sum of RVU").Caption = "Total RVU"
goXL.ActiveWorkbook.ShowPivotTableFieldList = False
End With
Application.CommandBars("PivotTable").Visible = False

With goXL.ActiveSheet
    .Range("B11").Select
    .PivotTables("PivotTable1").PivotCache.Refresh
End With
goXL.Cells(3, 1).Select
' *** Format Grid
Call XLFormatBackColorSolid(10, 10, 2, 14, 15)
Call XLFormatCenter(10, 10, 2, 14)
Call XLFormatFontBold(10, 10, 2, 14)
Call XLFormatNumberComma(11, 17, 2, 14)
Call XLFormatColWidth(1, 1, 20)
Call XLFormatColWidth(2, 14, 15)
Call XLFormatRowHeight(18, 18, 5)
Call XLFormatRight(11, 29, 1, 1)
' Add Formula Labels
With goXL.ActiveSheet
    .Cells(19, 1).Value = "Chg/Unit"
    .Cells(20, 1).Value = "Pmt/Unit"
    .Cells(22, 1).Value = "Resolution Rate"
    .Cells(23, 1).Value = "Gross Coll Rate"
    .Cells(24, 1).Value = "Net Coll Rate"
    .Cells(26, 1).Value = "RVU/Unit"
    .Cells(27, 1).Value = "Chg/RVU"
    .Cells(28, 1).Value = "Pmt/RVU"
    .Cells(30, 1).Value = "Remaining Balance"
    .Cells(31, 1).Value = "Percent Remaining"
End With
' Add Formulas
For iCl = 2 To 14
    With goXL.ActiveSheet
        .Cells(19, iCl).Formula = "=IF(" & (ConvColLet(iCl)) & "11=0,0," & (ConvColLet(iCl)) & "12/" & _
                                    (ConvColLet(iCl)) & "11)"
        .Cells(20, iCl).Formula = "=IF(" & (ConvColLet(iCl)) & "11=0,0," & (ConvColLet(iCl)) & "16/" & _
                                    (ConvColLet(iCl)) & "11)"
        .Cells(22, iCl).Formula = "=IF((" & (ConvColLet(iCl)) & "16+" & (ConvColLet(iCl)) & "15+" & _
                                    (ConvColLet(iCl)) & "14-" & (ConvColLet(iCl)) & "13)=0,0," & (ConvColLet(iCl)) & _
                                    "16/(" & (ConvColLet(iCl)) & "16+" & (ConvColLet(iCl)) & "15+" & _
                                    (ConvColLet(iCl)) & "14-" & (ConvColLet(iCl)) & "13))"
        .Cells(23, iCl).Formula = "=IF(" & (ConvColLet(iCl)) & "12=0,0," & (ConvColLet(iCl)) & "16/" & _
                                    (ConvColLet(iCl)) & "12)"
        .Cells(24, iCl).Formula = "=IF((" & (ConvColLet(iCl)) & "12-" & (ConvColLet(iCl)) & "14)=0,0," & _
                                    (ConvColLet(iCl)) & "16/(" & (ConvColLet(iCl)) & "12-" & (ConvColLet(iCl)) & "14))"
        .Cells(26, iCl).Formula = "=IF(" & (ConvColLet(iCl)) & "11=0,0," & (ConvColLet(iCl)) & "17/" & _
                                    (ConvColLet(iCl)) & "11)"
        .Cells(27, iCl).Formula = "=IF(" & (ConvColLet(iCl)) & "17=0,0," & (ConvColLet(iCl)) & "12/" & _
                                    (ConvColLet(iCl)) & "17)"
        .Cells(28, iCl).Formula = "=IF(" & (ConvColLet(iCl)) & "17=0,0," & (ConvColLet(iCl)) & "16/" & _
                                    (ConvColLet(iCl)) & "17)"
        .Cells(30, iCl).Formula = "=" & (ConvColLet(iCl)) & "12+" & (ConvColLet(iCl)) & "13-" & (ConvColLet(iCl)) & _
                                    "14-" & (ConvColLet(iCl)) & "15-" & (ConvColLet(iCl)) & "16"
        .Cells(31, iCl).Formula = "=IF(" & (ConvColLet(iCl)) & "12=0,0," & (ConvColLet(iCl)) & "30/" & _
                                    (ConvColLet(iCl)) & "12)"
    End With
Next iCl
' Format
Call XLFormatNumberComma_OneDcml(19, 20, 2, 14)
Call XLFormatRowHeight(21, 21, 5)
Call XLFormatBottomLine(21, 2, 14)
Call XLFormatNumberPercent(22, 24, 2, 14)
Call XLFormatRowHeight(25, 25, 5)
Call XLFormatBottomLine(25, 2, 14)
Call XLFormatNumberComma_OneDcml(26, 28, 2, 14)
Call XLFormatRowHeight(29, 29, 5)
Call XLFormatBottomLine(29, 2, 14)
Call XLFormatNumberComma(30, 30, 2, 14)
Call XLFormatNumberPercent(31, 31, 2, 14)
Call XLFormatRowHeight(32, 32, 5)
Call XLFormatBottomLine(32, 2, 14)
Call XLFormatLeftLine(2, 18, 32)
Call XLFormatLeftLine(14, 18, 32)
Call XLFormatRightLine(14, 18, 32)
Call XLFormatFontSize(9, 32, 2, 14, 12)
goXL.ActiveSheet.Cells(3, 1).Select

End Sub

 

[PHV]

Anyway, I'd replace this:
Range("A4").Select
with this:
goXL.ActiveSheet.Range("A4").Select

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

So why a PivotTable in Excel?

Why an on-the-fly, coded approach?

Will this be done using various source tables?

Well my bias is grinning thru. I'd be designing this in Excel. The Excel PivotTable is designed as a quick report solution. Doing the entire design in code is a real chore comparitavily. I'm asking, WHY your approach from Access? What are the project requirements that would dictate such an approach?

 

[vba317]

The system I am working with was designed before I was hired. The method that is being used is to create the reports data using SQL 2005. The report data is linked to access through an ODBC connection. Than the data is exported to excel from Access. Now that the client managers have bought into the excel templates I am not allowed to change them. The worst part for me is the client managers through a PHP website which is connected to the SQL database can change, add or subtract the reports they want anytime. Right now there are 107 different reports that they can choose from and they can be sorted two different ways by 8 different categories and filtered two different ways by 8 different categories. Finally there is the time crunch. Once the data is released to me the SQL processing and the Access processing can take up to 3 hours. The client that I had this issue with needed there reports on Friday. The report that this error was based on was a report the manager had just added. So I convinced her to remove it and she did. This particular client gets a package with over 425 reports in it. If I had to add the reports manually it would take me too long. Because there are over 25 files and the manager wants the reports in a certain tab on all the reports and of course it cant be in the beginning or the end.

Tom

 

[SkipVought]

okay.

 

[vba317]

The source table data is on the sheet where the pivot table is. One final thought. The system I am working on will be phased out in the next three to six months. The company will be upgrading to SQL 2012 BI edition. So if anyone had some recommendations on training material it would be appreciated. I digress my apologies.

Tom