RunTime error 1004 Unable to get PivotFields property of the pivot table class 2

[vba317]

I am getting this runtime error 1004 when my code is trying to populate a pivot table. The template with the pivot table on it is currently open. The stop point is highlighted in green. strFld = Facility, li1stGrp = 3, li2ndGrp=2? Any help is appreciated. Tom

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

Dim strSQL As String
Dim rstDat As Recordset
Dim iRec As Integer
Dim iRw As Integer
Dim strFld As String

' Set SubGroup Titles
With goXL.ActiveSheet
    .Cells(1, 107).Value = (GetSubName(li1stGrp))
    .Cells(1, 108).Value = (GetSubName(li2ndGrp))
End With
' Get SvcSummary Data
strSQL = "SELECT uci,ptype,grpdsc1,grpdsc2,rcatdesc,svcyr,monasdt,Sum(amt) as tamt " & _
            "FROM PROC_RptSrc_SvcPivot " & _
            "GROUP BY uci,ptype,grpdsc1,grpdsc2,rcatdesc,svcyr,monasdt " & _
            "ORDER BY ptype;"
Set rstDat = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
iRw = 2
If Not rstDat.EOF Then
    With rstDat
        .MoveLast
        .MoveFirst
    End With
    For iRec = 1 To rstDat.RecordCount
        With goXL.ActiveSheet
            .Cells(iRw, 105).Value = (rstDat![UCI])
            .Cells(iRw, 106).Value = (rstDat![ptype])
            .Cells(iRw, 107).Value = (rstDat![grpdsc1])
            .Cells(iRw, 108).Value = (rstDat![grpdsc2])
            .Cells(iRw, 109).Value = (rstDat![rcatdesc])
            .Cells(iRw, 110).Value = (rstDat![svcyr])
            .Cells(iRw, 111).Value = (rstDat![monasdt])
            .Cells(iRw, 112).Value = (rstDat![tamt])
        End With
        iRw = iRw + 1
        rstDat.MoveNext
    Next iRec
'End If Moved this end if to end of routine 7/8/2013 TD
rstDat.Close
Set rstDat = Nothing
' Reset Data Name Range
ActiveWorkbook.Names.Add Name:="SvcData", RefersToR1C1:="=Svc_Summary!R1C105:R" & (iRw - 1) & "C112"
' Refresh
With goXL.ActiveSheet
    .Range("B9").Select
    '.PivotTables("PivotTable1").PivotCache.Refresh
    'Caused error on pivot chart commented out 07/08/2013
End With
' Configure Sub Groups for Filtering
If (li1stGrp = 1) Then ' No First Filter
    If (li2ndGrp = 1) Then ' No First or Second Filter
        ' Do nothing
    Else ' Second Filter Only
        strFld = (GetSubName(li2ndGrp))
        With ActiveSheet.PivotTables("PivotTable1").PivotFields(strFld)
            .Orientation = xlPageField
            .Position = 1
        End With
    End If
Else ' First Filter
    If (li2ndGrp = 1) Then ' No Second Filter, First Only
        strFld = (GetSubName(li1stGrp))
        With ActiveSheet.PivotTables("PivotTable1").PivotFields(strFld)
            .Orientation = xlPageField
            .Position = 1
        End With
    Else ' Both First and Second Filter
        strFld = (GetSubName(li1stGrp))
[GREEN]        With ActiveSheet.PivotTables("PivotTable1").PivotFields(strFld) [/GREEN]
            .Orientation = xlPageField
            .Position = 1
        End With
        strFld = (GetSubName(li2ndGrp))
        With ActiveSheet.PivotTables("PivotTable1").PivotFields(strFld)
            .Orientation = xlPageField
            .Position = 1
        End With
    End If
End If
End If 'Added 7/8/2013 TD
Application.CommandBars("PivotTable").Visible = False

End Sub

 

[lameid]

You posted in an Access forum so I am going to assume you are trying to automate Excel...

No where do I see you setting a reference to an Excel Applicaton object... Anything done in Excel will have to be run off that object.


This snippet from one of my procedures may help you see what I mean.

