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