PivotTable Reports - Get Slicers ', Charts', and Filters Information

Question

I am working on a large reporting system with a large number of pivot tables, pivot charts, slicers and filters.

To make sure that all pivot tables have the correct sources and which slicers apply to each of them , I started working on code that combines useful information 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 of getting the selected elements in 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 

---

Questions

Slicers

Sl.Shape.Parent.Name only works when Slicer is on the same sheet as the pivot table . And I can not find 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 name , but I need the name SlicerCache . (maybe I could loop into SlicerCaches rather than sheets, and use one of these expressions to get the sheet name)

Charts

I try my best to work with Pivot Charts , since the HasChart property HasChart already in the Pivot Chart object ... I wanted to know if it was where it is and what it's called . I thought of a function with error handling to avoid interruptions, but I'm not sure if this is the best way.

ActiveFilters and PivotTable Version

For ActiveFilters I get this error message for some tables:

This pivot table was created in a later version of Excel and cannot be updated in this version.

I created several pivot tables in Excel 2013 and usually worked in 2010, I tried the filter with the version, but they all have the same : xlPivotTableVersion14 (value = 4), with the exception of one which give 5, which do not have constant constant, to describe her ... EDIT . In Excel 2013, I found this: Const xlPivotTableVersion15 = 5

So, any enlightenment, advice or workaround is welcome!

Answer 1

The Worbook has a SlicerCaches collection.

 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 the control points associated with the slicers and their corresponding source data.

For charts, it’s best to use a 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 will need to use OERN + OEG0 if you have a normal schedule.
This will lead to runtime since PivotLayout is not associated with it.

For ActiveFilters, this 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 used only when your filter type is associated with values.
If not, and you are filtering shortcuts, then it will run the runtime.

For the version, I don’t think you have a problem getting this information?