eXLerate Your Scripts

In the late 1990s, when I first began using VBScript scripts to produce Windows NT administration reports for management, I had to work hard just to get the output into a presentable form. What began as echoed output to DOS was eventually transformed from a text file into a more appealing document, such as a Microsoft Word or Microsoft Excel file.

To provide consistency in my reports, I soon settled on Excel. I hadn’t yet discovered that I could create Excel spreadsheets using VBScript scripts, but I knew I could embed tabs into my output and import tab-delimited files into Excel. From that point, sorting columns, calculating totals, and even adding charts became relatively easy tasks.

The managers in my organization liked Excel. Some of the more Excel-savvy managers would add pivot tables, graphs, and pie charts to the existing spreadsheet and send them to their managers. They could add their own analysis, fine-tuning, and flair to the overall presentation. So began my deep involvement with scripting and Excel.

The floodgates opened, however, when I discovered I could create an instance of Excel and output directly to rows and columns. As it turned out, creating the Excel instance was actually an easy process. I’ll describe the steps of that process, briefly take you through them (covering some basic methods and properties as I go), share selected Excel features that I rely on, and offer some Excel scripting tips.

Creating an Excel Instance

The steps for creating an instance of an Excel application are straightforward:
• Create an object (or instance) of an Excel application.
• Add a new empty workbook to the workbooks collection.
• Make the application visible (optional).
• Assign values to specific rows and columns.

Creating an Excel instance. Creating the Excel object is pretty straightforward. You simply use the CreateObject function to create an instance of the Excel.Application object and assign that instance with a variable name. The syntax for creating this type of object is

Set objectvariable = CreateObject(“Excel.Application”)

where objectvariable is the desired variable name. For example, in the code

Set XL = CreateObject("Excel.Application")

I set the instance of the Excel application to the variable name of XL. Note that the XL variable is used in subsequent lines of code to refer to the Excel.Application object's properties such as Workbooks, Visible, and Cells.

Creating a workbook. To create a new workbook, you use Excel’s Workbooks object and the Add method. Without a workbook, an instance of the Excel application will be running but you’ll have no document to work with or populate. The Workbooks property represents a collection of all open workbooks. XL.Workbooks.Add adds a new empty workbook to the Workbooks collection.

When you add a new workbook, it automatically contains three default worksheets: Sheet1, Sheet2, and Sheet3. You’ll write your data to these worksheets. After you have a workbook, you can start populating your spreadsheet.

If you want to add a new worksheet to your Excel workbook and give it a descriptive name, you can use the Sheets object, the Add method, and the Name property. For example, if you want to add a worksheet named FormatDate, you'd use the code:

XL.Sheets.Add.name = "FormatDate"

The number of worksheets you can create in a workbook is limited only by the amount of memory you have available. I’ve created workbooks with hundreds of worksheets when I’ve gathered data for every server in a domain as well as workbooks with thousands of worksheets when I’ve documented user information from Active Directory (AD).

Making Excel visible. You don’t need to make an Excel application visible. You can perform any actions you usually perform without it being visible. However, if you want to see your spreadsheet come to life and watch the cells populate as your script runs, make Excel visible by setting the Excel.Application object’s Visible property to True. So, in this example, you'd use the code

XL.Visible = True

In some cases, you might want to wait until you’ve completed your work on the spreadsheet to make it visible. And if you prefer to always have Excel run behind the scenes, don’t set the Visible property at all or set it to False.

Assigning values to specific rows and columns. After a worksheet is available, you can start populating cells by using the Cells object. The Cells object takes two arguments—row and column—that refer to a specific cell location. Simply place the row and column numbers (separated with a comma) within parentheses and assign a value to the cell with the Value property. For example, the following code fills in cell A1 with the value of 38777.25:

XL.Cells(1,1).Value = 38777.25

The following code shows all the steps in creating an Excel application instance:

Set XL = CreateObject("Excel.Application")XL.Workbooks.AddXL.Visible = TrueXL.Cells(1,1).Value = "Name" : XL.Cells(1,2).Value = "UserID"XL.Cells(2,1).Value = "Elizabeth" : XL.Cells(2,2).Value = "04567"

From this point on, you simply increment the row and column numbers and assign values to the cells at those locations as you cycle though your script. For example, in a script that lists the users in a group, you would initialize row and column variables and increment those variables as you loop through the names to get the names into subsequent rows.

You’re now ready to start creating basic Excel spreadsheets with VBScript. However, you won’t want to stop there. Excel is a great reporting tool with a wide range of functionality, as the following section demonstrates. You’ll find yourself producing reports that are useful not only for the information they contain but also for how you can organize and present data.

Excel Functionality

I consistently use a number of simple Excel formatting features to emphasize key areas of reports. I describe several of the features that I think you’ll find most useful.

Colorizing worksheet tabs. I often colorize my worksheet tabs. Sometimes I color them just so they stand out, but for the most part I color the tabs of worksheets that need attention.

