Produce Pivot Tables Programmatically

For sometime now, I've used Microsoft Excel as my primary VBScript reporting medium. It's easy to sort and organize data in Excel, and in general, Excel is easy to use. I've also found that most people are familiar with it. Team members and managers seem to like Excel because they can easily add their own touches, such as charts and colors, to reports before passing them on to upper management.

Not too long ago, I was manually creating a pivot table that displayed a count of locked-out accounts for an NT domain administration report. I decided to try scripting this functionality. I had tried my hand at scripting pivot tables in the past, but the Microsoft documentation and Visual Basic for Applications (VBA) macros just didn't give me the syntax structure I needed. I had found most of the pieces of this puzzle. I just needed to find the missing few, then put all the pieces together. After an exhaustive trial-and-error period and actually walking away from the project for a couple of months, I decided to give it another shot. It took a couple of long nights, but I finally got all the pieces to fit. The result was two scripts: PivotTable.vbs and PivotTable2.vbs.

PivotTable.vbs creates a pivot table that shows a quarterly breakdown of three roommates and their contributions toward living expenses. PivotTable2.vbs constructs a pivot table for locked-out accounts. It also contains some advanced functionality not included in the first script.

A Look at PivotTable.vbs
Listing 1 shows PivotTable.vbs. The first part of the script sets up the Excel spreadsheet and creates the main worksheet called Quarterly Bills. Note that headers are required in the first row when you create a pivot table.

The code at callout A in Listing 1 builds the sample data to be used in the pivot table. The randomly created test data consists of three roommate names, three types of utilities, the amount paid to the utility, and the quarter the payment was made. Although this example is basic, it paints a clear picture of how you can start producing pivot tables programmatically.

The code at callout B in Listing 1 creates a new worksheet called Summary. The pivot table will reside in this worksheet. The code then selects Quarterly Bills (i.e., the main worksheet that contains the data).

The pivot table creation process begins by calling the PivotTableWizard method, which provides an interface to Excel's PivotTable Wizard. As callout C in Listing 1 shows, I use the method's SourceType argument to specify the type of data source for the pivot table. In this case, I set SourceType to the xlDatabase constant, which specifies the type as an Excel database. There are other constants such as xlExternal (specifies the type as another application) you can use, but this example will concentrate on getting data from the current spreadsheet. Next, I define the range of cells in which the data resides in the Excel database. You have to be precise here. In most cases, the starting point of the range will be A1. The endpoint of the range is the last row and column of data. The column can be hard-coded. In this case, I know that the last column is D because the headers are hard-coded. The code stores the endpoint row in a variable called lastrow. Next, I set the pivot table's destination, which is the Summary worksheet, starting at row 1, column 1. Finally, I provide the name of the database, which is Quarterly Bills.

The lines at callout D in Listing 1 define how the pivot table is constructed. There are three main pieces: row fields, column fields, and data fields. The code uses the Orientation property to define these fields. The Orientation value of 1 specifies a row field, 2 specifies a column field, and 4 specifies a data field. The code also names each field. In this example, the roommates' names and type of utility will appear in the rows labeled Name and Description, respectively. The column containing the quarter the payment was made will appear in a column labeled Quarter. The data field is labeled Amount. Note that the PivotTable Wizard automatically prepends Sum of to Amount, so the data field will appear as Sum of Amount, as Table 1 shows. The PivotTable Wizard also automatically calculates the Grand Total values. In addition, the PivotTable Wizard calculates subtotal values, which the wizard automatically labels as Jack Total, Joe Total, and Judy Total in Table 1.

The two lines after callout D simply close the interface to Excel's PivotTable Wizard. Note that Microsoft Office 2000 doesn't have field-header functionality for pivot tables, so if you're going to use this script with Office 2000 only, you should add the On Error Resume Next statement to the beginning of the script or comment out the line at callout E in Listing 1.

You might want to try switching the Orientation values for the row and column fields to see different layouts for the pivot table. For example, if you change the Orientation value from 1 to 2 in the code

Xl.ActiveSheet.PivotTables _ ("Quarterly Bills"). _PivotFields _ ("Description").Orientation = 1

the electricity, telephone, and water data will appear in columns rather than rows. This layout works well if you have only a few unique values for a field. If you have a lot of different values, you'll probably want to list the field as a row; otherwise, you'll have to scroll right to see all the columns in the spreadsheet.

You need to leave the data-field orientation alone. In this example, that would be the code

Xl.ActiveSheet.PivotTables _ ("Quarterly Bills") _.PivotFields _ ("Amount").Orientation = 4

A Look at PivotTable2.vbs
PivotTable2.vbs creates a worksheet called Test and populates it with a small list of user account data under the headers UserID, Account Locked, and Disabled Account. Listing 2 shows an excerpt from PivotTable2.vbs. You can download the entire script (as well as PivotTable.vbs) from the Windows Scripting Solutions Web site. Go to http://www.windowsitpro.com/windowsscripting, enter 45502 in the InstantDoc ID text box, then click the 45502.zip hotlink.

PivotTable2.vbs is similar to PivotTable.vbs, except that PivotTable2.vbs programmatically resolves the value for the lastcell variable and filters the test data so that the pivot table displays only specific values (i.e., locked accounts). First, PivotTable2.vbs populates the spreadsheet with the test data. Next, the script programmatically obtains the value for the lastcell variable, as callout A in Listing 2 shows. Now if you add new headers, you don't have to manually go in and change the range. The code at callout A uses the SpecialCells method with the value of 11, which represents the xlLastCell constant. The Address property returns the cell row and column. The xllastcell variable stores this information, which the script uses in the PivotTableWizard method call, which callout B in Listing 2 shows.

The code at callout C in Listing 2 illustrates how to use the Subtotals property to suppress subtotals in a pivot table. This property has 12 elements, which Table 2 lists. Setting an element to True indicates you want a subtotal for that element; setting an element to False means you don't want a subtotal for that element. You always get at least one subtotal (i.e., a count of records) in a pivot table, even if you suppress all the subtotal elements. In PivotTable2.vbs, I suppressed all the elements and used the unsuppressable subtotal for the Grand Total row count of all the locked accounts.

The last key task that PivotTable2.vbs performs is displaying the locked accounts. When I first worked on scripting this task, I thought that I'd just have to specify the values I wanted to see. However, contrary to the way I thought it would work, I discovered I had to filter out every value in a specific field that I didn't want to appear by setting its Visible property to False. For this example, there are only two possible values for the Account Locked status: Yes (i.e., the account is locked) and No (i.e., the account isn't locked). So, I set the Visible property to False for those data fields that have the value of No in the Account Locked column, as the code at callout D in Listing 2 shows. In this code, PivotTables(xldata) refers to the source worksheet (i.e., the Test worksheet. PivotFields("Account Locked") refers to the column with the Account Locked header. And PivotItems("No") selects those data fields that have the value of No under this header; the code excludes these data fields by setting the Visible property value to False. Therefore, only locked-out accounts will be appear in the pivot table.

Using and Adapting the Scripts
To use PivotTable.vbs and PivotTable2.vbs, you need to be running Windows 2000 or later and Microsoft Office 2000 or later. After you run and experiment with these scripts, you can adapt them to create pivot tables that display data from your organization. To help you find the methods and properties you need, you can use the resources mentioned in the sidebar "How to Learn About Excel's Methods and Properties."