Microsoft Excel is a useful tool that you can use in your scripts to produce customized and even colorful reports. Although many people are familiar with how to use Excel, most people aren’t too familiar with how to programmatically create and use spreadsheets. To make matters worse, the Excel object model isn’t the most straightforward, so the learning curve can look like Mt. Everest.
To help turn this mountain into a molehill that you can more easily scale, I’ve compiled a list of articles we’ve published on how to use Excel in VBScript code. Because Excel’s functionality varies between the different versions, I’ve broken down the articles into two groups: articles in which the code accesses Excel 2003 and Excel XP and articles in which the code accesses Excel 2000. However, although the functionality differs, the core concepts are similar, so checking out the articles in both groups might prove beneficial.
Excel 2003 and Excel XP Resources
If you have Excel 2003 or Excel XP and you’re a novice when it comes to programmatically using it, the best article to start out with is "eXLerate Your Scripts." This article provides the basics of how to use VBScript scripts to produce Excel reports. It discusses how to create an Excel object (or instance), add a workbook, make the application visible, assign values to rows and columns, select a specific cell, move worksheets, and copy data between worksheets. You’ll also learn how to size the Excel window, split a workshop window, size columns, freeze a pane, and use AutoFit to adjust columns in active worksheets. The article also provides a brief introduction on how to customize Excel reports through such measures as formatting cells, rows, columns, worksheet tabs, and numeric values.
For a more detailed look at how to use VBScript scripts to produce customized Excel reports, check out Scripting Pro VIP’s five-part series “Create Excel Reports the VBScript Way.” The series is based on a HTML Application (HTA) named ExcelerateYourVBScripts.hta. This HTA provides reusable VBScript code snippets that automate Excel functions as well as demonstrates those functions. Here are the articles in the series:
- "Create Excel Reports the VBScript Way, Part 1" introduces the HTA. It also discusses how the HTA’s code snippets were created using recorded macros in case you want create your own snippets.
- "Create Excel Reports the VBScript Way, Part 2" walks you through a code snippet in ExcelerateYourVBScripts.hta that programmatically creates standard and customized bar charts. The article also provides a code snippet that programmatically adds hyperlinks to spreadsheets.
- "Create Excel Reports the VBScript Way, Part 3" discusses how to write VBScript scripts that concatenate items, find duplicates, and filter results in a spreadsheet. As part of this discussion, the article covers several commonly used Excel features, including conditional formatting, inserting columns, pasting formats, the CountIF function, and relative cell referencing.
- "Create Excel Reports the VBScript Way, Part 4" highlights two code snippets that let you import text files and extract elements from pathnames. The first code snippet uses the Excel Query Tables class to import delimited and fixed-length text files into a spreadsheet. The second code snippet demonstrates how to create Excel formulas that let you extract from a full pathname (e.g., C:\samples\vbscript\excel\demo1.vbs) the filename (e.g., demo1.vbs) or path (C:\samples\vbscript\excel).
- "Create Excel Reports the VBScript Way, Part 5" walks you through how to programmatically create PivotTable reports so that you can group similar records together, produce group totals, filter specific data values, and more.
Another HTA than can help you learn how to use Excel in your VBScript scripts is AddColor.hta, which is discussed in "Add a Little Color to Your World." This HTA runs demos that show you how to spice up Excel 2003, Microsoft Word, and HTA reports with some color. For example, the Row Color Demo illustrates how you can change the color of a row's contents in a spreadsheet to highlight important data or add color to the title of an Excel report. This article also provides a spreadsheet (ExcelRGB.xls) containing the 46 colors that Excel 2003 can display.
To see how useful adding color to worksheets can be, check out "Script Scrutinizes SNMP Servers' Security Status." This article presents a script that uses Windows Management Instrumentation (WMI) to check Windows servers for SNMP security vulnerabilities. For each server it checks, ServerAlerts.vbs reports its findings in an Excel worksheet whose tab is colorized to denote the server's status. An additional Excel worksheet includes hyperlinks to the server worksheets; the hyperlinks are color coded and grouped so you can easily spot problematic servers.
Excel 2000 Resources
If you’re a novice when it comes to programmatically using Excel 2000 in VBScript scripts, the best place to start is "Generating Deployment Reports." This article covers how to automate Microsoft Excel 2000 to dynamically create a spreadsheet of statistics for Computer objects in AD. It also discusses how to create a pie chart. Although this article is published by Windows IT Pro, I’ve opened it up for public viewing so you don’t need to subscribe to that publication to read it.
"Using WSH with ADSI to Create Excel Spreadsheets for Debugging" demonstrates how you can write script results to an Excel 2000 spreadsheet. The script connects to an Active Directory (AD) object, walks through the property cache to retrieve and examine the object’s properties, then writes the desired properties to a spreadsheet.
If want your scripts to create PivotTable reports and trend charts, you’ll want to read "Automating Excel to Create Trend Charts" and "Produce Pivot Tables Programmatically." "Automating Excel to Create Trend Charts"describes how to create a script that regularly gathers data, updates an Excel spreadsheet with that data, and creates charts showing trends. "Produce Pivot Tables Programmatically" provides two scripts that demonstrate how to code PivotTable reports that sum numeric data and that use multiple subtotal groupings. PivotTable.vbs creates a pivot table that shows a quarterly breakdown of three roommates and their contributions toward living expenses. PivotTable2.vbs constructs a pivot table for locked-out accounts.
I hope you find these articles useful.
