Posted on in Excel, PowerPivot

Setting selected item in a slicer connected to a Power Pivot table via VBA

Yesterday a colleague asked me whether it was possible to select an item in a slicer via VBA and I replied “Of course” – but that was before I studied the documentation about the “Selected” property of the SlicerItem object –

“Returns or sets whether the slicer item is selected. Read/write for slicers connected to non-OLAP data sources. Read-only for slicers connected to OLAP data sources.”

http://msdn.microsoft.com/en-us/library/office/ff822747(v=office.15).aspx

Now that was a big surprise – and I thought why not try to work around this.

First idea was to add a page field (Filter) because changing a page field will set the selected slicer items to the items selected in the page field (Filters)


->

However, writing a macro to do this seemed a bit over kill – so I continued reading about slicers and their properties

Moreover, it turned out that if we look at the SlicerCache it has a property called “VisibleSlicerItemsList”

That is not read/only – but Read/Write

 

Therefore, we can use this property to set the selected item in a slicer connected to an OLAP source – and here is the code to set a selected item

Sub SetSelection()

Dim sc As SlicerCache

Dim s As Slicer

Set sc = ActiveWorkbook.SlicerCaches(“Slicer_DimDate.CalendarYear”) ‘ Name of slicer

Set s = sc.Slicers(1)

sc.VisibleSlicerItemsList = “[Query1].[DimDate.CalendarYear].&[2005]” ‘Name of item you want selected

End Sub

 

Run the code (or modify to your need) to select the item.

You can download an example file here.

