Create Excel Reports the VBScript Way, Part 1

Downloads
94768.zip

In my article “eXLerate Your Scripts,” January 2007, InstantDoc ID 94623, I introduced you to the basics of scripting Microsoft Excel reports. Combining Excel with VBScript scripts puts enormous flexibility and functionality at your fingertips. In that article, I outlined the steps for creating an instance of Excel, showed you how to use selected Excel features that I rely on, and offered you some Excel scripting tips. Now, in this five-article series, I'm going to give you a detailed tour of how to use VBScript scripts to produce Excel reports.

Over the years, I’ve written numerous VBScript scripts that used Excel for different purposes. I’ve found myself searching for segments of code from previously written scripts to reuse in my new scripts. Although it’s efficient to reuse pieces of code, searching for the code segments can be time-consuming. To improve the search process, I created a compendium of reusable code segments for quick reference.

After organizing some not-so-quick references to these code segments, I decided to put together an HTML Application (HTA) that contains not only the code segments for quick reference but also functional demonstrations of the code. After I completed ExcelerateYourVBScripts.hta, I realized that this HTA was a great way to demonstrate how to use VBScript scripts to produce Excel reports.

This article and the HTA, which you can download in the .zip file associated with this article, cover many fundamental routines that VBScript scripts and Excel perform. After I present some brief background information, I’ll review the routines for which I’ve developed reusable code segments and show you how to get a recorded macro into VBScript code. I hope that the information, references, demos, and code that I offer will help those of you who currently use VBScript scripts to produce Excel reports to create them more efficiently—and inspire those who haven’t yet tried this technique to do so.

The HTA and Its Routines
ExcelerateYourVBScripts.hta demonstrates routines that perform many functions, including how to

add color
auto-fill formulas
concatenate cells
create pivot tables
find duplicates
format cells, rows, and columns
format cells conditionally
get subtotals
import delimited text and fixed-length files
perform lookups and matches
produce charts
sum columns
use formulas
use AutoFilter to filter reports by criteria

This HTA lets you easily copy the demo code. The application offers you the option of exposing or hiding the code; exposing the code displays the area from which you can copy it for your use. Note that I wrote ExcelerateYourVBScripts.hta for Excel 2003 and Excel XP. It might not work with earlier versions of Excel because of differences in functionality.

For the most part, these segments of code began as recorded Excel macros. Excel macros are a form of Visual Basic (VB) called Visual Basic for Applications (VBA). To record a macro, you open a new or existing spreadsheet. You then choose Tools, Macro, Record Macro. Name the macro, perform the task you want to incorporate into your code, then stop recording the macro. After you’ve finished recording, you can open the recorded macro and access the code that performs the task. Simply chose Tools, Macro, Macros, select the name of the macro you created, and click Edit. What you’ll see is the VBA code that performs the task you just completed.

Unfortunately, you can’t simply copy that VBA code into a VBScript script and expect it to work. But you do have the fundamental groundwork to create the code you need for your script. And because the code is VB at its core, it’s usually easy to translate it into VBScript code. In my experience, about 75 percent of the time, you need only to add the name of your Excel application object variable followed by a period to the beginning of each Excel element. For example, if the variable representing your Excel application is named XL and you're using Excel's Range object, you'd specify XL.Range. About 15 percent of the time, you might need to visit Microsoft Developer Network (MSDN) to review the structure of an Excel property, element, method, or argument. (I’ll say more about the remaining 10 percent of the time later.)

In VBA, you use keywords to declare specific properties and arguments. Let’s say a method has five arguments. When you look at the VBA code, you might see only three rather than all five arguments in the recorded macro because the method didn’t use the other two.

If you were to write a simple VBA sort on column A only, for example, you’d see something like the following in the macro code:

Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False

It’s a different story with VBScript code. In VBScript scripts, you must delimit unused arguments with commas unless the arguments occur after the last required argument. The VBScript Sort method offers a prime example of the need to delimit unused arguments. The Sort method uses the following expression:

Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase,
        Orientation, SortMethod, DataOption1, DataOption2, DataOption3).

You can see that the VBA Sort method uses only 5 of the 15 VBScript properties. If you were to write the same simple sort that you see in VBA for use in a VBScript script, you’d need to delimit with commas all of the properties that fall between any of the used properties. The VBScript code would look like the following:

XL.Selection.Sort XL.Range("A1"),2,,,,,,1,1,False

Notice that I haven’t used the last five properties: Orientation, SortMethod, DataOption1, DataOption2, and DataOption3. However, because I don’t use them and because they don’t occur between any used properties, I don’t have to delimit them.

About 10 percent of the time, you’ll need to find a value for an Excel constant. Excel constants usually start with xl (e.g., xlSum, xlAscending, xlDescending). You must declare constants and their values in your VBScript script if you want to refer to them by name. You’ll find that getting constant values is straightforward.

With a spreadsheet open, press Alt F11 to bring up Visual Basic Editor. Press F2 to get the Object Browser and press Ctrl+G to get the Immediate pane. Let's say that you want to find the value for the xlLastCell constant. To use the Object Browser, enter xlLastCell in the text box next to the binocular icon, then click the binocular icon to launch the search. You’ll see the Const xlLastCell = 11 result in the lower left pane of the Object Browser window. Alternatively, to use the Immediate pane to get a constant value, type MsgBox xlLastCell in the pane, then press Enter to produce a pop-up that shows you the value of xlLastCell.