First, I test for a certain condition in my script. If the condition is true—if, for example, an account in my listing has expired—I set the tab color to red. That way, when I have multiple worksheet tabs, the red tabs are easy to spot.

I haven’t found a quick reference to Excel’s color index through Microsoft. For an accurate list of colors, you can go to my article “Add a Little Color to your VBScript Reports,” November 2005, InstantDoc ID 47800. About 56 colors are available for your use. For example, the following code sets the worksheet tab color to teal by setting the ColorIndex property to 14:

XL.ActiveWorkbook.Sheets("FormatDate").Tab.ColorIndex = 14

The first part of the code simply specifies the worksheet tab on which the color will be set—in this case, the FormatDate worksheet.

Formatting columns. In the example of assigning values to rows and columns that I offered previously, I set the cell value to 38777.25, which is the actual result of a value one of my scripts returned. In fact, it represents the number of days since January 1, 1900. As you can imagine, such a number wouldn’t be very useful in a report. Some formatting is required to turn that column of cells into date and time values. To accomplish the change, first select the column by using the Columns property. For example, if you want to select the C column, you'd use the code:

XL.Columns("C:C").Select

With the column selected, use the NumberFormat property of the Selection object to set the format as a date value:

XL.Selection.NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"

After the formatting is applied, the column contains values such as 3/1/06 6:00 AM. Note that I derived this piece of code by using a recorded macro. At first glance, the code might look a bit unusual. When I see such code, I usually try it without the unusual pieces (e.g., [$-409], ;@). Those pieces can, in fact, be completely removed from the format string. Although 409 has a correlation to a three-digit English Locale Code, it isn’t required in the format string. As for the ;@ piece at the end of the code, I know only that it isn’t required. All you really need for the format string is "m/d/yy h:mm AM/PM". So, you can change the code to

XL.Selection.NumberFormat = "m/d/yy h:mm AM/PM"

Formatting numeric values. I’ll show you a couple more examples of formatting numeric values. The first example changes a numeric value with three decimal places into a value with just two decimal places. It also formats the number to include commas if the number is large enough to require them. The following code formats a number with commas and two decimal places:

XL.Cells(1,1).Value = 12345678.123XL.Range("A1").SelectXL.Selection.NumberFormat = "#,##0.00"

