Using variables with PowerPivot

[tacoken]

I'm trying to use VBA with PowerPivot, and I'd like to change the slicer field by using a variable. The code below was from recording a macro and works fine when the client's name is explicit as in the code below. When I try to replace Smith in the code with a string variable, I get a "Run-time error '1004': The item could not be found in the OLAP Cube." Using a variable for a filter works fine with a regular pivot table, but I must be missing something with PowerPivot. Any help would be greatly appreciated!


ActiveSheet.PivotTables("PivotTable3").PivotFields( _
"[Holdings].[Client].[Client]").CurrentPageName = _
"[Holdings].[Client].&[Smith]"

 

[Derek Brown]

Hello and welcome to The Board.
I have not yet needed to use VBA with PowerPivot but I wonder how you are changing that code.

' Original:
ActiveSheet.PivotTables("PivotTable3").PivotFields( _
"[Holdings].[Client].[Client]").CurrentPageName = _
"[Holdings].[Client].&[Smith]"
' Possible alternative?:
Dim strCurrentPage as String
strCurrentPage = "[Holdings].[Client].&[" & myVariable & "]"
' or should that be:
strCurrentPage = "[Holdings].[Client].[" & myVariable & "]"
'
ActiveSheet.PivotTables("PivotTable3").PivotFields( _
"[Holdings].[Client].[Client]").CurrentPageName = strCurrentPage

Note that I have not tested this.

 

[tacoken]

Thanks for the reply, Derek. Basically, I'm just defining a variable to use in place of Smith in the code, so that I can choose the client name elsewhere.

dim cname as string
ActiveSheet.PivotTables("PivotTable3").PivotFields( _
"[Holdings].[Client].[Client]").CurrentPageName = _
"[Holdings].[Client].&[cname]"

I've tried different combos of quotations and ampersand (including your suggestion), and I can't get it to work. There seems to be something unique about the powerpivot that I'm missing because I've had no trouble with this with regular pivot tables.

 

[Derek Brown]

Perhaps I should have added a value to 'myVariable' in the example, but I did not know where you were getting the value from.
Try it again with the modification:

' Possible alternative?:
Dim strCurrentPage as String
Dim myVariable as string
myVariable = "Smith"
strCurrentPage = "[Holdings].[Client].&[" & myVariable & "]"
' or should that be:
strCurrentPage = "[Holdings].[Client].[" & myVariable & "]"
'
ActiveSheet.PivotTables("PivotTable3").PivotFields( _
"[Holdings].[Client].[Client]").CurrentPageName = strCurrentPage

 

[tacoken]

Brilliant, Derek! The first option worked perfectly. I can't thank you enough. I can't imagine how many more hours of frustration you just saved me.