Can you tell us why are you disconnecting slicers?

The spreadsheet accesses information on a server and performs massive calculations so each time you edit a slicer it takes 30-90 seconds before you can edit the next slicer (I edit up to 9 slicers at a time).  If they are disconnected, I can set them all to what I want then reconnect them all to update the entire sheet.  I have tried a work around to attempt to pause all of the data acquisition and calculations but have not been able to.  This was my next attempt at a work around.  Thought it would be a simple record macro then execute when needed, but for some reason the recorded macro faults out on the first line of code.

To further clarify, it runs an OLAP query every time you change anything with the slicers and that is the main hold up.

Hi,

I wrote once a code for a similar task, you're lucky 🙂

You will have to test the code and adjust it to your needs though:

Hi Catalin

The code doesn't seem to reconnect back the slicers.

I did tested it, and I tested again on a new file, works on my side (excel 2016).

Tested again in the attached sample file, this time I split the code in 2 parts, one button to disconnect slicers, another code for reconnecting slicers with the initial pivot tables connected. (there may be slicers that are not connected to all pivot tables, some users may design their slicers to work this way)

Found an error indeed, in this line:

If UBound(pt) > 0 Then

Because a slicer connected to a single pivot table will have Ubound 0 (in a base 0 option) , The correct line should be:

Looks like I tested only with 2 pivots connected, that's why it worked for me on my first test, thank you for spotting the error. I believe you tested with only one PT connected, and I tested only with more than 2 PT connected 🙂

When I tested the code I received this error:

Run-time error '457:

This key is already associated with an element of this collection

When I debug it highlights this line of the code: PTDict.Add Key:=slpt.Name, Item:=slpt

Could it be an issue that I am running Excel 2013 vs you running Excel 2016?

Is there anything I need to edit in the code other than where you specified for me to enter my code here?

Sorry I haven't worked with macros for a few years and am extremely rusty...

Hi,

That is a weird error. It means that one of your slicers is connected more than once with the same pivot table, which I don't think it's possible.

Can you run the code again, debug, then go to Immediate window in vb editor (if you don't see this panel, enable it from View tab in the editor menu) and paste this code:   ?sl.Name    then press enter? This will allow you to identify which slicer raises the problem.

It will be easier if you can upload a sample file, just with a few rows of data, to test it.

A dictionary cannot accept duplicate keys, but it should not be possible to have 2 pivot tables with the same name connected to the same slicer.

You can easily avoid the error by changing the line that raised the error to:

Ignoring those duplicate names might not be a good option, I am interested to understand what caused the error, unfortunately can't do that without a file, so you have to identify the slicer yourself and list the pivot tables that are found in Report Connections list.

Turns out I actually did have the same slicer linked to 2 pivots with the same name.  I think maybe it let me because the pivots were on different sheets so it was able to differentiate between the two.  I made sure all the pivots have unique names and it runs past that point now.  However, I now get this error: 

Run-time error '1004':

Unable to set the SaveData property of the Pivot Table class

Debug takes me to this line: pt(i).SaveData = True

Unfortunately I can't easily supply a sample file because it is running off an OLAP cube which is on a private data source and has proprietary data.

I should add that I am trying to put the code is two different macros so I can run "disconnect" then "reconnect" independently.

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 Catalin

This is the file I was using to test your macro.

If the slicers were disconnected before running the macro, they fail to get reconnected.

I have added the >= sign and use your latest codes (Connect/Disconnect) but it still not working properly (sometime OK, sometime no) under the above situation.

It seems odd. I am using Excel 2010.

Sunny

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 🙂

bcwilk said
When I tested the code I received this error:

Run-time error '457:

This key is already associated with an element of this collection

When I debug it highlights this line of the code: PTDict.Add Key:=slpt.Name, Item:=slpt
  

Indeed, that was a mistake from me, to use only the pivot table name as the key. There can be pivot tables in different sheets, but they can have the same name (PivotTable1 for example).

I changed the initial code, to take into account this fact, the key should be the combination of sheet name and pivot table name, this way duplicates will not be possible to occur:

PTDict.Add Key:=slpt.Parent.Name & slpt.Name, Item:=slpt