16 thoughts on “Setting selected item in a slicer connected to a Power Pivot table via VBA”

  2. hummm, I’m trying to figure out how to pass back MULTIPLE selections (ala, 2005 & 2006, but not [ALL]) on an PowerPivot(OLAP)-Slicer to a non-OLAP Slicer with identical SlicerItems…. any Ideas?

    1. Hi Mark,

      I think this should do the trick

      Sub SetMultipleSlicerItems()

      Dim scOlAP As SlicerCache

      Dim scList As SlicerCache

      Dim sO As Slicer
      Dim sL As Slicer

      Dim si As SlicerItem

      Set scOlAP = ActiveWorkbook.SlicerCaches(“Slicer_Product1”)
      Set scList = ActiveWorkbook.SlicerCaches(“Slicer_Product”)

      Set sO = scOlAP.Slicers(1)

      Set sL = scList.Slicers(1)

      Dim i As Integer
      Dim svalue As String

      For i = 1 To UBound(scOlAP.VisibleSlicerItemsList)

      svalue = Replace(Replace(scOlAP.VisibleSlicerItemsList(i), “[Table1].[Product].&[“, “”), “]”, “”)

      For Each si In scList.SlicerItems
      If svalue = si.SourceName Then
      si.Selected = True
      Else
      si.Selected = False
      End If

      Next

      Next

      End Sub

      The Slicer_Product1 is the OLAP/PowerPivot slicer and the Slicer_Product is linked to a table in the workbook.

      Hope this solves your problem

      Erik

  3. Erik, very awesome of you to reply…. I’m almost there, but the behavior isn’t quite right, ClearManualFilters on the non-OLAP slicer work to improve but there are still some odd glitches… sample workbook is here:
    https://www.dropbox.com/s/pobonolmxf555yl/SyncSlicerProblem_07.05_2015.xlsm?dl=0

    When any single selection is made from the OLAP slicer, the Regular Slicer follows it correctly. When you hold CTRL when all Slicer Items are selected, only deselecting D & E will Follow correctly; whereas, deselecting A,B,or C will not Follow into the Regular Slicer. Selecting A and then holding CTRL and Selecting B will Follow correctly; however, selecting B and then holding CTRL and Selecting C will not Follow correctly. There are many other instances that do not Follow correctly. Thoughts?

    Code:
    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim scOLAP As SlicerCache
    Dim scList As SlicerCache
    Dim sO As Slicer
    Dim sL As Slicer
    Dim si As SlicerItem
    Dim i As Integer
    Dim svalue As String

    Set scOLAP = ActiveWorkbook.SlicerCaches(“Slicer_RegionCode”)
    Set scList = ActiveWorkbook.SlicerCaches(“Slicer_RegionCode1”)

    scList.ClearManualFilter

    Set sO = scOLAP.Slicers(1)
    Set sL = scList.Slicers(1)

    For i = 1 To UBound(scOLAP.VisibleSlicerItemsList)

    svalue = Replace(Replace(scOLAP.VisibleSlicerItemsList(i), “[CleanedData].[RegionCode].&[“, “”), “]”, “”)

    For Each si In scList.SlicerItems
    If svalue = si.SourceName Then
    si.Selected = True
    Else
    si.Selected = False
    End If

    Next

    Next

    End Sub

    1. Hi Mark,

      It was due to the order in which the code deselected and selected the items – when all items gets deselected the slicer will return to all.

      I modified the code to do it in a slightly different way – using a function to check whether the item in the synched slicer should be selected or not

      Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
      Dim scOLAP As SlicerCache
      Dim scList As SlicerCache
      Dim sO As Slicer
      Dim sL As Slicer
      Dim si As SlicerItem
      Dim i As Integer
      Dim j As Integer
      Dim svalue As String

      Set scOLAP = ActiveWorkbook.SlicerCaches(“Slicer_RegionCode”)
      Set scList = ActiveWorkbook.SlicerCaches(“Slicer_RegionCode1”)

      scList.ClearManualFilter

      Set sO = scOLAP.Slicers(1)
      Set sL = scList.Slicers(1)

      For j = 1 To scList.SlicerItems.Count

      scList.SlicerItems(j).Selected = CheckSelected(scList.SlicerItems(j).SourceName, scOLAP)

      Next

      End Sub

      Function CheckSelected(testFor As String, sc As SlicerCache)

      For k = 1 To UBound(sc.VisibleSlicerItemsList)
      If testFor = Replace(Replace(sc.VisibleSlicerItemsList(k), “[CleanedData].[RegionCode].&[“, “”), “]”, “”) Then
      CheckSelected = True
      Exit For
      End If
      Next

      End Function

      I have tested and it seems to work in the scenarios you mention.

      Hope this solves your problem

      BR
      Erik

  4. Hi Erik, yes that did work with a little help from the Microsoft Developers forum I simplified the code a bit to omit the function. For all other reading these comments the code below will help you Sync an OLAP slicer to a non-OLAP slicer and is an excellent workaround for the inherent problem with MDX and not being able to “ShowDetails” on data that has been filtered by multiple critrea (ala, two selections from a single slicer).

    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim scOLAP As SlicerCache
    Dim scList As SlicerCache
    Dim sO As Slicer
    Dim sL As Slicer
    Dim si As SlicerItem
    Dim i As Integer
    Dim svalue As String
    Dim ar() As String

    Set scOLAP = ActiveWorkbook.SlicerCaches(“Slicer_RegionCode”)
    Set scList = ActiveWorkbook.SlicerCaches(“Slicer_RegionCode1”)

    scList.ClearManualFilter

    Set sO = scOLAP.Slicers(1)
    Set sL = scList.Slicers(1)
    ReDim ar(UBound(scOLAP.VisibleSlicerItemsList))
    For i = 1 To UBound(scOLAP.VisibleSlicerItemsList)
    svalue = Replace(Replace(scOLAP.VisibleSlicerItemsList(i), “[CleanedData].[RegionCode].&[“, “”), “]”, “”)
    ar(i) = svalue
    Next
    For Each si In scList.SlicerItems
    If UBound(Filter(ar, si.SourceName)) < 0 Then
    si.Selected = False
    End If
    Next
    End Sub

    1. This is great Mark – only caution is that the quotes do not copy and paste correctly into VBA. I’m using this as a base for passing multiple slicer selections between OLAP slicers and had previously found it challenging to store an array of OLAP slicer selections.

  5. Erik thank you for this, I was stuck for about 4 days because of this, was about to rebuild my entire workbook to have it based directly off the the data in the regular excel table, lose my calculated fields, and KPIs. I appreciate the help immensely.

  7. Spent all afternoon trying to figure out vba for pivot table slicers with external ranges. Not much out there, so I was glad to find your info. It solved my problem – thanks so much for taking the time to post your experience.

  8. Pingback: Excel 2013 – VBA code to select multiple items on a Slicer connected to a PowerPivot pivot table. – copypastespecialvalues

  9. Hi Erik,

    I’m a begginer in VBA and I’m struggling with a problem that I believe to be similar as the one discribed in this post.

    Every month I have to save as PDF hundreds of reports, one for each client in the company I work for. The problem is that the reports uses data from 3 diferent tables in the same worksheet.

    Previously I used only one table, and I used to have the problem of the code taking hours to run, since I was clearing the manual filter for every client in the slicer. I solved that problem with the following code:

    Dim sC As SlicerCache
    Set sC = ActiveWorkbook.SlicerCaches(“Slicer_name”)

    If sC.VisibleSlicerItems.Count 1 Or sC.SlicerItems(1).Selected = False Then
    MsgBox “Selecionar apenas o primeiro nome da segmentação!”
    Exit Sub
    End If

    For i = 1 To sC.SlicerItems.Count

    sC.SlicerItems(i).Selected = True
    If i 1 Then sC.SlicerItems(i – 1).Selected = False

    Sheet1.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    “Workbook_path” & Range(“Client_name”).Text & “.pdf”, Quality:= _
    xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

    Next
    End Sub

    Now, I need to select the client name from 3 different slicers. I tried creating a Power Pivot data model to connect the 3 tables to a list with all the clients, and applied the code to a new slicer, that connected the 3 tables. But in this case, the code I wrote above does not work, specifically in this line:

    If sC.VisibleSlicerItems.Count 1 Or sC.SlicerItems(1).Selected = False Then

    So, to sum up, I have 2 alternatives. The first, is to find a code that works for the Power Pivot Slicer, or I can creat a code that selects the same name (client) in the 3 different slicers from the 3 different tables. In both solutions, I still need to avoid the problem of clearing the manual filter in the slicer, that causes the code to take hours to run. To give you a dimension of this problem, when I had only 1 table and 1 slicer, the code that cleared the manual filter used to take nearly 10 minutes per report (client), and with the code above, takes only a few seconds.

    I hope you can help me with this problem, that is making my life in the company very difficult.

    Thank you in advance,

    Pedro

Leave a comment