Extract/list slicer values using VBA

[Mr.Karr]

Hello

Is there a way to extract values out of a slicer using VBA ? Please provide your inputs

Many thanks in advance

 

[Smallman]

Hi Mr Karr

The following will list all of the slicer items in your slicer.

Option Explicit

Sub ListSlicerItems()
Dim i As Integer

    With ActiveWorkbook.SlicerCaches("Slicer_Test")
      For i = 1 To ActiveWorkbook.SlicerCaches("Slicer_Test").SlicerItems.Count
        Range("B" & i) = .SlicerItems(i).Name
      Next
    End With
End Sub

Note if you right click on your slicer and choose Slicer settings you will get the name of the slicer which is important if you want to get the above coding to work.





I have attached a sample.


Take care

Smallman

 

[Mr.Karr]

Hi @Smallman :
It works fine. But can you please help to accommodate with this situation. I have 2 slicers with the same name. When I modify the above provided code, only one picks up & lists out the data but the other throws up an error. I think some data model & cube work at the background.

Can you please look into this. I have attached a file with screenshots and slicer values.

Thanks a lot for the help in advance.

 

[Smallman]

Hi

Your solution is in your post. Create a new slicer and make damn sure that the new slicer does not have the same name as any other slicer.

You need these two slicers to have unique names.

Take care

Smallman

 

[Mr.Karr]

thanks @Smallman

 

[Smallman]

You are very welcome Mr Karr.

All the very best

Smallman

 

[ryanwade44]

Great post guys. It seems that this only works when you are connected to a traditional pivot table and not to the data model. I get an 1004 error in that situation. I looked at the properties of a slicer connected to a traditional pivot table and a a slicer connected to a power pivot model and they have different properties. I thought they would have the same properties.

 

[Maxwolf]

Hi Mr Karr

The following will list all of the slicer items in your slicer.

Option Explicit

Sub ListSlicerItems()
Dim i As Integer

    With ActiveWorkbook.SlicerCaches("Slicer_Test")
      For i = 1 To ActiveWorkbook.SlicerCaches("Slicer_Test").SlicerItems.Count
        Range("B" & i) = .SlicerItems(i).Name
      Next
    End With
End Sub

Note if you right click on your slicer and choose Slicer settings you will get the name of the slicer which is important if you want to get the above coding to work.


View attachment 14571


I have attached a sample.


Take care

Smallman

 

[Maxwolf]

Hi @Smallman :
It works fine. But can you please help to accommodate with this situation. I have 2 slicers with the same name. When I modify the above provided code, only one picks up & lists out the data but the other throws up an error. I think some data model & cube work at the background.

Can you please look into this. I have attached a file with screenshots and slicer values.

Thanks a lot for the help in advance.

Hi Mr Karr

The following will list all of the slicer items in your slicer.

Option Explicit

Sub ListSlicerItems()
Dim i As Integer

    With ActiveWorkbook.SlicerCaches("Slicer_Test")
      For i = 1 To ActiveWorkbook.SlicerCaches("Slicer_Test").SlicerItems.Count
        Range("B" & i) = .SlicerItems(i).Name
      Next
    End With
End Sub

Note if you right click on your slicer and choose Slicer settings you will get the name of the slicer which is important if you want to get the above coding to work.


View attachment 14571


I have attached a sample.


Take care

Smallman

 

[Maxwolf]

the code for listing the item in the slicer is very good but how can we retrieve only the Selected item from the slicer.

 

[NARAYANK991]

the code for listing the item in the slicer is very good but how can we retrieve only the Selected item from the slicer.

Hi ,

See if this helps :

http://www.jkp-ads.com/articles/slicers05.asp

Narayan

 

[Chihiro]

Depends on type of slicer. If it's OLAP model based or standard Excel Range/Table based.

If latter, just loop and check the visible property of each item, output only those that has .Visible = True.

If former. Remember that .VisibleSlicerItemsList is array of visible items.
I'd put it into variant array and loop through it like below.

Sub Demo()
Dim ar
ar = ThisWorkbook.SlicerCaches("Segment_Number_item1").VisibleSlicerItemsList

For i = 1 To UBound(ar)
    Debug.Print ar(i)
Next

End Sub

 

[Maxwolf]

Thank you !