On rare occasions, you might need to do some heavier digging and more trial-and-error testing, as I did when I developed the VBScript code to create pivot tables. In that case, the recorded macro didn’t even come close to giving me the syntax I needed to complete the program. (For more information about the digging that’s occasionally necessary, see my article "Produce Pivot Tables Programmatically," April 2005, InstantDoc ID 45502.)

Getting a Recorded Macro into VBScript Code
To familiarize you with the process, I’ll show you how to take a recorded macro and get it into working VBScript code. As an example, I’ll use one of the routines I use most often in my Excel reports: the Sort method. I’ll cover only what you’ll need to know to construct a working VBScript script Excel Sort statement. (For more detail about translating a recorded macro into VBScript code, see “Formatting the Reports” by Alistair B. Lowe-Norris, May 2003, InstantDoc ID 38402.)

The Sort method has 15 arguments. Of them, I typically use between six and nine arguments for common sorts. The last arguments aren’t commonly used and can be omitted from the statement completely. The Type argument is used for pivot tables only, but because it falls between other arguments that are used more often, it must have a comma delimiting it. The Sort method arguments are

Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase,
        Orientation, SortMethod, DataOption1, DataOption2, DataOption3)

The following list explains each of the Sort method arguments:

Key1 represents the first range or column to be sorted.
Order1 will be either Ascending (1) or Descending (2).
Key2 represents the second range or column (if any) to be sorted.
Type is used for sorting pivot tables only; I don’t know why it’s placed between the second key and the second order arguments.
Order2 represents the sort order of the second key and will be Ascending (1) or Descending (2).
Key3 and Order3 are used to sort on a third column.
Header indicates whether headers are present: (xlYes value of 1) means headers are present, (xlNo value of 2) indicates headers aren’t present, and (xlGuess value of 3) leaves it to Excel to decide whether headers are present.
OrderCustom is used only if you want to set up custom sorts. Otherwise, you enter 1 for normal sort order. To learn about custom sorts, see the Microsoft article “XL2000: Sample Macro to Sort List Based on Custom Sort Order” at http://support.microsoft.com/?kbid=213625.
MatchCase is True for case-sensitive sorts, False for case-insensitive sorts.
Orientation represents the sort orientation. The two values are xlTopToBottom (the default) and xlLeftToRight.
SortMethod can be either xlPinYin (default) or xlStroke. These choices are related to sorting Chinese characters (xlStroke refers to the number of strokes in each character; xlPinYin is a phonetic Chinese sort).
DataOption1 specifies how to sort text in Key1. Excel Constants can be either xlSortNormal (the default) or xlSortTextAsNumbers.
DataOption2 specifies how to sort text in Key2, either xlSortNormal or xlSortTextAsNumbers.
DataOption3 specifies how to sort text in Key3, either xlSortNormal or xlSortTextAsNumbers.

Notice that if you’re sorting on one column only, you leave a comma delimiter for Key2, Order2, Key3, and Order3.

Here’s an example of the VBA code that a recorded macro produces to perform a sort:

Range("A1:C6").Sort Key1:=Range("B2"), Order1:=xlDescending, Key2:=Range( _
        "A2"), Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending, _
        Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:= _
        xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
        DataOption3:=xlSortNormal

Notice that no Type keyword (or argument) appears between Key2 and Order2, nor is there a SortMethod argument.

Here’s translated VBScript code that performs the same sort:

XL.Range("A:C").Select
XL.Selection.Sort XL.Range("B2"),2,XL.Range("A2"),,1,XL.Range("C2"),1,1,1,False

The following code offers an alternative approach. It shows a different way to construct the statement and eliminates one line of code.

XL.Range("A:C").Sort XL.Range("B2"),2,XL.Range("A2"),,1,XL.Range("C2"),1,1,1,False

The following tips apply whenever you convert a macro into a script:

Any segment of a recorded macro that contains “keyword:=” (e.g., Key1:=, Order1:=, MatchCase:=) must be removed when translating to VBScript code.
Excel constants (e.g., xlDescending, xlYes, xlAscending, xlTopToBottom) can be left in place, but you must declare them as constants with the appropriate values. You can also remove the Excel constants and substitute the appropriate values.
Any occurrences of the word Range must be preceded with the name of the Excel object variable you created (as in the lines of code just given). That rule doesn't apply, however, if Range is used as a property of another element, as is the case in this code:
```
XL.ActiveChart.SetSourceData XL.Sheets("Bar Chart").Range("A1:D4"), xlColumns
```

You now have the basics that you’ll need to create Excel reports and to convert Excel VBA macros into VBScript code. To run ExcelerateYourVBScripts.hta, simply launch the HTA and select the Excel Demo from the list of items available in the drop-down list. If you want to see the code used in the routines, simply click ShowAllCode. You can copy code from this text area to start creating your own Excel report script or incorporate the code into one of your existing scripts.

Stayed Tuned for More
In Part 2 of this series, I’ll delve deeper into the code that you'll find in ExcelerateYourVBScripts.hta. I'll show how some of the routines work and discuss a variety of Excel functions and methods.

Comments

Plain text