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
Previous
Register
downbylhl August 28, 2006 (Article Rating: