How to use Macros to manage report connections in slicers|VBA & Macros|Excel Forum|My Online Training Hub

Can you tell us why are you disconnecting slicers?

You can remove that line pt(i).SaveData = True from code, it's irrelevant to your situation.

If you're using the version with connect and reconnect in different procedures, you have to be aware that they are not quite independent. The SlicersDict dictionary is declared as a global parameter, and it is created by the Disconnect procedure. The Reconnect procedure needs to be run only when the dictionary exists (the previous Disconnect procedure was run), it needs to know which slicer was connected to which pivots. You can run any procedure between Disconnect and Reconnect procedures, as long as your codes does not generate errors, global parameters are reset when an error occurs, so you will not be able to reconnect them because the stored data is lost.

Hi Sunny,

That's a different case, the code is designed to restore the slicers to the exact situation they were before disconnecting. For example, if a slicer has 10 pivot tables in its connections list, but was connected to only 5 of them, those exact 5 pivots will be reconnected, not all, this is the reason why the code is storing each slicer data, to know which pivot tables must be reconnected.

To reconnect all available pivot tables, you need a different approach. The object: slicer.PivotTables does not contain what you actually see in Report Connections list. You see all available pivot tables in the list, even if they are connected or not. From visual basic code, slicer.PivotTables list contains only the connected pivot tables, this is the main difference.

If a slicer is not connected to any pivot table, the code:

SlicersDict.Add Key:=sl.Name, Item:=PTDict

will add that slicer to the dictionary, the key will be the slicer name, but Item will be empty, no pivot tables in that list, obviously that slicer will remain unconnected.

If you want to reconnect ALL pivot tables, no matter if they was connected or disconnected before reconnecting, you have to loop through all sheets and collect the list of all existing pivot tables, then try to connect them to each slicer with:

ThisWorkbook.SlicerCaches(SlItem).PivotTables.AddPivotTable (pt(i))

Of course, in some cases it's not possible to connect all existing pivot tables to a slicer. There are 2 cases when this operation will fail:

- if the pivot tables have different data sources (if you go to a slicer Report Connections, the pivot tables that have a different data source than the pivot table where the slicer belongs will not show up in that list)

- if the pivot tables have the save data source, but the data is stored in different pivot caches. (same behaviour: even if the data source is the same, if the pivot cache is different, not all existing pivot tables will be listed)

The last case can be solved, there is a code already developed on Contextures website, that will check the pivot tables caches, and connect all pivot tables with the same data source to the same cache (pt.CacheIndex must be the same, only then those pivots will show up in the same Report Connections list):

Sub CheckCaches()
' Developed by Contextures Inc.
' www.contextures.com
Dim pc As PivotCache
Dim wsList As Worksheet
Dim lRow As Long
Dim lRowPC As Long
Dim pt As PivotTable
Dim ws As Worksheet
Dim lStart As Long
lStart = 2
lRow = lStart

Set wsList = Worksheets.Add
For Each pc In ActiveWorkbook.PivotCaches
  wsList.Cells(lRow, 1).Value = pc.Index
  wsList.Cells(lRow, 2).Value = pc.SourceData
  wsList.Cells(lRow, 3).FormulaR1C1 = _
    "=INDEX(R1C[-2]:R[-1]C[-2],MATCH(RC[-1],R1C[-1]:R[-1]C[-1],0))"
  lRow = lRow + 1
Next pc

For lRowPC = lRow - 1 To lStart Step -1
  With wsList.Cells(lRowPC, 3)
    If IsNumeric(.Value) Then
      For Each ws In ActiveWorkbook.Worksheets
      Debug.Print ws.Name
        For Each pt In ws.PivotTables
        Debug.Print .Offset(0, -2).Value
          If pt.CacheIndex = .Offset(0, -2).Value Then
            pt.CacheIndex = .Value
          End If
        Next pt
      Next ws
    End If
  End With
Next lRowPC

'uncomment lines below to delete the temp worksheet
'Application.DisplayAlerts = False
'wsList.Delete

exitHandler:
Application.DisplayAlerts = True
Exit Sub

errHandler:
MsgBox "Could not change all pivot caches"
Resume exitHandler

End Sub

As you see, life is not easy 🙂

Hi Theo,

Please see post no 5 above, the code is already posted there.

Hi Jose,

To connect all slicers to all pivot tables from a specific sheet, you can use this code (change the targetsheet name as needed, it's set to Sheet1 at this moment):