Produce Pivot Tables Programmatically

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."

MsgBox xlLastCell