Creating a Pivot Table Report Programmatically

Although the PivotWizard utility has undergone many improvements to make it easy to use, some users still find the process of creating PivotTable reports confusing. For those users, you may want to generate PivotTables via VBA code. Also with VBA, you can make many formatting changes to the existing PivotTables. This section demonstrates how you can work with PivotTables programmatically. We will start by creating the PiviotTable report shown earlier in Figure A-3 using the data source presented in Figure A-1. Here's the code that generates that PivotTable:

Sub CreateNewPivot()

Dim wksData As Worksheet Dim rngData As Range Dim wksDest As Worksheet Dim pvtTable As PivotTable

' Set up object variables

Set wksData = ThisWorkbook.Worksheets("Source Data")

Set rngData = wksData.UsedRange

Set wksDest = ThisWorkbook.Worksheets("Sheet1")

' Create a skeleton of a PivotTable

Set pvtTable = wksData.PivotTableWizard(SourceType:=xlDatabase, _ SourceData:=rngData, TableDestination:=wksDest.Range("B5"))

' Close the Pivot Table Field list that appears automatically ActiveWorkbook.ShowPivotTableFieldList = False

' Add fields to the PivotTable With pvtTable

.PivotFields("Vendor").Onentation = xlRowField

.PivotFields("Equipment Type").Orientation = xlRowField .PivotFields("Warranty Type").Orientation = xlColumnField With .PivotFields("Total Units") .Orientation = xlDataField .Function = xlSum End With

.PivotFields("Equipment Id").Orientation = xlPageField End With

' Autofit columns so all headings are visible wksDest.UsedRange.Columns.AutoFit End Sub

The procedure shown above creates a new PivotTable report using the PivotTableWizard method of a Worksheet object. Notice that this method takes a few arguments that allow you to specify the type of the data source, its location, and the location where the PivotTable reports should be placed. All these arguments of the PivotTableWizard method are optional. However, it is a good idea to specify these arguments, as we did in our example code. Because you can create a PivotTable from various sources of data, by using the xlDatabase constant, our code specifically says that our data comes from an Excel range. If you want to create a PivotTable report from another PivotTable, use xlPivotTable for the SourceType argument. And if your data is to be pulled from an external database (as shown in a later example), specify xlExternal as the SourceType.

The SourceData argument in the example procedure is a reference to the used range on the worksheet containing the source data. The Table-Destination argument has a reference to cell B5 on Sheet1 in the current worksheet. This is where the upper left-hand corner of the PivotReport will be placed. This code assumes that Sheet1 exists in the workbook. If you don't have Sheet1, it's easy enough to add one via the VBA code prior to setting the reference.

It is important to understand that when you call the PivotTableWizard method, you create a blank PivotTable report. All the fields from the data source are hidden. To make the fields visible, you need to add them to appropriate areas of the PivotTable report. As you recall, there are four such areas. PivotTable Wizard automatically displays the list of fields that you can add. However, because you are creating a PivotTable programmati-cally, there is no need to display that list on the screen. By setting the ShowPivotTableFieldList property to False, the list will go off the screen and you can concentrate on specifying the position of the fields in the PivotTable report areas. For each field that you want to display in the PivotTable report, set the Orientation property of the PivotField object. Use the following constants for the Orientation property: xlRowField, xlColumnField, xlDataField, and xlPageField. Note that for the Total Units field placed in the Data area, the procedure sets the Function property of the PivotField object to Sum.

When you are creating a PivotTable report via code, you may need to check whether a PivotTable already exists in the destination worksheet. You can place the following code just below the code that sets variables (see the CreateNewPivot procedure above):

' Check if PivotTable already exists If wksDest.PivotTables.Count > 0 Then

MsgBox "Worksheet " & wksDest.Name & " already contains a pivot _

table." Exit Sub End If

When you run the CreateNewPivot procedure, you should see a PivotTable report resembling the one in Figure A-3.

0 0

Post a comment