Create Excel Reports the VBScript Way, Part 5

Microsoft Excel's PivotTable feature is a powerful tool. With it, you can organize large amounts of data into attractive, focused reports. PivotTable reports can group similar records together, produce group totals, filter specific data values, produce group sums, averages, minimums, and maximums, and do much more. This special Excel feature also demonstrates amazing performance when it comes to crunching large amounts of data. I think you’ll find programming PivotTable reports well worth the time and effort that you put into them.

The name “PivotTable” comes from the fact that it’s easy to move (pivot) data fields into or out of the PivotTable report. This functionality is great when you’re summarizing and analyzing data and you want to see the results of grouping data in different ways. I wrote a full-length article (“Produce Pivot Tables Programmatically,” InstantDoc ID 45502) about using VBScript to create pivot tables. That article covers the technical side of this topic in detail, and I encourage you to grab a copy of the helpful code that goes with it. However, because PivotTable reports are a key feature of this series about Excel reporting and I have more experience with them now than when I wrote the previous article, I’d like to elaborate on the subject in this article.

I’ll post the key elements of the PivotTable code here and break it up into sections as we step through it and discuss what each section of does and how it works. Listing 1 shows the code I’ll be covering. This code is an excerpt from the XLPivotTable subroutine, which is part of the HTML Application (HTA) named ExcelerateYourVBScripts.hta that I’ve been covering in this series. Listing 1’s snippet doesn’t include the code at the beginning of the subroutine that sets up the test data. You can download the full HTA in the .zip file associated with this article.

Let’s Get Started
The first line in the XLPivotTable subroutine that directly deals with the creation of my PivotTable uses the SpecialCells(11) property:

xllastcell = _ Xl.Cells.SpecialCells(11).Address 

This line of code determines the end point of the source data range that will be used in the PivotTable report. SpecialCells(11) refers to the last cell of a spreadsheet that contains data. The Address property in this code simply returns that cell’s row and column number.

Because I’m talking about the source range, I might as well mention the other piece of the range: the source data start point. In most cases, the source data start point will be cell A1. You can set the range to any range that contains data, but for the most part, you’ll typically start your range at the first cell containing data and end it with the last cell that contains data.

Next, I create a new worksheet that will contain my PivotTable report and select the worksheet that contains my source data. The PivotTable worksheet that I create will be named LockedStatus, and the source data worksheet that I’ll use as data is named Data. I store the Data worksheet in the xldata variable and use this variable to refer to the worksheet throughout the script. Having the worksheet’s name in a variable makes the name easy to change if desired, because you only need to change the name in one spot.

Here are the two lines of code needed for this step of the process:

XL.Sheets.Add.Name = "LockedStatus"XL.Sheets(xldata).Select

If you break down the first line of code, you can see that I use the Add method of the Excel Sheets property to add a worksheet. Then, I use the Name property to assign it the name LockedStatus.

The next line of code also uses the Sheets property, but in this case, the code indicates a specific sheet, xldata, which in this case refers to the Data worksheet that contains all of the source data. I set up the Data worksheet earlier in the script and filled it with test data. The Select method at the end of this statement selects the source data worksheet. Selecting the worksheet makes it the active worksheet. You’ll see in a moment that ActiveSheet is an Excel property that is referenced quite a bit throughout the script.

The next step of the process uses the PivotTable Wizard event to define the source data type, the source data, the PivotTable report’s destination, and the source worksheet or table name. The PivotTable Wizard event is similar to the Wizard object you use to create the PivotTable report interactively, except it doesn’t have a GUI. In this example, the source data type is an Excel worksheet, the source data refers to the start and end range of the source data, the PivotTable report’s destination is the LockedStatus worksheet, and the source worksheet is xldata (i.e., the Data worksheet). Here’s what that line of code looks like:

XL.ActiveSheet.PivotTableWizard _ xlDatabase,XL.Range("A1" & ":" & _ xllastcell),"LockedStatus!R1C1",xldata

Note that in this example, R1C1 refers to Row 1 Column 1 as the starting location for the PivotTable report and xlDatabase is an Excel constant that’s defined earlier in the code as Const xlDatabase = 1.

The next thing I do is lay out the source fields that I want to use in the PivotTable report. The source fields are defined by the column headers of the source data. In PivotTable reports, the field location is called the field orientation. It refers to whether the fields will appear in rows, columns, or pages or whether the fields will be used as data. The Data field is primarily where you’d put numeric data that you wanted to add, count, or perform numeric summaries on. The Row fields show detailed records in rows. The Column fields show details in columns. The Page field can be used as a filter to display only specific field values in the PivotTable report.

