CODEWITHSUNDEEP
excelvbapivot-tableolap-cube
matandked
matandked

Reputation: 1565

Pivot Tables - VBA

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?

Upvotes: 0

Views: 3220

Answers (2)

matandked
matandked

Reputation: 1565

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.

Upvotes: 0

xchiltonx
xchiltonx

Reputation: 2031

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

Upvotes: 1

Related Questions