[markmahon]

Hi -

Basic question: How can I iterate through the items in a Pivot Table report filter?

So lets say a pivot table contains a "Car Model" report filter with elements such as "Chevy", "Olds", "Toyota", etc. For starters I just want to be iterate through the Car Model filter and show each element in a MessageBox (just to get the hang of it).

Other notes:
I'm automating Excel 2007 via C# and VSTO.
The PivotTable data source is an OLAP cube stored on a server
While I'd like a C# snippet I'll take VB.Net (and even VBA, but third choice)
What I really want to do is change the selected element to "Chevy", print the page, select "Olds", print the page, select "Toyota", print the page ....

My code so far:

Excel.PivotTable pvt = (Excel.PivotTable)Globals.Sheet1.PivotTables("PivotTable1");
Excel.PivotField fld = (Excel.PivotField) pvt.PivotFields("[Contract Info].[Model].[Model]");
fld.CurrentPageName = "[Contract Info].[Model].&[Chevy]"; //<- Changes the filter from "All" to "Chevy"
// Code works to this point

But I can't figure out how to expose the values in the PivotField (psudo code along the lines of:)
foreach (PivotItem item in fld.PivotItems)
MessageBox.Show(item.Name)


Thanks for any suggestions
Mark

Post is abusive