Create Slicers Error in Pivot from Access

[DAOsland]

My first Post.

I have a database in SQL, managed by Access. Access doesn't do Pivot tables. Don't trust my users not to screw up an Excel based version of this. In addition, in Access you select Manufacturer to report and it runs a bunch of Pivot Tables and Saves them. This all works except for adding Slicers. As my comments below indicate, I am aware of needing to be overly specific in referencing objects.

I suspect that there is something wrong with variable xlWB. This is set to the Workbook and an immediate print of xlWB.name produces Book1.

Public Function FsYTD(Detail As Boolean)     ' if detail = true then include items but don't calc gain


    ' this is a procedure run from Access in Excel.  As such, the usual Excel defaults (ActiveSheet, etc) cannot be relied
    ' on.  Therefore, this procedure is as explicit as possible about where the data is and where the pivot table is.
    ' for Example the getlast row construct - FinalRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row  only works when running
    ' in Excel.  This must be FinalRow = WSD.Cells(WSD.Rows.Count, 1).End(xlUp).Row (note the extra WSD.) in order to work
    ' when Access is in control.
    
    
 
    Dim RS As DAO.Recordset, I As Long, Sfile As String
    Dim XlAP As Excel.Application, XlWb As Excel.workbook
    Dim WSD As Excel.Worksheet, PWSD As Excel.Worksheet     ' WSD source - PWSD - Pivot table WS
    Dim PTCache As PivotCache
    Dim PT As PivotTable, PTField As PivotField, PRange As Range
    Dim FinalRow As Long
    Dim FinalCol As Long
    
    Dim MfgBrand As String, WBname As String, Mfg As String
    Dim Filename As String
    
    
    
    Dim psheetname, ptablename, Queryname As String
    
    psheetname = "pivot"                ' sheet name only
    ptablename = "fiscal_ytd_Sales"           ' Internal PTable name and used for saving Excel Pivot Table.
    Queryname = "FsYtd_q"         ' Access Query - it would be interesting to rewrite this as a SQL query.
    
    Filename = [Forms]![Selections]![ClientSel] & " " & ptablename
    
    Set XlAP = CreateObject("excel.application")
    Set XlWb = XlAP.Workbooks.Add
    Set WSD = XlWb.Sheets(1)
    
    Set Db = CurrentDb
 
    Set Qdf = Db.QueryDefs(Queryname)            ' data source = access query
        For Each prm In Qdf.Parameters           ' Selection parameters in the Access Query must be resolved
            prm.value = Eval(prm.name)           '      example [forms]![Selections]![lowyear]
        Next
        
    Set RS = Qdf.OpenRecordset(dbOpenDynaset)       ' get the records specified in the Query above
    
    WSD.Cells(2, 1).CopyFromRecordset RS        ' copy query data starting with row 2  - any number of rows 1 command.
    
    For I = 0 To RS.Fields.Count - 1
        WSD.Cells(1, I + 1) = RS.Fields(I).name ' put column name in from Query
    Next I
    
    WSD.Cells.EntireColumn.AutoFit
    
    ' Define input area and set up a Pivot Cache
    FinalRow = WSD.Cells(WSD.Rows.Count, 1).End(xlUp).Row                   ' make sure column1 is not blank


    FinalCol = WSD.Cells(1, WSD.Columns.Count).End(xlToLeft).Column
    Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
    Set PTCache = XlWb.PivotCaches.Create(SourceType:=xlDatabase, _
            SourceData:="sheet1!R1C1:R" & Format(FinalRow) & "C" & Format(FinalCol), Version:=xlPivotTableVersion14)
    
            
    Set PWSD = XlWb.Sheets.Add
        PWSD.name = psheetname
            
    Set PT = PTCache.CreatePivotTable(TableDestination:=PWSD.Cells(5, 2), _
            TableName:=ptablename, DefaultVersion:=xlPivotTableVersion14)


    PT.ErrorString = ""                                    ' use blank instead of error code
    PT.DisplayErrorString = True                            ' turn on ability to display same


    
    With PT.PivotFields("FsYear")                         ' year
        .Orientation = xlColumnField
        .name = "Year"
    End With


    PT.ColumnGrand = False              ' No grand totals at bottom
    PT.RowGrand = False
    


    Set PTField = PT.RowFields("Branch")
    PTField.DrillTo PTField.name
    XlWb.ShowPivotTableFieldList = False  ' hide the field list (makes form more readable
    PT.DisplayFieldCaptions = False                         ' turn off default field labels


    PWSD.PivotTables(ptablename).TableStyle2 = "PivotStyleMedium9"


    Dim sct As SlicerCache
    Dim scb As SlicerCache
      
    If Batch <> True Then XlAP.Visible = True 'Make it visible
    
    Dim wrc As SlicerCaches
    Dim wrsl As SlicerCache
    XlWb.Activate
    PWSD.Activate

         This is the macro code generated by Excel.  It works sometimes.  When Excel has focus probably.
         ActiveWorkbook.SlicerCaches.Add(ActiveSheet.PivotTables("fiscal_ytd_Sales"), _
        "WRep").Slicers.Add ActiveSheet, , "WRep", "WRep", 99, 432, 144, 198.75

    
          XlWb.SlicerCaches.Add(ActiveSheet.PivotTables(ptablename), _
        "WRep").Slicers.Add ActiveSheet, , "WRep", "WRep", 99, 432, 144, 198.75

The above is what I think should work. but gives error similar to slicercaches add invalid procedure call or argument
   
  more code to save file etc. 


End Function

Hope this is interesting to someone out there.

 

[DAOsland]

changing code to
Workbooks("book1").SlicerCaches.Add(ActiveSheet.PivotTables(ptablename), _
"WRep").Slicers.Add ActiveSheet, , "WRep", "WRep", 99, 432, 144, 198.75

works. I think I'll use a variable for "book1" and get it from xlWB.name.

BYW this is in Excel2010 - moving to 2016 but .........