Pivot Tables - VBA

Question

I want to select some values through VBA in Pivot Table which is linked to OLAP Cube. As I know such modification can be realised by typing:

ActiveSheet.PivotTables("PivotTable1").PivotFields("[parameter].[parameter]").VisibleItemsList = Array("value1","value2","value3")

Since get list of parameters from cells in Excel sheet, I wrote simple function which - In mentioned example - returns:

""value1","value2","value3""

I can't use such string as parameter for Array function (as it recognize it as one string), so I've tried to convert it to Array of Variant, typing above code:

Dim tableVar() As Variant

 myVar = Replace(myVar, Chr(34), "") 
 myVar = Split(myVar, ",") 
 lowerB =LBound(myVar) 
 upperB = UBound(myVar)

 ReDim tablica(lowerB To upperB)
  For i = lowerB To upperB 
     tableVar(i) = myVar(i) 
  Next i

Unfortunately it changes nothing - when I'm calling:

ActiveSheet.PivotTables("PivotTable1").PivotFields("[parameter].[parameter]").VisibleItemsList = tableVar

I'm still receiving an error message. Could you help me, please?

Thank you for advice - since Stackoverflow have so many users it's not so simple to response fast. My error is: Run-time error '1004': The item could not be found in the OLAP Cube — matandked, Jun 19 '12 at 11:15
Why not just have your function return a pipe (|)-delimited list of fields, then you can just use `Split(myVar, "|")` to create the array. — Tim Williams, Jun 19 '12 at 14:51

score 1 · Answer 1 · answered Jun 18 '12 at 21:09

1

you have a typo in your code, daty should say myVar. (Either that or we're missing more details)

answered Jun 18 '12 at 21:09

xchiltonx

1,946
3
20
18

Yes, you're right - thanks - before asking question, I rewrote part of my code. Anyway - it doesn't resolve my problem. – matandked Jun 19 '12 at 10:51
it seems that I made another mystake - I forgot to change "tablica" to "tableVar" – matandked Jun 22 '12 at 21:49

score 0 · Answer 2 · answered Jun 22 '12 at 22:04

Stupid thing, but error message is simply correct - there's no such items in Cube:

Run-time error '1004': The item could not be found in the OLAP Cube

I gave incorrect parameter here:

ActiveSheet.PivotTables("PivotTable1").PivotFields("[parameter].[parameter]").VisibleItemsList = tableVar

My code was unnecessary complicated - sorry for wasting your time. Now my problem will be - how to check if specific dimensions or whole Cube exist... Thanks once more for help.

Pivot Tables - VBA

2 Answers2