Change date in a OLAP Cube based pivot table

[Hans10]

I have had posted a similar issue earlier, but it did not solve my problem. Therefore, I try again.

My Pivot table gets data from a OLAP Cube. Using the macrorecorder i got this code by setting a new date in the filter and then update the Pivot table. I can manually change the date and it will update when running the macro again.

Sub Update_pvt1()

With Sheets("pvt 1").PivotTables("Pivottabel1")
.PivotFields("[Dato].[AarMaanedDagH].[Aar]").ClearAllFilters
.CubeFields(47).EnableMultiplePageItems = True
.PivotFields("[Dato].[AarMaanedDagH].[Aar]").VisibleItemsList = Array("")
.PivotFields("[Dato].[AarMaanedDagH].[AarMd]").VisibleItemsList = Array("")
.PivotFields("[Dato].[AarMaanedDagH].[AarMaanedDag]").VisibleItemsList = Array("[Dato].[AarMaanedDagH].[AarMaanedDag].&[20150826]")
.PivotTables("Pivottabel1").PivotCache.Refresh

End With
End Sub

In my case i will always have to update my Pivot table to the day before (yesterday!)


Solution 1: Maybe you can put this in the code in one way or another?

Array("[Dato].[AarMaanedDagH].[AarMaanedDag].&[Format(Date - 1, "YYYYMMDD")]")


Solution 2: The trick where you use a range/cell reference? But the trick has not worked for me in the past!


Be aware that the date in my Cube is formated like this "YYYYMMDD" example "20150827" for today.

Any suggestions? it has been an ongoing problem and i have many tables that i almost every day wants to update.

Thanks to jorismoerings for showing interest in my previous posted thread.

 

[Hans10]

Is this impossible?