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.
This sample Excel add-in was developed using Add-in Express for Office and .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