Note that the format structure contains within double quotes number symbols (#), a comma, and 0.00. The two zeroes after the decimal point will restrict the decimal value to two places, and the comma will automatically insert commas in the appropriate places.

This code

XL.Selection.NumberFormat = "#,##0"

shows you how to format a column so that the numbers still have commas if they’re large enough to require them, but the cell containing the numbers will display no decimal values at all. (Note that only the formatting changes; the actual values don’t change.)

Basic Elements of Excel Scripting

Let's go through some basic elements of scripting with Excel. You’ll likely incorporate these elements into most of the Excel scripts that you'll write.

Selecting. I usually select the topmost cell to return to after my main work is done so that I’m at the top of my worksheet when the script is complete. That way, I’m not left staring at a highlighted (or selected) column or row or at the bottom of the worksheet.

Selecting a specific cell is easy. You use the Range property with the cell identifier in both double quotes and parentheses followed by the Select method:

XL.Range("A1").Select

If you create multiple worksheets in your script and want to return to a specific worksheet when your processing is done, you can do so. You can use either the Activate or Select method, as the following syntax examples show:

XL.sheets("Sheet1").Activate

or

XL.sheets("FormatDate").Select

Freezing the panes. In almost every Excel VBSscript that I write, I “freeze the panes,” as Excels calls it. If the worksheet has headings, I like to freeze the panes so that if I scroll down, the headings are always visible. To achieve header visibility, select the second row of the active worksheet and set the FreezePanes property of the ActiveWindow to True, as the following code shows:

XL.Rows("2:2").SelectXL.ActiveWindow.FreezePanes = TrueXL.Range("A1").Select

If you don’t select “A1” after freezing the panes, the second row will remain highlighted.

Using AutoFit. I always use AutoFit because it automatically adjusts the columns in the active worksheet so that you can see all of the data in each column. If you don’t use AutoFit, you might see only portions of what’s actually contained in a cell. This problem occurs because Excel doesn’t automatically expand a column if the data is wider than the default column size. In the following sample code, I use the EntireColumn property and apply the AutoFit method to automatically adjust all of the worksheet columns:

XL.Cells.EntireColumn.AutoFit

Moving worksheets. I sometimes create a summary worksheet after I’ve finished my other worksheets, and I like to move it to the front of the workbook, where it’s readily available. Should you ever want to move a specific worksheet to a specific position, you can easily do so by using the Move method followed by the position to which you want to move the worksheet. The following code moves the FormatDate worksheet to the first tab position in a workbook:

XL.Sheets("FormatDate").Move xl.Sheets(1)

Copying data between worksheets. Being able to copy data from one worksheet and paste it into another is useful. I often copy and paste when I want to keep my original data intact. I create a copy on which I can perform various operations. To copy a range of cells, you select your starting and ending points and use the Copy method. To paste the selection into another worksheet you select or create the destination worksheet and use the Paste method of the ActiveSheet object. The following segment of code shows you how to copy and paste:

XL.Range("A1").SelectXL.Range(XL.Selection, XL.ActiveCell.SpecialCells(11)).SelectXL.Selection.CopyXL.Sheets.Add.name = "PasteSetFontAndColor"XL.ActiveSheet.Paste

The first line of code selects the starting point of the area to be copied (in this case, cell “A1”). Note that because you’ve selected an area, you can now use the Selection property to refer to what’s selected, as the second line of code shows. The second line selects the start and end range of the area to be copied. This approach is similar to highlighting an area before you copy it. SpecialCells(11) represents the last cell in a spreadsheet that contains data. The third line simply uses the Copy method to copy the selection to the Clipboard. The fourth line creates the new worksheet into which the copy will be pasted. The last line uses the Paste method to paste the Clipboard contents into the new worksheet.

Formatting with font, style, and color. Formatting a cell with a particular font, style, and color can also improve your reports. The following segment of code sets the font of a selected cell to Red, Size 8, Courier New, Italic (the properties and the code are self-explanatory):

XL.Range("A1").SelectWith XL.Selection.Font  .Name = "Courier New"  .FontStyle = "Italic"  .Size = 8  .ColorIndex = 3End With

Sizing columns. You might also need to size certain columns because of their extreme width or just want to size them because you want to display as many columns of your report as possible. To size a column, simply use the ColumnWidth property of the Columns object and set the width to a specific number. The following syntax sets the width of Column A to 15:

XL.Columns("A:A").ColumnWidth = 15.00

Setting the background color. Another way to emphasize a specific area of a report is to set the interior (or background) color for a cell or an entire row. The following segment of code sets the background color:

XL.Cells(2,2).interior.color = rgb(255,240,210) XL.Rows("2:2").font.color = rgb(255,0,0) XL.Rows("3:3").interior.color = rgb(255,0,255) 

The first line of code sets the interior color of Cells(2,2)—Row 2, Column B—to a pale yellow. The second line sets the entire second row’s font color to red. The last line sets the entire third row’s interior color to a light purple. You’ll notice I use the RGB (Red Green Blue) function to create my colors. You can also use hex values. (My article “Add a Little Color to Your World,” which I mentioned previously, includes any additional details you’ll need to adding color to your Excel reports.)

Sizing the Excel window. On occasion, you might also want to size the Excel application window and place it in a specific location on your screen. You can size the Excel application by setting the WindowState property to Normal, Minimized, or Maximized. You can indicate where the window should be placed by setting the Left, Top, Width, and Height properties as the following segment of code indicates (the numbers represent pixel values):

XL.Application.WindowState = xlNormalXL.Application.Left = 1XL.Application.Top = 115XL.Application.Width = 835XL.Application.Height = 350

The first line of code sets the WindowState property to Normal and sets size and placement of the Excel application window as they were the previous time Excel was opened. Usually, you would use this line of code by itself without additional lines of code for resizing. However, the additional lines of code let you see how a window can be quickly moved and resized. The Left and Top properties set position and width. The Width and Height properties set size.

The first line of code places the left edge of the Excel application window to the leftmost position of your screen. Pixel 1 is as far to the left as you can go. If you were to increase the number, you would see the application window move from left to right. The second line sets the top edge of the Excel application window 115 pixels down from the top of your screen. The third line defines the width of the application window at 835 pixels. The last line sets the Excel applications window’s height at 350 pixels.

If you intend to set the window’s state, you’ll need to set up WindowState constants. The three window states and their values are

Const xlNormal = -4143Const xlMaximized = -4137Const xlMinimized = -4140

The names of the constants, such as xlNormal, can be any unreserved string of characters. However, it’s a good practice to make the names descriptive. The value -4143, for example, refers specifically to a windows state of Normal. If you want to have your spreadsheet minimized during a process or after a process completes, you can set the windows state to Minimized by setting the application WindowState property value to -4140.

Splitting a workshop window. One last feature you might want to use splits a worksheet window. Although I don’t use this feature often, I occasionally want to see more than one area of a spreadsheet at one time. Splitting lets me to do so.

To accomplish this task, use the Excel.Application object's ActiveWindow property and set its SplitColumn and SplitRow properties accordingly. The numbers in the following segment of code represent the column and row numbers where the splits will occur:

With XL.ActiveWindow  .SplitColumn = 13  .SplitRow = 10End With

Once you get started, I think you’ll find, as I have, that scripting Excel reports is worthwhile. When you combine Excel with VBScript, you have enormous flexibility and functionality at your fingertips. You can easily create your own full-blown applications in record-breaking time—and people will notice.