Pivot CalculatedFields Excel Add-in with code example

Pivot CalculatedFields Excel Add-in with code example

A collection of PivotField objects that represents all the calculated fields in the specified PivotTable report. Use the CalculatedFields method of the PivotTable object to return the CalculatedFields collection.

Note: the code is written using VSTO (Visual Studio Tools for Office ) hence, following prerequisites are recommended:

C# code example

private void btnPivotTable_Click(object sender, RibbonControlEventArgs e)
{
	excel.Workbook oWorkbook = Globals.ThisAddIn.Application.ActiveWorkbook;
	excel.Worksheet oWorksheet = Globals.ThisAddIn.Application.ActiveSheet;

	//Create pivot caches collection
	excel.PivotCaches oCaches = oWorkbook.PivotCaches();
	
	//get pivot tables collection from worksheet
	excel.PivotTables oPivotTables = oWorksheet.PivotTables();
	
	//add Pivot field
	excel.PivotTable oPivotTable = oWorksheet.PivotTables("myTable");

	//add CalculatedFields
	oPivotTable.CalculatedFields().Add("myField", "IF('SUM' > 10000,'COUNT',0", true);

	//set field
	excel.PivotField oPivotField_one = oPivotTable.PivotFields("myField");
	oPivotField_one.Orientation = excel.XlPivotFieldOrientation.xlRowField;
	oPivotField_one.Position = 1;
}

VB.Net Code example

Private Sub btnPivotFields_Click(sender As Object, e As RibbonControlEventArgs) Handles btnPivotFields.Click
	Dim oWorksheet As excel.Worksheet
	oWorksheet=Globals.ThisAddIn.Application.ActiveSheet

	'get pivot tables collection from worksheet
	Dim oPivotTables as excel.PivotTables
	oPivotTables = oWorksheet.PivotTables()

	'Get pivot
	dim oPivotTable as excel.PivotTable
	oPivotTable=oPivotTables("myTable")

	'add CalculatedFields
	oPivotTable.CalculatedFields().Add("myField", "IF('SUM' > 10000,'COUNT',0", true)

	'set field
	dim oPivotField_one As excel.PivotField
	oPivotField_one = oPivotTable.PivotFields("myField")
	oPivotField_one.Orientation = excel.XlPivotFieldOrientation.xlRowField
	oPivotField_one.Position = 1
End Sub

VBA code example

Public Sub pivotFieldsCodeExample()
    Dim oWorksheet As Worksheet
    Set oWorksheet = ActiveSheet

    'get pivot tables collection from worksheet
    Dim oPivotTables As PivotTables
    Set oPivotTables = oWorksheet.PivotTables()

    'Get pivot
    Dim oPivotTable As PivotTable
    Set oPivotTable = oPivotTables("myTable")

    'add CalculatedFields
    oPivotTable.CalculatedFields.Add "myField", "IF('SUM' > 10000,'COUNT',0", True

    'set field
    Dim oPivotField_one As PivotField
    Set oPivotField_one = oPivotTable.PivotFields("myField")
    oPivotField_one.Orientation = Excel.XlPivotFieldOrientation.xlRowField
    oPivotField_one.Position = 1
End Sub

Next >> Pivot Items in Pivot Table Excel VBA code example

Leave a Reply

Your email address will not be published.