Slicer Item name for OLAP data source


Board Regular
Jan 29, 2014
Office Version
  1. 365
  1. Windows

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?

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Bump - any takers? Is there something that isn't clear?
Upvote 0
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?
Upvote 0
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 '' to the SFileName variable.
Upvote 0
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
Upvote 0
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.
Upvote 0
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.
Upvote 0
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
[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 “——————————————————————————“
[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 “——————————————————————————“
[COLOR=#666666][FONT=Courier New]Next sI[/FONT][/COLOR]

And this still just brings up 'False'
Upvote 0
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).
Last edited:
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back