Create Excel Reports the VBScript Way, Part 2

In "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 "Create Excel Reports the VBScript Way, Part 1," February 2007, InstantDoc ID 94768, I showed you how to increase the efficiency of your VBScript scripting with Excel by reusing segments of code that perform common functions. I also introduced an HTML Application (HTA)-ExcelerateYourVBScripts.hta-that contains the reusable code segments. In this article, I delve into the two of the subroutines that ExcelerateYourVBScripts.hta offers: XLCharts and XLHyperlinks.

When you launch the HTA, you'll see the following list of options:

• XL Charts
• XL Hyperlinks
• Concatenate and Find Duplicates
• XL Subtotals
• XL AutoFilter
• XL Formula to Get Filename From Full Path
• XL Misc
• XL PivotTable
• Import with Lookup, Match & Index
• XL Sum

In this article, I'll first discuss XL Charts in depth, taking you step-by-step through the process of creating bar charts, both manually and programmatically. I'll then discuss XL Hyperlinks, which you can use to link associated material to your Excel reports. However, I won't go into as much detail because using the XLHyperlinks subroutine is fairly straightforward.

XL Charts
Let's begin with the XL Charts demo code, which Listing 1 shows. The XLCharts subroutine first initializes several constants that represent the Excel constants that will be needed during the chart creation process. Two key constants are xlRows and xlColumns. As you might recall from Part 1, Excel macro code is a form of Visual Basic (VB) called Visual Basic for Applications (VBA). VBA for Excel has an overwhelming number of built-in constants, but VBScript doesn't have any knowledge of these constants. So, you must reference the constants and their actual values in VBScript code. You can either declare the constants and their values or replace the Excel constant with the appropriate value. For example, here's a line of VBA code from the XL Chart macro:

ActiveChart.ChartType = xlColumnClustered

To convert this VBA code to VBScript code, you can use the following two lines of code:

Const xlColumnClustered = 51
XL.ActiveChart.ChartType = xlColumnClustered

Alternatively, you can enter the actual value of the constant, as in the following single line of code:

XL.ActiveChart.ChartType = 51

Although the second option uses less code, the single line doesn't indicate which chart type (clustered columns) it designates. Either way, you still need to look up the values of all the Excel constants generated in the Excel macro and refer to them in your VBScript code. If you have Excel 2003 or Excel XP, you can use the following steps to quickly look up constants' values:

1. Open Excel.
2. Press Alt+F11 to open the Visual Basic Editor.
3. Press F2 to open the Object Browser.
4. Enter the constant name in the text box next to the binocular icon, then click that icon.

You can then copy the value in the Object Browser window and paste it directly into your script. Note that in earlier versions of Excel, the constants' values might not appear. If that's the case, you can use the GetConstants.vbs script, which lists the Excel constants' values in a spreadsheet. You'll find GetConstants.vbs and ExcelerateYourVBScripts.hta in the downloadable .zip file.

As I mentioned previously, many Excel constants are involved in coding spreadsheets. Because of the number of subroutines and the amount of code that ExcelerateYourVBScripts.hta encompasses, I won't be able to cover what each constant represents. As you'll see when you explore the code, however, many of the constants are fairly descriptive. You can find more information about specific Excel constants at the MSDN Web site. You'll find a good beginning reference at http://msdn2.microsoft.com/en-us/library/aa221100(office.11).aspx.

After the declaring the constants, the XLCharts subroutine opens Excel, creates a workbook, and adds a worksheet, as the code at callout A in Listing 1 shows. The following line names the worksheet "Bar Chart":

XL.Sheets.Add.name = "Bar Chart" 

You'll see this name as a tab name at the bottom of the spreadsheet. I prefer naming my worksheets rather than keeping the default names (e.g., Sheet1, Sheet2) for easier reference.

The next section of code in callout A creates some basic sample data, which will be used to produce a standard bar chart and custom 3D bar chart. This code creates three header columns that specify months and three rows that specify the names of salespeople and their corresponding monthly sales values. Before I explain how the HTA programmatically creates the two types of bar charts from this data, let's first look at how you'd manually create a standard bar chart.