• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

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

You are here: Home / How to use Macros to manage report connections in slicers|VBA & Macros|Excel Forum|My Online Training Hub

sale now on

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosHow to use Macros to manage report …
sp_PrintTopic sp_TopicIcon
How to use Macros to manage report connections in slicers
Page: 12Jump to page
Avatar
bcwilk
Member
Members
Level 0
Forum Posts: 6
Member Since:
April 27, 2017
sp_UserOfflineSmall Offline
1
April 27, 2017 - 1:57 am
sp_Permalink sp_Print

I am trying to create a macro (Excel 2013) that will change the report connections for multiple slicers and multiple pivot tables all at once.  I frequently need to disconnect or connect the slicers and it takes a decent amount of time to go in one by one and select which pivots I want connected or disconnected.  I tried to record myself doing it with the macro record feature but it bugs out once I try to run it.  Below is an example of what I got from the recorded macro:

ActiveWorkbook.SlicerCaches("Slicer1").PivotTables. _
RemovePivotTable (ActiveSheet.PivotTables("PivotTable1"))
ActiveWorkbook.SlicerCaches("Slicer1").PivotTables. _
RemovePivotTable (ActiveSheet.PivotTables("PivotTable2"))
ActiveWorkbook.SlicerCaches("Slicer1").PivotTables. _
RemovePivotTable (ActiveSheet.PivotTables("PivotTable3"))
ActiveWorkbook.SlicerCaches("Slicer2").PivotTables. _
RemovePivotTable (ActiveSheet.PivotTables("PivotTable1"))
ActiveWorkbook.SlicerCaches("Slicer2").PivotTables. _
RemovePivotTable (ActiveSheet.PivotTables("PivotTable2"))
ActiveWorkbook.SlicerCaches("Slicer2").PivotTables. _
RemovePivotTable (ActiveSheet.PivotTables("PivotTable3"))

To reconnect substitute "AddPivotTable" for "RemovePivotTable" and so on...

All of the slicers are on a single sheet and each pivot is on its own sheet within the same workbook.  Any help is much appreciated!

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1802
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
April 27, 2017 - 1:50 pm
sp_Permalink sp_Print

Can you tell us why are you disconnecting slicers?

Avatar
bcwilk
Member
Members
Level 0
Forum Posts: 6
Member Since:
April 27, 2017
sp_UserOfflineSmall Offline
3
April 27, 2017 - 10:26 pm
sp_Permalink sp_Print

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.

Avatar
bcwilk
Member
Members
Level 0
Forum Posts: 6
Member Since:
April 27, 2017
sp_UserOfflineSmall Offline
4
April 28, 2017 - 4:43 am
sp_Permalink sp_Print

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1802
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
5
April 28, 2017 - 4:54 pm
sp_Permalink sp_Print sp_EditHistory

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:

Sub DisconnectAndReconnectPTToSlicers()
Dim SlicersDict As Variant
Dim PTDict As Variant
Set SlicersDict = CreateObject("Scripting.Dictionary")
Dim sl As SlicerCache, slpt As PivotTable, SlItem As Variant, pt As Variant, i As Byte
'create a dictionary of dictionaries with slicers and connected pivot tables
For Each sl In ThisWorkbook.SlicerCaches
    Set PTDict = CreateObject("Scripting.Dictionary")
    For Each slpt In sl.PivotTables
        PTDict.Add Key:=slpt.Parent.Name & slpt.Name, Item:=slpt
    Next
    SlicersDict.Add Key:=sl.Name, Item:=PTDict
Next
 
For Each SlItem In SlicersDict.Keys
   'remove pt connections for this slicer
    Set PTDict = SlicersDict(SlItem)
    pt = PTDict.items
    If UBound(pt) >= LBound(pt) Then
        For i = LBound(pt) To UBound(pt)
            pt(i).SaveData = True
            ThisWorkbook.SlicerCaches(SlItem).PivotTables.RemovePivotTable (pt(i))
        Next
    End If
Next
 
'your code here, before reconnecting the pivot tables to slicers...

For Each SlItem In SlicersDict.Keys
    Set PTDict = SlicersDict(SlItem)
    pt = PTDict.items
      
    'reconnect all pivot tables to this slicer
    If UBound(pt) >= 0 Then
        For i = LBound(pt) To UBound(pt)
               ThisWorkbook.SlicerCaches(SlItem).PivotTables.AddPivotTable (pt(i))
        Next

    End If
Next

Set SlicersDict=Nothing

Set PTDict=Nothing

End Sub

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
6
April 28, 2017 - 5:10 pm
sp_Permalink sp_Print

Hi Catalin

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1802
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
7
April 28, 2017 - 11:12 pm
sp_Permalink sp_Print sp_EditHistory

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:

If UBound(pt) >= 0 Then

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 🙂

