Programming PivotTables

---

Programming PivotTables involves creating the appropriate PivotCache object and then using the PivotCache object to create a PivotTable object. Once the PivotTable object is available, you can modify its properties to view the data from different positions.

Tip 

Make Things Easy on Yourself
Creating PivotTables can be difficult, so you might want to fall back on the standard macro writer's trick: record a new macro using the commands necessary to perform the task you wish to program, and then use the recorded code as the basis of your new program. This trick is extremely useful when dealing with complex object models such as those used by Excel to manipulate PivotTables.

Creating a PivotTable

The code to create a simple PivotTable is shown the following listing. The routine begins by declaring temporary objects to hold references to the PivotCache and the PivotTable objects, plus a temporary variable that will be used to delete the worksheet containing the PivotTable.

Sub CreatePivotTable()
 
Dim pc As PivotCache
Dim ws As Worksheet
Dim pt As PivotTable
 
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name = "PivotTableSheet" Then
        ws.Delete
      
    End If
   
Next ws
   
Set ws = ActiveWorkbook.Worksheets.Add()
ws.Name = "PivotTableSheet"
 
Set pc = ActiveWorkbook.PivotCaches.Add(xlDatabase, "AllData!R1C1:R1117C6")
 
Set pt = pc.CreatePivotTable("PivotTableSheet!R1C1", "My Pivot Table")
 
pt.PivotFields("Month").Orientation = xlRowField
pt.PivotFields("Month").Position = 1
 
pt.PivotFields("Hour").Orientation = xlColumnField
pt.PivotFields("Hour").Position = 1
 
pt.AddDataField pt.PivotFields("Sales"), "Sum of Sales", xlSum
 
End Sub

A simple For Each loop is used to scan through the collection of Worksheet objects associated with the active workbook, looking for a worksheet named PivotTableSheet. If the worksheet is found, the worksheet is deleted. After the worksheet is deleted, a new worksheet with the same name is added to the Worksheets collection. This code ensures that the worksheet is empty before the PivotTable is added.

Next a PivotCache object is created using the PivotCaches.Add method. The xlDatabase argument indicates that the data is formatted as a series of rows and columns, while the second parameter indicates that the data is located on the AllData worksheet in columns 1 to 6 and rows 1 to 1117.

Once the PivotCache object has been created, a PivotTable object can be created by using the PivotCache object's CreatePivotTable method. The CreatePivotTable method takes two arguments, the location of the upper-left corner of the PivotTable and the name of the PivotTable.

By default, the PivotTable is empty, so you need to define the row fields, the column fields, and the data fields. Defining the rows and the columns involves using the PivotTable object's PivotFields collection and setting the Orientation property. The Position property is also set to 1, to allow you to add additional row and column fields.

Finally, the AddDataField method is used to define which fields are included in the data area of the PivotTable. This routine takes three arguments, the PivotField object that should be added to the data area, the title for the field, and the function used to combine the data fields together.

Running the CreatePivotTable routine creates the PivotTable shown in Figure 16-7.

Figure 16-7: This PivotTable is the result of running the subroutine shown on page 358.

Creating a PivotChart

Once you've created a PivotTable, creating a PivotChart is very easy. Simply create a new Chart object and use the PivotTable as the data source using the following code:

Charts.Add
ActiveChart.SetSourceData Sheets("PivotTableSheet").Range("A1")
ActiveChart.Location xlLocationAsNewSheet, "Pivot Chart"  

The Charts.Add method creates a new Chart object. Then SetSourceData method specifies the PivotTable located on PivotTableSheet starting at cell A1. Finally, the Location method is used to create a new sheet to hold the PivotChart and to give it the title Pivot Chart.

---