You’ll likely want to use rows when you want to summarize many unique data items and use columns when you have just a few unique data categories such as regions or quarterly results. For example, let’s say you have 10 users for whom you want to show summaries. If you put them in rows, you’ll see all 10 users in your PivotTable report in a list running from top to bottom. If you put them in columns, you’ll see the users listed from left to right across the PivotTable report. Be aware that if you have 300 users, for example, and orient the User field as columns, reading or analyzing your PivotTable report will be difficult because it will extend way out to the right, far beyond your immediate view.

You can use any of five types of PivotTable field orientations. When you’re defining the field orientation in your code, you can use either the number value of the field type or a constant name that you define earlier. The choice is yours. Here are the five orientation constant names and values that you can choose from:

In my example, I use just the Row and Data field orientations. The Row orientation shows UserIDs in rows, the Disabled Account status in rows next to the UserID, and the Account Lockout status in rows. The Data orientation field in my example produces counts of how many user accounts were locked.

To see the effects of changing field orientation, try changing the orientation value of the UserID field to 0, 2, and 3. I think you’ll find this experiment interesting when you see how it changes the PivotTable report. Also note that you can use the same field in both a Row orientation and a Data orientation. In my example, I use the Account Locked field in both Row and Data orientations. In doing so, I display my Account Locked status values as row items, and by using the same field as a Data-oriented field, I display counts on that field as well.

Callout A in Listing 1 shows how the field orientations are set up. I use hard-coded orientation values instead of constants to show that you’re not limited to using constant declarations.

The last two lines in callout A simply keep the Field List Box and PivotTable Toolbar from appearing with the PivotTable report. These two windows are typically left open when you create a PivotTable report manually or programmatically, so you must specifically set the two objects to False if you don’t want them. If you do want them, you can change the values to True, comment out the two lines of code, or remove the lines of code completely.

The code at callout B in Listing 1 uses the Subtotals property to produce subtotals. As you’ll see, the Subtotals property uses an array consisting of 12 elements. You must define all 12 elements as either True or False. Each of these elements represents a specific type of subtotal. I’ll define all of the elements for you shortly.

In my report, I chose not to include subtotals because I’m not really calculating any values, I just want to know how many accounts are locked. And by default, a PivotTable report will always give you at least one Total column. In this case, that column would show a count of my locked accounts because that was the field I put in the Data orientation.

To turn off all other subtotals, I have to set all of the subtotal elements to False. Otherwise, Excel will automatically produce subtotals on every field. If you want Excel to automatically produce subtotals and totals, you can remove the subtotal statements from your code altogether. If you want to comment out the code instead of removing it, note that each subtotal line of code uses the continuation character and therefore actually consists of two lines.

You might also want to experiment with the subtotals feature by setting specific elements to True. For example, you could set the fourth element to True to display an average. Here’s the list of the Subtotal array elements that you can chose to turn on or turn off:

Finally, I wanted my PivotTable report to display only locked accounts. Locked accounts have a source data value of Yes; unlocked accounts have a source data value of No. Unfortunately, filtering by a specific value works contrary to what you might assume. Instead of defining what you want to see, you need to indicate what you don’t want to see by setting the Visible property to False. To exclude unlocked accounts, I set the Visible property of the Locked Account field item to False when the item's value is equal to No, as you can see in the code that appears after callout B.

If you have a lot of different values that you need to exclude, you’d need to set up a line of code similar to the one in callout B for every value—which could be a good bit of work. But there’s an easy way to get most of this work done automatically. You guessed it: Use the Macro Recorder. Simply produce the PivotTable report without any filters first. Then, turn on the Macro Recorder. Click the drop-down arrow next to the field in the PivotTable table that you want to filter. Then, deselect all of the entries you don’t want to see, click OK, then stop the macro recording. When you open the recorded macro, you’ll see that most of the code that you’ll need to incorporate into your script is automatically generated for you.

That about wraps it up for PivotTable reports. If you’d like to see an example of how to code PivotTable reports that sum numeric data and that use multiple subtotal groupings, take a look at “Produce Pivot Tables Programmatically.” Remember, you can easily expand on this by turning on the Record a Macro feature of Excel, then creating your own PivotTable report. Most of the code you’ll need will be right there in the recorded macro. Just use this example as a base along with the macro you record, and you’ll be coding your own unique PivotTable reports in no time.