How the Report Agent Works

The report agent, BuildReport.vbs, relies on the Microsoft Excel application object, the VBScript Regular Expression object (RegExp), and our trusty friend, the FileSystemObject. The Excel application object exposes a rich programming environment that scripts can leverage. (For a brief overview of scripting with the Excel application object, see "Using Excel Objects to Manipulate a Spreadsheet," April 2000, http://www.winscriptingsolutions.com, InstantDoc ID 8387.)

BuildReport.vbs starts by defining some constants used in the script and initializing some global variables. Then, the Excel application object creates a workbook. Next, the script calls the PrepareWorkSheet subroutine. This subroutine creates 14 worksheets in the workbook, names each worksheet, creates column labels, formats some columns, and saves the workbook as InvReport.xls.

The FileSystemObject then uses the Files collection and a For Each statement to iterate through all the files in the hardware-collection directory. To reduce the number of files that the FileSystemObject must iterate, put nothing in this directory other than the files that the inventory agent collects.

Inside the For Each loop, the FileSystemObject uses the OpenTextFile method to open two text-stream objects for each inventory file. (Opening two text streams to the same data source is necessary because of how the script uses the inventory files.) The script reads the first text-stream object, which I call TextStream1, line by line and writes data from the object to the spreadsheet. The script opens the second text-stream object, which I call TextStream2, and reads it completely, using the FileSystemObject's ReadAll method. The Regular Expression object (RegExp) uses TextStream2 to determine whether an inventory file contains a particular data section.

Next, the script calls the GenReport subroutine—one of the most prevalent calls in the script. This subroutine relies on several functions to work its magic. GenReport requires the name of a section head, the name of a TextStream2 object, a unique label to search for in the inventory file, and an array containing the values that the script should write to the columns in InvReport.xls.

Using the specified section head, GenReport calls the MoveToSectionHead subroutine to determine where in the text file the section head occurs. The MoveToSectionHead subroutine uses RegExp to locate the section head in TextStream2, then uses the FileSystem-Object's ReadLine method to move forward to the matching section in TextStream1. GenReport then calls the FindValues function to determine how many unique labels appear in that section of the inventory file, and thus how many lines the script needs to write to a particular worksheet.

Next, the InsertValues subroutine writes the collected properties from the inventory file line by line to the worksheet. InsertValues uses Case statements to determine which worksheet to make active, to write data to the worksheet, and to format cells. Each Case statement relies on the FillCells subroutine to write values to each cell in the worksheet.