1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract/list slicer values using VBA

Discussion in 'Power Pivot, Power Map etc' started by Mr.Karr, Jan 9, 2015.

  1. Mr.Karr

    Mr.Karr Member

    Messages:
    227
    Hello

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

    Many thanks in advance
  2. Smallman

    Smallman Excel Ninja

    Messages:
    1,217
    Hi Mr Karr

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

    Code (vb):
    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.


    Slicer.jpg


    I have attached a sample.


    Take care

    Smallman

    Attached Files:

  3. Mr.Karr

    Mr.Karr Member

    Messages:
    227
    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.

    Attached Files:

  4. Smallman

    Smallman Excel Ninja

    Messages:
    1,217
    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
    David Evans likes this.
  5. Mr.Karr

    Mr.Karr Member

    Messages:
    227
  6. Smallman

    Smallman Excel Ninja

    Messages:
    1,217
    You are very welcome Mr Karr.

    All the very best

    Smallman
  7. ryanwade44

    ryanwade44 New Member

    Messages:
    1
    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.
  8. Maxwolf

    Maxwolf New Member

    Messages:
    15
  9. Maxwolf

    Maxwolf New Member

    Messages:
    15
  10. Maxwolf

    Maxwolf New Member

    Messages:
    15
    the code for listing the item in the slicer is very good but how can we retrieve only the Selected item from the slicer.
  11. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,615
    Maxwolf likes this.
  12. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,743
    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.

    Code (vb):
    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 likes this.
  13. Maxwolf

    Maxwolf New Member

    Messages:
    15
    Thank you !

Share This Page