title: Reports on Pivot Tables - Getting Slicers', Charts' and Filters' info
body: <p>I'm working on a big reporting system with a lot of Pivot Tables, Pivot Charts, Slicers and Filters.</p>
  
  <p>So to be sure that <strong>all the Pivot Tables have the right sources and which slicers apply to each one of them</strong>, I started to work on a code that aggregate useful info for each Pivot Table :</p>
  
  <pre><code>Sub Test_2_Pt_Report_by_sheet()
  ThisWorkbook.Save
  Application.ScreenUpdating = False
      Dim pT As PivotTable, _
          Sl As Slicer, _
          RWs As Worksheet, _
          Ws As Worksheet, _
          pF As PivotFilter, _
          pFL As PivotField, _
          HeaDers As String, _
          TpStr As String, _
          Sp() As String, _
          A()
      ReDim A(20, 0)
  
  Set RWs = ThisWorkbook.Sheets("PT_Report")
  
  HeaDers = "Name/Sheet/Address/Version/Source/SlicerCache/Refreshed/Slicer_Number/Slicers/Slicers_Values" &amp; _
              "ActiveFilters/Filters/ActiveValues/HasChart/Chart_Location/ / / / / / "
  For i = LBound(A, 1) To UBound(A, 1)
      A(i, 0) = Split(HeaDers, "/")(i)
  Next i
  
  On Error Resume Next
  For Each Ws In ThisWorkbook.Sheets
      For Each pT In Ws.PivotTables
          TpStr = vbNullString
          ReDim Preserve A(UBound(A, 1), UBound(A, 2) + 1)
          With pT
              A(0, UBound(A, 2)) = .Name
              A(1, UBound(A, 2)) = Ws.Name
              A(2, UBound(A, 2)) = Replace(.TableRange2.Address &amp; " / " &amp; .TableRange1.Address, "$", "")
              A(3, UBound(A, 2)) = .Version
              A(4, UBound(A, 2)) = .SourceData
              A(5, UBound(A, 2)) = ""         '.PivotCache.Name
              A(6, UBound(A, 2)) = .RefreshDate
              A(7, UBound(A, 2)) = .Slicers.Count
  
              For Each Sl In .Slicers
                  TpStr = TpStr &amp; "/" &amp; Sl.Name '&amp; " : " &amp; Sl.Shape.Parent.Name
              Next Sl
              If Len(TpStr) &gt; 0 Then A(8, UBound(A, 2)) = Right(TpStr, Len(TpStr) - 1)
  
              TpStr = vbNullString
              Sp = Split(A(8, UBound(A, 2)), "/")
              For i = LBound(Sp) To UBound(Sp)
                  TpStr = TpStr &amp; "/" &amp; GetSelectedSlicerItems(Sp(i))
              Next i
              If Len(TpStr) &gt; 0 Then A(9, UBound(A, 2)) = Right(TpStr, Len(TpStr) - 1)
  
              If .Version = xlPivotTableVersion12 Then
                  TpStr = vbNullString
                  For Each pF In .ActiveFilters
                      TpStr = TpStr &amp; "/" &amp; pF.PivotField.Name
                  Next pF
                  If Len(TpStr) &gt; 0 Then A(10, UBound(A, 2)) = Right(TpStr, Len(TpStr) - 1)
              Else
              End If
  
              TpStr = vbNullString
              For Each pFL In .DataFields
                  TpStr = TpStr &amp; "/" &amp; pFL.Name
              Next pFL
              If Len(TpStr) &gt; 0 Then A(11, UBound(A, 2)) = Right(TpStr, Len(TpStr) - 1)
  
              'A(12, UBound(A, 2)) = .VisibleFields
              'A(13, UBound(A, 2)) =
  '            A(14, UBound(A, 2)) =
  '            A(15, UBound(A, 2)) =
  '            A(16, UBound(A, 2)) =
  '            A(17, UBound(A, 2)) =
  '            A(18, UBound(A, 2)) = .PivotChart.HasChart
  '            A(19, UBound(A, 2)) = .PivotChart.Chart.Shapes.Name
  '            A(20, UBound(A, 2)) =
          End With
      Next pT
  Next Ws
  
  RWs.Cells.ClearContents
  RWs.Cells.ClearFormats
  RWs.Range("A1").Resize(UBound(A, 2) + 1, UBound(A, 1) + 1).Value = Application.Transpose(A)
  RWs.Columns("A:Z").EntireColumn.AutoFit
  
  RWs.Activate
  Set Ws = Nothing
  Set RWs = Nothing
  Application.ScreenUpdating = True
  MsgBox "done"
  End Sub
  </code></pre>
  
  <p>And the function to get selected items in a slicer :</p>
  
  <pre><code>Public Function GetSelectedSlicerItems(SlicerName As String) As String
      Dim oSc As SlicerCache
      Dim oSi As SlicerItem
      Dim lCt As Long
      Application.Volatile
      On Error Resume Next
  
      Set oSc = ThisWorkbook.SlicerCaches("Slicer_" &amp; Replace(SlicerName, " ", ""))
      If Not oSc Is Nothing Then
          For Each oSi In oSc.SlicerItems
              If oSi.Selected Then
                  GetSelectedSlicerItems = GetSelectedSlicerItems &amp; oSi.Name &amp; ", "
                  lCt = lCt + 1
              ElseIf oSi.HasData = False Then
                  lCt = lCt + 1
              End If
          Next
          If Len(GetSelectedSlicerItems) &gt; 0 Then
              If lCt = oSc.SlicerItems.Count Then
                  GetSelectedSlicerItems = "All Items"
              Else
                  GetSelectedSlicerItems = Left(GetSelectedSlicerItems, Len(GetSelectedSlicerItems) - 2)
              End If
          Else
              GetSelectedSlicerItems = "No items selected"
          End If
      Else
          GetSelectedSlicerItems = "No slicer with name '" &amp; SlicerName &amp; "' was found"
      End If
  End Function
  </code></pre>
  
  <hr>
  
  <h1>Issues</h1>
  
  <h2>Slicers</h2>
  
  <p><code>Sl.Shape.Parent.Name</code> only <strong>works when the Slicer is on the same sheet as the Pivot Table</strong>. And I can't seem to locate it more accurately than on a sheet (not dramatic).</p>
  
  <p>When I use <code>pT.Slicers(1).Parent.Name</code> or <code>pT.Parent.Name</code>, I get the <strong>sheet's name, but I want the SlicerCache's name</strong>. (maybe I could loop on SlicerCaches rather than Sheets, and use one of these expressions to get the sheet name)</p>
  
  <h2>Charts</h2>
  
  <p>I struggle to work with the <strong>Pivot Charts</strong>, as the property <code>HasChart</code> is already in the Pivot Chart object... I wanted to know <strong>if there was one, where it is and how is it named</strong>. I thought of a function with error handling to avoid breaks but I'm not sure it is the best way.</p>
  
  <h2>ActiveFilters and Pivot Table version</h2>
  
  <p>For <code>ActiveFilters</code>, I get this error message for some tables: </p>
  
  <blockquote>
    <p>This Pivot Table was created in a later version of Excel and can't be updated in this version.</p>
  </blockquote>
  
  <p>I created few Pivot Tables in Excel 2013 and usually work on 2010, I tried to <strong>filter with the version, but they all have the same</strong> : <code>xlPivotTableVersion14</code> (value = 4), except one that give 5 which hasn't any constant to describe it... <strong>EDIT</strong> : On Excel 2013, I found this : <code>Const xlPivotTableVersion15 = 5</code></p>
  
  <p>So, any enlightenment, advice or workaround are welcome! </p>