|< Day Day Up >|| |
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.
Make Things Easy on Yourself
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.
Dim pc As PivotCache
Dim ws As Worksheet
Dim pt As PivotTable
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "PivotTableSheet" Then
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
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.
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:
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.
|< Day Day Up >|| |