Set XLAPP = CreateObject("Excel.Application")
       With XLAPP
          '.Visible = True  'When you debug, sometimes it helps to see what is going on
          lngArrayPos = .Workbooks.Count
          .Workbooks.Open FileName:= _
               strFilePath
          Set XLWorkBook = .Workbooks(lngArrayPos + 1)
         'Select All Data
         Set XLRptSheet = .Worksheets.Add(Before:=.Worksheets(1))
         
         '.....

      End With

Also when automating Excel, any oject with active in it is the enemy... Explicitly reference objects for sheets, workbooks etc.

 

[vba317]

You are right I have an access application that is creating excel spreadsheets.

The code you are looking for is this:
The module that defines the Excel Application is
in another module
Public goXL As Excel.Application ' The Excel Object variable
Public gbXLPresent As Boolean ' Identifies whether Excel is present upon starting

Private Sub cmdProcess_Click()

Dim strSQL As String ' String used for building SQL strings
Dim rstUCI As Recordset ' Recordset for ClntID/UCI list
Dim liClntID As Long ' Client ID
Dim strUCI As String ' UCI
Dim strCompany As String ' Company Mnemnonic
Dim strFilePath As String ' Path to File Location
Dim strTemplatePath As String ' Path to Templates
Dim iZ As Integer ' Counter
Dim iY As Integer ' Counter
Dim rstQ As Recordset ' Recordset for Report Queue
Dim liQID As Long ' Queue ID
Dim liQRptPd As Long ' Queued Report Period ID
Dim liRptID As Long ' Report ID
Dim strSrcTable As String ' Source Table Name
Dim strTemplate As String ' Template Name
Dim strTabName As String ' Sheet Tab Name
Dim strRptTitle As String ' Report Title
Dim strRptSubTitle As String ' Report SubTitle
Dim liGrp1 As Long ' First Group Identifier
Dim liGrp2 As Long ' Second Group Identifier
Dim strFileName As String ' File Save Name
Dim strStartTime As String
Dim strEndTime As String
Dim intDay As Integer
Dim intDayTotal As Integer
Dim dteTime As Date
Dim dteDay As Date
Dim strStartDate As String
Dim strEndDate As String
Dim intDif As Integer
Dim intI As Integer
Dim intCount As Integer
Dim StartDate As Date
Dim EndDate As Date

'Create Time Stamp

' Create Excel Instance
Call XLCreate ' New Excel Instance - goXL
If gbXLPresent = True Then ' If Excel installed

    ' Post Queued Reports to Temp Table
    With DoCmd
        .Maximize
        .SetWarnings False
        .OpenQuery "000_ClearProcessRptQueue"
        .OpenQuery "001_PostRptsToQueue" ' Post Reports in Queue to Process
    End With
    ' Make Sure Folders are Updated
    Call RptCreateFolders
    ' Get List of UCI to cycle through
    strSQL = "SELECT u.clntid,u.uci " & _
                "FROM [_UCI_Select] u " & _
                "GROUP BY u.clntid,u.uci " & _
                "ORDER BY u.uci;"
    Set rstUCI = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    If Not rstUCI.EOF Then ' Make sure not empty set
        With rstUCI
            .MoveLast
            .MoveFirst
        End With
        For iZ = 1 To rstUCI.RecordCount
            ' Get ClntID UCI Variables
            liClntID = (rstUCI![clntid])
            strUCI = (rstUCI![UCI])
            strCompany = Trim(GetCompany(liClntID))
            strFilePath = "\\salmfilesvr1\Public\Client Services\AutoRpts\_Rpts\" & (strCompany) & "\" & (strUCI) & "\"
            strTemplatePath = "\\salmfilesvr1\Public\Client Services\AutoRpts\Templates\"
 strSQL = "INSERT INTO PROC_FYInfo (compid,compmne,compdesc,clntid,uci,clntname,rptpd,rptpddiff,fy,fydiff" & _
                                    ",fyord,MnthAsDt,mon,monfull,yr,MonShNm,MonFullNm,days,last3mondys,lastdayasdt,impfl ) " & _
                        "SELECT cl.compid,cmp.compmne,cmp.compdesc,fy.clntid,fy.uci,cl.clntname,fy.rptpd,fy.rptpddiff" & _
                                    ",fy.fy,fy.fydiff,fy.fyord,pd.monasdt,pd.mon_shnm,pd.mon_nm,pd.yr" & _
                                    ",((pd.mon_shnm) & ' ' & (pd.yr)),((pd.mon_nm) & ' ' & (pd.yr)),pd.days,pd.last3mondys" & _
                                    ",pd.lastdayasdt,fy.imp " & _
                        "FROM ((dbo_rpt_FYInfo fy  " & _
                            "INNER JOIN (dbo_rpt_Clients cl " & _
                            "INNER JOIN dbo_dic_Company cmp ON cl.compid = cmp.compid) ON fy.clntid = cl.clntid) " & _
                            "INNER JOIN dbo_dic_Period pd ON fy.rptpd = pd.pd) " & _
                            "INNER JOIN PROC_ReportQueue rq ON fy.clntid = rq.clntid " & _
                        "WHERE (fy.clntid = " & (liClntID) & ") " & _
                        "GROUP BY cl.compid,cmp.compmne,cmp.compdesc,fy.clntid,fy.uci,cl.clntname,fy.rptpd,fy.rptpddiff,fy.fy" & _
                                    ",fy.fydiff,fy.fyord,pd.monasdt,pd.mon_shnm,pd.mon_nm,pd.yr,((pd.mon_shnm) & ' ' & (pd.yr))" & _
                                    ",((pd.mon_nm) & ' ' & (pd.yr)),pd.days,pd.last3mondys,pd.lastdayasdt,fy.imp " & _
                        "ORDER BY cmp.compmne,fy.uci,fy.rptpd;"
                       CurrentDb.Execute strSQL
