Reports on Pivot Tables - Getting Slicers', Charts' and Filters' info

Question

I'm working on a big reporting system with a lot of Pivot Tables, Pivot Charts, Slicers and Filters.

So to be sure that all the Pivot Tables have the right sources and which slicers apply to each one of them, I started to work on a code that aggregate useful info for each Pivot Table :

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" & _
            "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 & " / " & .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 & "/" & Sl.Name '& " : " & Sl.Shape.Parent.Name
            Next Sl
            If Len(TpStr) > 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 & "/" & GetSelectedSlicerItems(Sp(i))
            Next i
            If Len(TpStr) > 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 & "/" & pF.PivotField.Name
                Next pF
                If Len(TpStr) > 0 Then A(10, UBound(A, 2)) = Right(TpStr, Len(TpStr) - 1)
            Else
            End If

            TpStr = vbNullString
            For Each pFL In .DataFields
                TpStr = TpStr & "/" & pFL.Name
            Next pFL
            If Len(TpStr) > 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

And the function to get selected items in a slicer :

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_" & Replace(SlicerName, " ", ""))
    If Not oSc Is Nothing Then
        For Each oSi In oSc.SlicerItems
            If oSi.Selected Then
                GetSelectedSlicerItems = GetSelectedSlicerItems & oSi.Name & ", "
                lCt = lCt + 1
            ElseIf oSi.HasData = False Then
                lCt = lCt + 1
            End If
        Next
        If Len(GetSelectedSlicerItems) > 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 '" & SlicerName & "' was found"
    End If
End Function

---

Issues

Slicers

Sl.Shape.Parent.Name only works when the Slicer is on the same sheet as the Pivot Table. And I can't seem to locate it more accurately than on a sheet (not dramatic).

When I use pT.Slicers(1).Parent.Name or pT.Parent.Name, I get the sheet's name, but I want the SlicerCache's name. (maybe I could loop on SlicerCaches rather than Sheets, and use one of these expressions to get the sheet name)

Charts

I struggle to work with the Pivot Charts, as the property HasChart is already in the Pivot Chart object... I wanted to know if there was one, where it is and how is it named. I thought of a function with error handling to avoid breaks but I'm not sure it is the best way.

ActiveFilters and Pivot Table version

For ActiveFilters, I get this error message for some tables:

This Pivot Table was created in a later version of Excel and can't be updated in this version.

I created few Pivot Tables in Excel 2013 and usually work on 2010, I tried to filter with the version, but they all have the same : xlPivotTableVersion14 (value = 4), except one that give 5 which hasn't any constant to describe it... EDIT : On Excel 2013, I found this : Const xlPivotTableVersion15 = 5

So, any enlightenment, advice or workaround are welcome!

Answer 1

There's a SlicerCaches collection in the Worbook Object.

Dim sc As SlicerCache

For Each sc In ThisWorkbook.SlicerCaches
    Debug.Print sc.Parent.Name ' returns the workbook name
    For Each pt In sc.PivotTables
        Debug.Print pt.Name ' returns the pivot table name
        Debug.Print pt.SourceData ' returns the source range
        Debug.Print pt.Parent.Name ' returns the sheet name
    Next
Next

This way, you can track all pivots associated with the slicers and their corresponding source data.

For Charts, your best bet would be to use Shapes Object.

Dim sh As Shape
Dim ch As ChartObject

For Each sh In Sheet1.Shapes
    If sh.Type = msoChart Then
        Set ch = sh.OLEFormat.Object
        On Error Resume Next
        ' source pivot table
        Debug.Print ch.Chart.PivotLayout.PivotTable.Name
        ' location of the pivot table
        Debug.Print ch.Chart.PivotLayout.PivotTable.Parent.Name
        ' source range
        Debug.Print ch.Chart.PivotLayout.PivotTable.SourceData
        On Error GoTo 0

        ' how it is named
        Debug.Print ch.Chart.Parent.Name
        ' location of the chart
        Debug.Print ch.Chart.Parent.Parent.Name
    End If
Next

Of course, you'll need to use OERN + OEG0 if you happen to have a normal chart.
That would result to a runtime since no PivotLayout is associated with it.

For ActiveFilters, that is a collection. To get all active filters, you can try:

Dim pt As PivotTable
Dim pf As PivotFilter

Set pt = Sheet1.PivotTables("PivotTable1")

For Each pf In pt.ActiveFilters
    Debug.Print pf.FilterType ' returns the filter type
    Debug.Print pf.Value1 ' returns the value
    On Error Resume Next
    Debug.Print pf.DataField.Name ' returns the field name
    On Error GoTo 0
Next

DataField is only used when your filter type is associated with Values.
If not, and you filter Labels, then it will throw a runtime.

For the version, I don't think you have problem retrieving that information?