Problem

In Excel, there are numerous PivotTable settings available. These can be found by right clicking the PivotTable and selecting PivotTable options. Not all of these are accessible through the ExcelApplication PivotTable API yet.

Solution

For any properties that are not yet available in ExcelApplication, we recommend that you create a template file that contains a PivotTable and set the PivotTable options on that table, rather than creating a PivotTable from scratch.

Then use ExcelApplication to modify the existing PivotTable by changing the data source and adding PivotFields.

The settings will be preserved, even if the data source of the PivotTable is changed and new PivotFields are added.

To get a handle on a PivotTable:

 worksheet.PivotTables[0]; //By index in the worksheet worksheet.PivotTables["PivotTable1"]; //By name 

To change the data source of a PivotTable, use PivotTable.ChangeDataSource.

For more about building PivotTables, see Creating a Basic PivotTable.

Follow

Related posts: