PDA

View Full Version : VBA CODE OLAP Pivot Table Excel 2007



honda17
08-27-2009, 05:42 PM
Hi,

I have a workbook with a pivot tables on each tab, these pivot tables are connected to an OLAP Cube in MS SQL Server database, I am trying to update all pivots programmatically by changing the filter items. Any help is appreciated.
Thx

Bob Phillips
08-28-2009, 01:41 AM
When you say update, do you mean you wish to change whata is being filtered, or add/remove items from therows/columns?

honda17
08-28-2009, 08:35 AM
I mean change what it is filtered.

Bob Phillips
08-28-2009, 09:51 AM
You have to specify the dimension, the item in the dimension and the value.

This is an example from one of mine



ActiveSheet.PivotTables("myPivot").PivotFields( _
"[Location Master].[Location Hierarchy]").CurrentPageName = _
"[Location Master].[Location Hierarchy].[Location Type].&[BONDED]"