Avatar
bcwilk
Member
Members
Level 0
Forum Posts: 6
Member Since:
April 27, 2017
sp_UserOfflineSmall Offline
8
April 29, 2017 - 12:43 am
sp_Permalink sp_Print

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...

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1802
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
9
April 29, 2017 - 1:25 am
sp_Permalink sp_Print

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:

If PTDict.Exists(slpt.Name)=False Then PTDict.Add Key:=slpt.Name, Item:=slpt

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.

Avatar
bcwilk
Member
Members
Level 0
Forum Posts: 6
Member Since:
April 27, 2017
sp_UserOfflineSmall Offline
10
April 29, 2017 - 3:23 am
sp_Permalink sp_Print

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.

Avatar
bcwilk
Member
Members
Level 0
Forum Posts: 6
Member Since:
April 27, 2017
sp_UserOfflineSmall Offline
11
April 29, 2017 - 3:31 am
sp_Permalink sp_Print

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1802
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
12
April 29, 2017 - 3:44 am
sp_Permalink sp_Print

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.

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
13
April 29, 2017 - 11:05 am
sp_Permalink sp_Print

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1802
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
14
April 29, 2017 - 1:54 pm
sp_Permalink sp_Print sp_EditHistory

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 🙂

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1802
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
15
April 29, 2017 - 2:17 pm
sp_Permalink sp_Print

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

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
16
April 29, 2017 - 3:01 pm
sp_Permalink sp_Print

Hi Catalin

Thanks for your lengthy explanation Smile

I do have codes that will disconnect all slicers (irrespective of data source) in a workbook and then reconnect them all again (I believe I got the codes from Contextures some time back).

If life is not easy for an expert like you, then imagine what it is like for meLaugh

Cheers

Avatar
Theo Doucakis

New Member
Members
Level 0
Forum Posts: 1
Member Since:
January 11, 2018
sp_UserOfflineSmall Offline
17
January 11, 2018 - 3:03 am
sp_Permalink sp_Print

Could you please post the code to disconnect and reconnect all slicers to all pivot tables in a workbook (irrispective of prior connections)

Thanks, TheoRI

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1802
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
18
January 11, 2018 - 4:03 am
sp_Permalink sp_Print

Hi Theo,

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

Avatar
jose pacapac

New Member
Members
Level 0
Forum Posts: 2
Member Since:
September 18, 2018
sp_UserOfflineSmall Offline
19
September 18, 2018 - 2:51 pm
sp_Permalink sp_Print

Hi Catalin,

Could you share how the code can be modified just for linking all slicers in a worksheet (not the whole workbook) to all pivot tables in the same worksheet (regardless of pivot tables that may be similarly named, such as PivotTable1, in other worksheets in the same workbook)?

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1802
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
20
September 24, 2018 - 4:16 pm
sp_Permalink sp_Print sp_EditHistory

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):

Sub ConnectAllPivotsToAllSlicers()
Dim TargetPTDict As Variant, TargetSheet As Worksheet, PvTable As PivotTable
Set TargetPTDict = CreateObject("Scripting.Dictionary")
Set TargetSheet = ThisWorkbook.Worksheets("Sheet1")
Dim slCache As SlicerCache, slCachePivotTable As PivotTable, SlItem As Variant, pt As Variant, i As Byte, aSlicer As Slicer

'get the list of pivot tables from that sheet
For Each PvTable In TargetSheet.PivotTables
    TargetPTDict.Add Key:=PvTable.Parent.Name & PvTable.Name, Item:=PvTable
Next

'loop through all slicers from all slicercaches
For Each slCache In ThisWorkbook.SlicerCaches
    For Each aSlicer In slCache.Slicers
        If aSlicer.Parent.Name = TargetSheet.Name Then 'this slicer is in the target sheet
            For Each SlItem In TargetPTDict.Keys
                'connect all pivots to this slicer
                pt = TargetPTDict.Items
                If UBound(pt) >= LBound(pt) Then
                    For i = LBound(pt) To UBound(pt)
                        aSlicer.SlicerCache.PivotTables.AddPivotTable (pt(i))
                    Next
                End If
             Next
         End If
    Next
Next

Set TargetPTDict = Nothing

End Sub

Page: 12Jump to page
sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 170
Currently Online:
Guest(s) 68
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 858
Velouria: 580
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 214
A.Maurizio: 202
Aye Mu: 201
Jessica Stewart: 189
Newest Members:
Vincent Starcevich
Patrick Lawrence
Louise Bell
GIJO GEORGE
Kumud Patel
Atos Franzon
Andrew MacDonald
Artie Ball
Jenn Cain
Peter Vandeweg
Forum Stats:
Groups: 3
Forums: 24
Topics: 6079
Posts: 26684

 

Member Stats:
Guest Posters: 49
Members: 31570
Moderators: 2
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.