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:

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 = 51XL.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:

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.

Creating a standard bar chart manually. To create a standard bar chart manually, select Chart on the Insert menu to launch the Excel Chart Wizard. The Chart Wizard first lets you choose the kind of chart you want to create. Because you want to create a column chart for this example, select Column as the Chart type and Clustered Columns as the chart subtype. Clustered columns compare values across categories (more about categories shortly). Click Next to proceed to the next screen, and select the Data Range and Series. The Data Range is the range of cells containing data that will be transformed into a chart. The range for this example is

='Bar Chart'!$A$1:$D$4 

which translates to cells A1 through D4 in the Bar Chart worksheet. The wizard is pretty good at auto-selecting the data, but if you want to manually select the range, simply select the entire data range by using your mouse. (Click the first cell of the range you want to select and hold the left mouse button down as you drag to the last cell of the range you want.) For the Series selection, you can choose either rows or columns. In this example, selecting columns for the chart means that the months (January, February, and March) will be the series, which in turn makes the salespeople (John, Mae, and Al) the categories. Selecting rows as the series would do just the opposite: John, Mae, and Al would become the series, and January, February, and March would become the categories.

Because you want to see the months as a series, select the Columns radio button and click Next. Although you can define titles for the chart, categories, and y axis (in this example, the monthly sales values), you don't want titles for this chart, so click Next. The wizard then lets you determine where the Chart object should go. Because you want the chart inserted in the same worksheet as the data, select the Place chart as object in radio button and select Bar Chart from the adjacent drop-down list. When you click Finish, the new chart will appear in your Bar Chart worksheet.

Creating a standard bar chart programmatically. Creating a standard bar chart programmatically for the first time with VBScript could be quite daunting if you had to start from scratch by digging into the Excel object model or searching through the MSDN Web site. But as it turns out, you can easily complete the groundwork for this chart-creation task.

By using Excel's macro recorder, you can produce virtually all of the code you'll need to programmatically produce Excel charts-or almost any Excel entity you want to produce. (For an introduction to the process of recording a macro and converting Excel VBA macros into working VBScript code, see the section "Getting a Recorded Macro into VBScript Code," in Part 1.)

To record a macro for making this chart, select Macro on the Tools menu, choose Record New Macro, give the macro a name, and click OK. Then create the chart as described in the "Creating a standard bar chart manually" section. When you're done, click Stop Recording, which appears when you use your mouse to hover over a square blue button on the macro recording toolbar.

To view and copy the VBA code that the recorded macro created, open the macro editor by selecting Macro on the Tools menu and choosing Macros. In the Macros dialog box, select the macro you just created and click Edit. What you'll see is VBA code that can programmatically produce the same columnar chart that you created manually. But because VBA code differs slightly from VBScript code, you must transform one code into the other.

First, copy the VBA macro code, omitting the Sub and End Sub statements and the commented code (unless you think the comments might prove beneficial). Here's an example of what the recorded macro code looks like, after those omissions:

Charts.AddActiveChart.ChartType = xlColumnClusteredActiveChart.SetSourceData Source:=Sheets("Bar Chart").Range("A1:D4"), PlotBy _  :=xlColumnsActiveChart.Location Where:=xlLocationAsObject, Name:="Bar Chart"With ActiveChart  .HasTitle = False  .Axes(xlCategory, xlPrimary).HasTitle = False  .Axes(xlValue, xlPrimary).HasTitle = FalseEnd With

When you examine the code and recall the steps you took to create the chart, you'll see that the lines of code are relatively self-explanatory. The first line of code adds a chart. The second line of code defines the chart type. The third line references the data source and row or column. The fourth line indicates where you want the chart to go. The fifth line sets up title choices. Because there are no chart, category, or value titles, those properties are set to False. All you have to do now is make a few changes to convert the VBA code to VBScript code.

As you might recall from Part 1, you need to precede Excel elements such as Chart and ActiveChart with the name of your Excel application object variable followed by a period. In this case, you'd use XL. That rule doesn't apply, however, if Range is used as a property of another element, as is the case in the following line of code:

XL.ActiveChart.SetSourceData XL.Sheets("Bar Chart").Range("A1:D4"), xlColumns

You also need to remove words that end with a colon and equal sign, such as Source:=, Where:=, PlotBy:=, and Name:=. One reference in the VBA macro code that might not seem obvious at first is the PlotBy:= argument. However, as you might have guessed by its reference to xlColumns, it sets the series to either rows or columns. This element of the chart is also commonly known in Excel terminology as the legend. With those instructions in mind, the following code shows you what the converted code would look like:

XL.Charts.AddXL.ActiveChart.ChartType = xlColumnClusteredXL.ActiveChart.SetSourceData XL.Sheets("Bar Chart").Range("A1:D4"), xlColumnsXL.ActiveChart.Location xlLocationAsObject, "Bar Chart"With XL.ActiveChart  .HasTitle = False  .Axes(xlCategory).HasTitle = False  .Axes(xlValue).HasTitle = FalseEnd With

You also need to look up the values for the Excel constants and include these statements in your VBScript code:

Const xlColumnClustered = 51Const xlColumns = 2Const xlLocationAsObject = 2Const xlCategory = 1Const xlValue = 2Const xlRows = 1

Creating a custom bar chart programmatically. In Excel, you can create custom charts. The HTA's XL Charts option demonstrates how to programmatically create a built-in custom 3D bar chart called Columns with Depth. I won't discuss the code for this chart in detail because it's similar to the code for the standard bar chart. However, the custom bar chart code does have three variations worth mentioning. First, as the code at callout B in Listing 1 shows, you use the line

XL.ActiveChart.ApplyCustomType xlBuiltIn, "Columns with Depth"

to create this type of chart. Second, the custom bar chart uses rows rather than months as the series. The months appear as categories. Finally, the custom bar chart includes titles for the chart, sales quarters, and dollar ranges. The code then adds the custom bar chart to a new worksheet named "Columns with Depth."

XL Hyperlinks
The XL Hyperlinks demo code in Listing 2 shows you how to add hyperlinks to your spreadsheets. Hyperlinks are easy to create, and they add a nice touch to spreadsheets-especially if you want to refer to associated materials or related data.

The code for creating a hyperlink is pretty straightforward. First, you populate a cell with some descriptive text, such as Link to Microsoft Web Site or Link to Budget Data. Then, you select that cell and apply the ActiveSheet.Hyperlinks.Add method. This method takes four arguments. The first argument indicates the selected cell where the hyperlink will be placed. The second argument is the address the link will access. The third argument points to a worksheet location, and the last argument defines a tooltip. This tooltip is what appears if you use your mouse to briefly hover over the object.

To create a link to a Web site, you simply provide the Web site URL as the second argument and leave the third argument blank, as the code at callout A in Listing 2 shows. To create a link to a worksheet cell somewhere within your spreadsheet, you provide an empty set of double quotes as the second argument and provide the reference to the worksheet cell enclosed in double quotes as the third argument, as the code at callout B in Listing 2 shows. Cells(1, 2) refers to the cell at row 1 column 2, which is technically the same as cell B1. You set cell values by referring to their row and column, and you select a cell or range of cells by referring to columns and rows.

Chart Creation and Hyperlinking
The in-depth discussion of the XLCharts subroutine and the brief coverage of the XLHyperlinks subroutine should help you create useful reports and make them more effective by linking to associated information. And, you can do much more with the dynamic duo of VBScript scripting and Excel, as indicated by the many options in ExcelerateYourVBScripts.hta. Next month, I'll discuss a couple more of those options, including the Concatenate and Find Duplicates option.