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).

Today, 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.

• Creating an Excel pivot table
• Adding fields to a pivot table
• Adding a calculated field
• Displaying or hide a pivot table field
• Deleting a pivot table
• Refreshing a pivot table
• Clearing a pivot table
• Creating a pivot chart

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"))
    targetSheet.Select()
 
    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", _
        Excel.XlConsolidationFunction.xlCount)
    pt.AddDataField(pt.PivotFields("Order Total"), "Total for Date", _
        Excel.XlConsolidationFunction.xlSum)
 
    '--OR--
    'AddPivotFields(pt, "Order Total", "Order Count", _
    '   Excel.XlConsolidationFunction.xlCount)
    'AddPivotFields(pt, "Order Total", "Total For Date", _
    '   Excel.XlConsolidationFunction.xlSum)
 
    Marshal.ReleaseComObject(pt)
    Marshal.ReleaseComObject(targetSheet)
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 = _
        Excel.XlPivotFieldOrientation.xlDataField
    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 = _
            Excel.XlPivotFieldOrientation.xlHidden
    Else
        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)
        Try
            sheet.PivotTables(PivotTableName).Delete()
        Catch ex As Exception
        End Try
        Marshal.ReleaseComObject(sheet)
    Next
    Marshal.ReleaseComObject(worksheets)
    Marshal.ReleaseComObject(activeWorkbook)
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)
    pt.PivotCache.Refresh()
 
    Marshal.ReleaseComObject(pt)
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)
    pt.ClearTable()
 
    Marshal.ReleaseComObject(pt)
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( _
        Excel.XlChartType.xlColumnClustered)
    chart.SetSourceData(pivotTable.TableRange2)
 
    Marshal.ReleaseComObject(targetSheet)
    Marshal.ReleaseComObject(chart)
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

• Part 1: Application and base objects
• Part 2: Customizing the Excel User Interface: What is and isn't customizable
• Part 3: Customizing Excel ribbons and toolbars
• Part 4: Customizing Excel main menu, context menus, and Backstage view
• Part 5: Creating custom task panes for Excel 2013 – 2003
• Part 6: Working with Excel workbooks and worksheets: VB.NET examples
• Part 7: Working with Excel cell values, formulas and formatting: C# samples
• Part 8: Working with Excel charts
• Part 9: Working with Excel tables and ranges
• Part 10: Importing data from SQL databases and other sources to Excel