Code 
'
'
'
'
Code

Select Case liRptID
                        Case 1, 31, 32 ' Executive Summary - Single
                            Call RS_ExecSumm_Single(liClntID, liQRptPd, strRptTitle, strRptSubTitle)
                        Case 2, 36, 37 ' Executive Summary - Grouped
                            If (liGrp2 = 1) Then
                                Call RS_ExecSumm_Group1(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            Else
                                Call RS_ExecSumm_Group2(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            End If
                        Case 3 ' Service Summary Pivot
                            Call RS_SvcPivot(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                        Case 4 ' Charge Detail
                            Call RS_ChgDetail(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                        Case 5 ' Payment Detail
                            Call RS_PmtDetail(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                        Case 6 ' Charge Resolution Trends
                            Call RS_ChgResTrends_Single(liClntID, liQRptPd, strRptTitle, strRptSubTitle)
                        Case 7 ' Procedure Summary - Single
                            Call RS_ProcSummary_Single(liClntID, liQRptPd, strRptTitle, strRptSubTitle)
                        Case 8 ' Charge Summary - Single
                            Call RS_ChgSummary_Single(liClntID, liQRptPd, strRptTitle, strRptSubTitle)
                        Case 9 ' Receipt Summary - Single
                            Call RS_PmtSummary_Single(liClntID, liQRptPd, strRptTitle, strRptSubTitle)
                        Case 10 ' Adjustment Summary - Single
                            Call RS_AdjSummary_Single(liClntID, liQRptPd, strRptTitle, strRptSubTitle)
                        Case 11 ' Adjustments/Write-offs - Single
                            Call RS_AdjWOSumm_Single(liClntID, liQRptPd, strRptTitle, strRptSubTitle)
                        Case 12 ' Admin Adjustment - Single
                            Call RS_AdminAdjDetail_Single(liClntID, liQRptPd, strRptTitle, strRptSubTitle)
                        Case 13 ' Performance Trends
                            Call RS_PerfTrends_Single(liClntID, liQRptPd, strRptTitle, strRptSubTitle)
                        Case 14, 50, 52 ' A/R Aging - Single
                            Call RS_ARAge_Single(liClntID, liQRptPd, strRptTitle, strRptSubTitle)
                        Case 15 ' A/R Insurance - Single
                            Call RS_ARSumm_Ins_Single(liClntID, liQRptPd, strRptTitle, strRptSubTitle)
                        Case 16 ' Trial Balance - Single
                            Call RS_TB_Single(liClntID, liQRptPd, strRptTitle, strRptSubTitle)
                        Case 17, 56, 58 ' A/R Rolling - Single
                            Call RS_ARRolling_Single(liClntID, liQRptPd, strRptTitle, strRptSubTitle)
                        Case 18, 91, 93 ' A/R Performance - Single
                            Call RS_ARPerf_Single(liClntID, liQRptPd, strRptTitle, strRptSubTitle)
                        Case 19 ' Client Breakdown - Grouped
                            If (liGrp2 = 1) Then
                                Call RS_Breakdown_Group1(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            Else
                                Call RS_Breakdown_Group2(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            End If
                        Case 20, 75 ' CPT Detail - Single
                            Call RS_CPTDetail_Single(liClntID, liQRptPd, strRptTitle, strRptSubTitle)
                        Case 21 ' CPT Procedure Summary - Single
                            Call RS_CPTProcSumm_Single(liClntID, liQRptPd, strRptTitle, strRptSubTitle)
                        Case 23, 74 ' Comparison Summary - Grouped
                            If (liGrp2 = 1) Then
                                Call RS_CompSumm_Group1(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            Else
                                Call RS_CompSumm_Group2(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            End If
                        Case 24 ' Charge Lag - Single
                            Call RS_ChgLag(liClntID, liQRptPd, strRptTitle, strRptSubTitle)
                        Case 25 ' Payment Lag - Single
                            Call RS_PmtLag(liClntID, liQRptPd, strRptTitle, strRptSubTitle)
                        Case 26, 51, 53 ' A/R Aging - Grouped
                            If (liGrp2 = 1) Then
                                Call RS_ARAge_Group1(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            Else
                                Call RS_ARAge_Group2(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            End If
                        Case 27, 76 ' CPT Detail - Grouped
                            If (liGrp2 = 1) Then
                                Call RS_CPTDetail_Group1(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            Else
                                Call RS_CPTDetail_Group2(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            End If
                        Case 28, 60, 62 ' AAR - Single
                            Call RS_AAR_Single(liClntID, liQRptPd, strRptTitle, strRptSubTitle)
                        Case 29, 61, 63 ' AAR - Grouped
                            Call RS_AAR_Group(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                        Case 30, 40 ' Executive Summary RVU - Single
                            Call RS_ExecSummRVU_Single(liClntID, liQRptPd, strRptTitle, strRptSubTitle)
                        Case 35, 43 ' Executive Summary RVU - Grouped
                            If (liGrp2 = 1) Then
                                Call RS_ExecSummRVU_Group1(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            Else
                                Call RS_ExecSummRVU_Group2(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            End If
                        Case 46 ' Procedure Summary - Grouped
                            If (liGrp2 = 1) Then
                                Call RS_ProcSummary_Group1(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            Else
                                Call RS_ProcSummary_Group2(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            End If
                        Case 47 ' Charge Summary - Grouped
                            If (liGrp2 = 1) Then
                                Call RS_ChgSummary_Group1(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            Else
                                Call RS_ChgSummary_Group2(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            End If
                        Case 48 ' Receipt Summary - Grouped
                            If (liGrp2 = 1) Then
                                Call RS_PmtSummary_Group1(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            Else
                                Call RS_PmtSummary_Group2(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            End If
                        Case 49 ' Adjustment Summary - Grouped
                            If (liGrp2 = 1) Then
                                Call RS_AdjSummary_Group1(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            Else
                                Call RS_AdjSummary_Group2(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            End If
                        Case 54 ' A/R Insurance - Grouped
                            If (liGrp2 = 1) Then
                                Call RS_ARSumm_Ins_Group1(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            Else
                                Call RS_ARSumm_Ins_Group2(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            End If
                        Case 55, 57, 59 ' A/R Rolling - Grouped
                            If (liGrp2 = 1) Then
                                Call RS_ARRolling_Group1(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            Else
                                Call RS_ARRolling_Group2(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            End If
                        Case 64 ' Adjustments/Write-offs - Grouped
                            If (liGrp2 = 1) Then
                                Call RS_AdjWOSumm_Group1(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            Else
                                Call RS_AdjWOSumm_Group2(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            End If
                        Case 65, 70 ' RVU Summary - Grouped
                            If (liGrp2 = 1) Then
                                Call RS_RVUSumm_Group1(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            Else
                                Call RS_RVUSumm_Group2(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            End If
                        Case 66 ' Trial Balance - Grouped
                            If (liGrp2 = 1) Then
                                Call RS_TB_Group1(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            Else
                                Call RS_TB_Group2(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            End If
                        Case 71 ' CPT Procedure Summary - Grouped
                            If (liGrp2 = 1) Then
                                Call RS_CPTProcSumm_Group1(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            Else
                                Call RS_CPTProcSumm_Group2(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            End If
                        Case 72, 73 ' Comparison Summary - Single
                            Call RS_CompSumm_Single(liClntID, liQRptPd, strRptTitle, strRptSubTitle)
                        Case 78, 79, 80 ' A/R Detail
                            Call RS_ARDetail(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                        Case 81 ' Debit Summary - Single
                            Call RS_DebitDetail_Single(liClntID, liQRptPd, strRptTitle, strRptSubTitle)
                        Case 82 ' Debit Summary - Grouped
                            If (liGrp2 = 1) Then
                                Call RS_DebitDetail_Group1(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            Else
                                Call RS_DebitDetail_Group2(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            End If
                        Case 83 ' Admin Adjustment - Grouped
                            If (liGrp2 = 1) Then
                                Call RS_AdminAdjDetail_Group1(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            Else
                                Call RS_AdminAdjDetail_Group2(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            End If
                        Case 84, 92, 94 ' A/R Performance - Grouped
                            If (liGrp2 = 1) Then
                                Call RS_ARPerf_Group1(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            Else
                                Call RS_ARPerf_Group2(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            End If
                        Case 85 ' CPT Chgs - Single
                            Call RS_CPTCharges_Single(liClntID, liQRptPd, strRptTitle, strRptSubTitle)
                        Case 86 ' CPT Chgs - Grouped
                            If (liGrp2 = 1) Then
                                Call RS_CPTCharges_Group1(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            Else
                                Call RS_CPTCharges_Group2(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            End If
                        Case 87, 89 ' CPT RVU - Single
                            Call RS_CPTRVU_Single(liClntID, liQRptPd, strRptTitle, strRptSubTitle)
                        Case 88, 90 ' CPT RVU - Grouped
                            If (liGrp2 = 1) Then
                                Call RS_CPTRVU_Group1(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            Else
                                Call RS_CPTRVU_Group2(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                            End If
                        Case 95 ' Pyramid Report - Service Month
                            Call RS_Pyramid_Svc(liClntID, liQRptPd, strRptTitle, strRptSubTitle)
                        Case 97 ' Monthly Charge Summary
                            Call RS_MonSum_Chg(liClntID, liQRptPd, strRptTitle, strRptSubTitle)
                        Case 98 ' Monthly Receipt Summary
                            Call RS_MonSum_Pmt(liClntID, liQRptPd, strRptTitle, strRptSubTitle)
                        Case 99 ' Monthly Payment Summary
                            Call RS_MonSum_Proc(liClntID, liQRptPd, strRptTitle, strRptSubTitle)
                        Case 100 ' Year-to-Date Summary
                            Call RS_YTDSum(liClntID, liQRptPd, strRptTitle, strRptSubTitle)
                        Case 101 ' Charge Ins Detail - Single
                            Call RS_InsDetail_Single(liClntID, liQRptPd, strRptTitle, strRptSubTitle, 2)
                        Case 102 ' Charge Ins Detail - Grouped
                            If (liGrp2 = 1) Then
                                Call RS_InsDetail_Group1(liClntID, liQRptPd, strRptTitle, strRptSubTitle, 2, liGrp1, liGrp2)
                            Else
                                Call RS_InsDetail_Group2(liClntID, liQRptPd, strRptTitle, strRptSubTitle, 2, liGrp1, liGrp2)
                            End If
                        Case 103 ' Receipt Ins Detail - Single
                            Call RS_InsDetail_Single(liClntID, liQRptPd, strRptTitle, strRptSubTitle, 3)
                        Case 104 ' Receipt Ins Detail - Grouped
                            If (liGrp2 = 1) Then
                                Call RS_InsDetail_Group1(liClntID, liQRptPd, strRptTitle, strRptSubTitle, 3, liGrp1, liGrp2)
                            Else
                                Call RS_InsDetail_Group2(liClntID, liQRptPd, strRptTitle, strRptSubTitle, 3, liGrp1, liGrp2)
                            End If
                        Case 105 ' Procedure Ins Detail - Single
                            Call RS_InsDetail_Single(liClntID, liQRptPd, strRptTitle, strRptSubTitle, 1)
                        Case 106 ' Procedure Ins Detail - Grouped
                            If (liGrp2 = 1) Then
                                Call RS_InsDetail_Group1(liClntID, liQRptPd, strRptTitle, strRptSubTitle, 1, liGrp1, liGrp2)
                            Else
                                Call RS_InsDetail_Group2(liClntID, liQRptPd, strRptTitle, strRptSubTitle, 1, liGrp1, liGrp2)
                            End If
                        Case 107 ' Adjustment Ins Detail - Single
                            Call RS_InsDetail_Single(liClntID, liQRptPd, strRptTitle, strRptSubTitle, 4)
                        Case 108 ' Adjustment Ins Detail - Grouped
                            If (liGrp2 = 1) Then
                                Call RS_InsDetail_Group1(liClntID, liQRptPd, strRptTitle, strRptSubTitle, 4, liGrp1, liGrp2)
                            Else
                                Call RS_InsDetail_Group2(liClntID, liQRptPd, strRptTitle, strRptSubTitle, 4, liGrp1, liGrp2)
                            End If
                        Case 111 ' Pyramid Report - Bill Charge Month
                            Call RS_Pyramid_Chg(liClntID, liQRptPd, strRptTitle, strRptSubTitle)
                        Case 112, 113, 114 ' AAR Report - HMA
                            Call RS_AAR_HMA(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                        Case 115, 116 ' CPT Report - HMA
                            Call RS_CPT_HMA(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                        Case 117, 118 ' Svc Stats Report
                            Call RS_SvcStats(liClntID, liQRptPd, strRptTitle, strRptSubTitle, liGrp1, liGrp2)
                    End Select
                    ' Set Tab Name
                    With goXL.Sheets(1)
                        .Select
                        .Name = strTabName
                    End With
                    'Verify Folder exists added 4/08/2013
                      Call RptCreateFolders
 Call XLKill
    ' Refresh Lists
    Call RefreshLists
    
Else
    MsgBox "ERROR:  COULD NOT OPEN EXCEL.", , "BOO!!!"
End If
'MsgBox "Processing Competed!", , "WHOO!!!"
Call createPrintSets
End Sub

 

[vba317]

I also have two more modules that define the excel application.

Public Function XLCreate()

' ************************************************
' *** THIS SUB CREATES A NEW INSTANCE OF EXCEL ***
' ************************************************
On Error Resume Next
gbXLPresent = True
Set goXL = CreateObject("Excel.Application")
If goXL Is Nothing Then ' Check if Excel is installed
    gbXLPresent = False
Else
    goXL.Visible = True ' If there, make it visible
End If

End Function

Public Function XLKill()
' **************************************************
' *** THIS SUB CLOSES THE OPEN INSTANCE OF EXCEL ***
' **************************************************
If gbXLPresent = True Then
    goXL.Quit
    Set goXL = Nothing
    gbXLPresent = False
End If

End Function

 

[lameid]

Assuming it doesn't balk at activesheet at the very least then...

With ActiveSheet.PivotTables("PivotTable1").PivotFields(strFld)

Should be

With [red]goXL[/red].ActiveSheet.PivotTables("PivotTable1").PivotFields(strFld)

 

[PHV]

Always use full qualified excel objects, eg:
[!]goXL.[/!]ActiveWorkbook.Names.Add Name:="SvcData", RefersToR1C1:="=Svc_Summary!R1C105:R" & (iRw - 1) & "C112

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

 

[vba317]

I changed the code to

With goXL.ActiveSheet.PivotTables("PivotTable1").PivotFields(strFld)

I get the same error.

 

[vba317]

When I added PHV's code it works now.