Programming Excel with VBA and .NET

PivotLayout Members

Use the PivotLayout object to access the pivot table of a pivot chart. Use the Chart object’s PivotLayout property to get a reference to this object. The PivotLayout object has the following members:

AddFields	Application
ColumnFields	Creator
CubeFields	DataFields
HiddenFields	InnerDetail
PageFields	Parent
PivotCache	PivotFields
PivotTable	RowFields
VisibleFields

If the active worksheet contains a pivot table and you call Charts.Add, Excel automatically creates a pivot chart for the pivot table. You can then use the chart’s PivotLayout property to navigate back to the underlying pivot table to set the pivot chart layout or change other elements. For example, the following code creates a new pivot chart then changes the layout of the pivot chart:

Sub ChangeChartLayout( ) Dim chrt As Chart, pt As PivotTable, pf As PivotField ' Activate a pivot table. Sheets("BookSales").Activate ' Create a pivot chart Set chrt = Charts.Add ' Set chart properties. chrt.ChartType = xlLine chrt.Axes(xlCategory).TickLabelPosition = xlNone ' Get the pivot table. Set pt = chrt.PivotLayout.PivotTable ' Change layout pt.PivotFields("ProductName").Orientation = xlPageField ' Clear data fields (ignore errors). On Error Resume Next pt.DataPivotField.Orientation = xlHidden pt.PivotFields("RelativeRank").Orientation = xlDataField pt.PivotFields("SalesRank").Orientation = xlHidden On Error GoTo 0 ' Select a page field pt.PageFields("ProductName").CurrentPage = "Essential SharePoint" ...