Slicer Item name for OLAP data source

[Gimics]

Hello,

I am trying to create a macro that saves a file using the name of a selected slicer item. My pivot table, though, is connected to an OLAP cube, and I am having troubles pulling the slicer item name.

Right now, I have this:

Public Sub SaveTables()
Dim GLSlicer As SlicerCache
Dim sFileName As String
Dim SFilePath As String

Set GLSlicer = ThisWorkbook.SlicerCaches("Slicer_Fiscal")
SFilePath = ThisWorkbook.Path & "\"
sFileName = GLSlicer.SlicerItem.Name & ".xlsm"






ThisWorkbook.SaveAs (SFilePath & sFileName)

End Sub

But I get a Run-time error '438': Object doesn't support this property or method.

I think it has something to do with being connected to an OLAP cube; there has to be a way to identify the selected slicer item... any thoughts?

 

[Gimics]

Bump - any takers? Is there something that isn't clear?

 

[mrmmickle1]

What line of code is it tripping up on? Have you tried adding in Debug.Print statements to make sure your variables are what they are expected to be?

 

[Gimics]

Thanks mrmmickle,

The GLSlicer.SlicerItem.Name is tripping up the code; this is actually just a snapshot of a larger macro that works fine, until I try to add the 'sliceritem.name' to the SFileName variable.

 

[Gimics]

I'll try one more bump for good measure..

 

[mrmmickle1]

After looking at my own example for thirty minutes I finally realized that you need to Select the Slicer you want to reference then go to the Slicer Tools > Options > Slicer Settings (On the Far Left of Tool Bar)

Look at what the information says. You will want to use what is labeled as the Name To Appear in Formulas. Which may be a slightly different then the name you expect. See this website for additional information: CLICK HERE

 

[Gimics]

Thanks for your time mrmmickle! What you are describing is already functioning in my code (you can see that I define the SlicerCache as Slicer_Fiscal, which is appropriate).

What appears to not be working is the ability to pull SlicerItem information from the Slicer. When I assign a slicer level to a variable, I can see each of the slicer items (51 to be exact) in the Locals window, but I'm not able to Debug.Print any of them. I use a bit of code like this:

Debug.Print ThisWorkbook.SlicerCaches("Slicer_Fiscal").SlicerCacheLevels(2).SlicerItem(1)

And the Immediate window just shows up "False", despite my being able to see SlicerItem 1 in the locals window.

 

[RoryA]

A slicercache doesn't have a sliceritem property. You need to either read the activeitem property of the slicer in question, or loop through each sliceritem in the sliceritems collection for the relevant slicercache level, since it's an OLAP data source.

 

[Gimics]

Rory, in my example above your post, I am using level 2 of SlicerCacheLevels as I can see in the Locals window that this is where the applicable slicer items reside. As stated, I can even see the slicer items listed for that slicer cache level. But, for some reason, I cannot use the SlicerItems themselves in any code... anything I debug.print (.Caption, .Name, CStr(sI.Value)) comes up as 'False' in my Immediate window.

I've tired a loop like you suggested:

[COLOR=#666666][FONT=Courier New]Dim sC As SlicerCache[/FONT][/COLOR][COLOR=#666666][FONT=Muli][FONT=Courier New]Dim SL As SlicerCacheLevel
[/FONT][FONT=Courier New]Dim sI As SlicerItem
[/FONT][/FONT][/COLOR]
[COLOR=#666666][FONT=Muli][FONT=Courier New]Set sC = ThisWorkbook.SlicerCaches(“Slicer_Fiscal”)
[/FONT][FONT=Courier New]Set SL = sC.SlicerCacheLevels(2)
[/FONT][FONT=Courier New]Debug.Print “——————————————————————————“
[/FONT][/FONT][/COLOR]
[COLOR=#666666][FONT=Muli][FONT=Courier New]For Each sI In SL.SlicerItems
[/FONT][FONT=Courier New]Debug.Print “Caption –> ” & sI.Caption
[/FONT][FONT=Courier New]Debug.Print “Value –> ” + CStr(sI.Value)
[/FONT][FONT=Courier New]Debug.Print “Unique Name –> ” + sI.Name
[/FONT][FONT=Courier New]Debug.Print “——————————————————————————“
[/FONT][/FONT][/COLOR]
[COLOR=#666666][FONT=Courier New]Next sI[/FONT][/COLOR]

And this still just brings up 'False'

 

[Gimics]

Hmm... I realized that my previous code wasn't requesting a caption or a name to print; when I change the single line to this:

Debug.Print ThisWorkbook.SlicerCaches("Slicer_Fiscal").SlicerCacheLevels(3).SlicerItems(1).Caption

The first SlicerItem text appears!

But the loop still doesn't work; it still just provides 'false' (I realize I'm using a different level, but that still hasn't made a difference).