Ty Anderson

Working with Excel pivot tables: VB.NET code examples

Microsoft Excel is the original business intelligence tool. Long before Big Data was even a twinkle in its mother's eye, there was Excel and its pivot tables. When I was a serious spreadsheet jockey trying to make my mark on the world (as a financial auditor for a super-serious accounting firm), I used pivot tables all the time… whether I needed to or not. They made my life easier and impressed my clients and my boss’s boss (but not my boss… he knew me too well).

HauntingToday, I’ll show-off some code that shows how to automate pivot tables. It’s quite likely your user base is 100% comprised of uber-serious spreadsheet jockeys. These people need your help. This code will help you relate to them and make their life easier.

NOTE: I imported data from the OrderSummary query of the Northwind sample Access database. I then used this data as the basis of my pivot table.

Create an Excel pivot table

To create an Excel PivotTable you need a range filled with data or a table. In this sample, I pass a table to use as the basis of the pivot table. I prefer to work with tables as they are cleaner and more structured (as compared to ranges).

Private Sub CreatePivotTable(tableName As String)
    Dim targetSheet As Excel.Worksheet = ExcelApp.Sheets.Add
    Dim ptName As String = "MyPivotTable"
    'We'll assume the passed table name exists in the ActiveWorkbook
    targetSheet.PivotTableWizard(Excel.XlPivotTableSourceType.xlDatabase, _
        tableName, targetSheet.Range("A5"))
    Dim pt As Excel.PivotTable = targetSheet.PivotTables(1)
    'To be professional or merely resuable, the name could be passed as parameter
    With pt.PivotFields("Order Date")
        .Orientation = Excel.XlPivotFieldOrientation.xlRowField
        .Position = 1
    End With
    pt.AddDataField(pt.PivotFields("Order Total"), "Order Count", _
    pt.AddDataField(pt.PivotFields("Order Total"), "Total for Date", _
    'AddPivotFields(pt, "Order Total", "Order Count", _
    '   Excel.XlConsolidationFunction.xlCount)
    'AddPivotFields(pt, "Order Total", "Total For Date", _
    '   Excel.XlConsolidationFunction.xlSum)

End Sub

The procedure first creates a new worksheet that will serve as the location for the new pivot table. Then, it creates a new pivot table with the help of the PivotTableWizard. We then tell the wizard the data source type, the data source name, and where to place the pivot table.

After creating the pivot table, the method adds a row field and two data fields (a count and sum of the Order Total column).

Add fields to a pivot table

If you have a pivot table, you might want to automate the addition of a field. Sure, I just showed you how to do it, but what if you wanted a reusable function that did it for you?

Private Sub AddPivotFields( _
    pivotTable As Excel.PivotTable, _
    fieldName As String, _
    fieldCaption As String, _
    fieldType As Excel.XlConsolidationFunction)
    pivotTable.AddDataField(pivotTable.PivotFields(fieldName), fieldCaption, fieldType)
End Sub

This method accepts a few self-explanatory parameters (if you disagree, just use the comments to ask a question) and adds a field to the passed pivot table.

Add a calculated field

Calculate fields are very cool. I’ve seen users create pivot tables and then add their own calculated field to it. This is fine but not elegant. The main problem is if the user refreshes the pivot table, their column disappears! WTW! The proper way to do this is use the Add calculated field feature to build a formula and insert it as a pivot table field. This code does just that.

Private Sub AddCalculatedField( _
    sheet As Excel.Worksheet, _
    pivotTable As Excel.PivotTable, _
    fieldName As String, _
    formulaText As String)
    'formulaText="= ('Shipping Fee'+Taxes )/'Order Total'
    pivotTable.CalculatedFields().Add(fieldName, formulaText, True)
    pivotTable.PivotFields(fieldName).Orientation = _
    pivotTable.PivotFields(fieldName).NumberFormat = "0.00%"
End Sub

The procedure is built for re-use. In the comment, I show a good example of a formula. Notice this field is added to the CalculatedFields collection. This is different from the previous examples that called AddDataField. As a special touch, I apply a NumberFormat to display the column as a percentage.

Display or hide a pivot table field

Hiding a field is easy. Re-displaying is too as long you understand that after you hide a field, you need to add it back to display it.

Private Sub DisplayField(pivotTable As Excel.PivotTable, _
    fieldName As String, _
    display As Boolean)
    If display Then
        pivotTable.PivotFields(fieldName).Orientation = _
        AddPivotFields(pivotTable, fieldName, _
            "Sum of " & fieldName, Excel.XlConsolidationFunction.xlSum)
    End If
End Sub

Hiding a field is a matter of setting its Orientation to xlHidden. To display it again, we add it back by using the AddPivotFields procedure we covered earlier.

Delete a pivot table

To delete a pivot table, you need to use the PivotTables class. This class has the Delete method. This sample looks for the name of a pivot table (the PivotTableName parameter) in each worksheet.

Private Sub DeletePivotTable(PivotTableName As String)
    Dim activeWorkbook As Excel.Workbook = ExcelApp.ActiveWorkbook
    Dim worksheets As Excel.Worksheets = activeWorkbook.Worksheets
    For i = 1 To worksheets.Count
        Dim sheet As Excel.Worksheet = worksheets(i)
        Catch ex As Exception
        End Try
End Sub

The code loops through each worksheet and attempts to delete the passed pivot table name. The delete is a Try/Catch block because, if it doesn’t exist, I don’t care… just keep looping and try again.

Refresh a pivot table

Refreshing a pivot is easy. First access the pivot table (you need to know the worksheet and pivot table name) then call its Refresh method.

Private Sub RefreshPivotTable(sheet As Excel.Worksheet, pivotTableName As String)
    Dim pt As Excel.PivotTable
    pt = sheet.PivotTables(pivotTableName)
End Sub

Clear a pivot table

To clear a pivot table of all fields and start over, do the same thing as the previous example but call ClearTable method.

Private Sub ClearThePivotTable(sheet As Excel.Worksheet, pivotTableName As String)
    Dim pt As Excel.PivotTable
    pt = sheet.PivotTables(pivotTableName)
End Sub

Boom… the slate will be clean and ready to rebuild.

Create a pivot chart

After creating a nice pivot table that effectively summarizes data, the logical next step is to create a chart. This sample creates a new pivot chart and places it on a new worksheet.

Private Sub CreatePivotChart(sheet As Excel.Worksheet, pivotTable As Excel.PivotTable)
    Dim targetSheet As Excel.Worksheet = ExcelApp.Sheets.Add
    Dim chart As Excel.Chart = targetSheet.Shapes.AddChart( _
End Sub

Adding the chart is simple. Setting the source data is a little tricky but that’s typically due to ignorance. After you know how to do it, it isn’t difficult at all. The trick is to use the pivot table’s TableRange2 property. This property sets the full range of the pivot table as the chart’s data source.


I know everyone is spread out all over the globe but I believe it is important to note the Fall is now officially here. The baseball World Series ended last night. This alone marks the end of Summer to me. Combine the season end with Halloween and the prospect of having only hockey and basketball to watch for the foreseeable future… well… this is scary indeed.

Available downloads:

This sample Excel add-in was developed using Add-in Express for Office and .net:

Excel PivotTables Add-in VB.NET

Excel add-in development in Visual Studio for beginners


  • https://secure.gravatar.com/avatar/8c404f5e39a3a1d189cb4c1406dfb5de?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Brian says:

    I tried the Excel PivotTables Add-in VB.NET solution. I’m getting an error that the system cannot find the file specified (AddinExpress.MSO.2005). It doesn’t like this reference for some reason.

  • https://secure.gravatar.com/avatar/c68f03884efb33fd95bcfbd9f1078c07?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Ty Anderson says:

    Hi Brian,

    Do you have Add-in Express installed?
    From your description, the ADX reference is not on you system.

    Will you double check?


  • https://secure.gravatar.com/avatar/723f9e89b9f73449bbc7d876d89ad0fd?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Mark Vander Veen says:

    Thanks for the code above. I was able to create the pivot using .NET but the format is wrong for my needs. I need a way to perform the following change in my code to modify the pivot table “Move Values to Columns”. I’ve done this in VBA but can’t find how to do it in VB .Net VS2015.

    Any help would be appreciated.

  • https://secure.gravatar.com/avatar/29957f26ad2d8ba527fd9cc8cfa7b2e0?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Andrei Smolin (Add-in Express Team) says:

    Hello Mark,

    When responding to such questions I usually recommend using the Macro Recorder to get some VBA code and then port it to .NET. There should be no problem with this as VBA and VB.NET are really close when you deal with properties/methods of an object model. I would recommend avoiding using default properties: in VBA you can write myRange(i, j) which is a short way to write myRange.Item(i,j). In VB.NET, I recommend using the long way. To see the default properties in the VBA object browser, right-click the list of properties/methods and choose Show Hidden Members on the context menu.

  • https://secure.gravatar.com/avatar/fdcc830da370c184422a7ecbdd272e94?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Zahoor Shah says:

    After creating a Pivot table, I want to control MDX query when a pivot table field checkbox is checked.
    Which event is triggered for that, can we write a wrapper around that?
    Please suggest..

  • https://secure.gravatar.com/avatar/29957f26ad2d8ba527fd9cc8cfa7b2e0?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Andrei Smolin (Add-in Express Team) says:

    Hello Zahoor,

    Should this be a regular Excel thing, I’d check if the PivotTable-related events of the Worksheet class can be used; see https://msdn.microsoft.com/VBA/Excel-VBA/articles/worksheet-object-excel. If these events won’t help, I’d record a VBA macro while setting/clearing that check box; I expect that the macro discloses a property specifying the state of the check box. If so, I would use the approach described at https://www.add-in-express.com/creating-addins-blog/2012/02/21/excel-shapes-events/. The sample project provided demonstrates using the CommandBars.OnUpdate event (in Add-in Express, it is mapped to the ADXExcelAppEvents.CommandBarsUpdate event) to compare the current state of properties with their previous values; find more details on that page.

    But, since this is a PowerPivot-related question, I suggest that you google for how to customize PowerPivot.

  • https://secure.gravatar.com/avatar/fdcc830da370c184422a7ecbdd272e94?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Zahoor Shah says:

    Hi Andrie,

    Thanks for the valuable inputs on my query; however I am a step ahead now, I am able to catch PivotTableChangeSync event where I am getting hold on Pivot table object; Now what I want is to modify the MDX query I am able to get the MDX query from Target.MDX, which is only get property of Pivot table; So is there any alternative way to modify MDX query for customization. Thanks

  • https://secure.gravatar.com/avatar/29957f26ad2d8ba527fd9cc8cfa7b2e0?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Andrei Smolin (Add-in Express Team) says:

    Hello Zahoor,

    Unfortunately, PowerPivot isn’t part of Excel as it is just an add-in and the features it provides aren’t part of the Excel object model. I believe that Microsoft provides resources on how to use and program it. I suggest that you google for them.

Post a comment

Have any questions? Ask us right now!