How did I determine what lines to add to the VBScript to automate Microsoft Excel? The trick is simple. Open Excel and select Tools, Macro, Record New Macro from the menu bar, then perform the operations that you want your script to automate (e.g., saving, opening, autofitting columns and rows, coloring cells). Stop the macro, then open and edit it to see the Visual Basic for Applications (VBA) code that it generates.
You can't copy this code exactly. First, constants such as xlNormal and xlSolid are already defined in VBA, so you'll need to find out their values and define them in your script. To do so, press F2 from within the Microsoft Visual Basic (VB) macro editor (or select View, Object Browser from the editor's menu bar) to open the Object Browser. Select the <globals> class from the Classes pane, then scroll through the Members of <globals> pane until you find the constant you want to identify. When you select the constant, its definition appears in the box at the bottom of the Object Browser. Second, VBA is more verbose than VBScript when a subroutine, function, or method has parameters, so you'll want to edit the code. Consider the following example, which is the record of my use of the SaveAs method:
ChDir "C:\ " ActiveWorkbook.SaveAs Filename:= _ "C:\Book2.xls", FileFormat:= _ xlNormal, Password:="", _ WriteResPassword:="", _ ReadOnlyRecommended:=False, _ CreateBackup:=False
The macro shows that Excel popped up a Save dialog box, from which I navigated to the C: root drive, then saved the spreadsheet as Book2. Because the SaveAs command includes the path, I can delete the first line of this code. The second line defines six parameters, each beginning with the parameter name, followed by ":=", then the parameter assignment. To convert this code into VBScript, I remove the parameter names and ":=" and preface the line of code with "appExcel." (or whatever variable name you're using for your Excel Application object). The result looks like the following:
appExcel.ActiveWorkbook.SaveAs _ "C:\Book2.xls",xlNormal, _ "", "", False, False
As long as I define xlNormal as a constant in my VBScript, this line of code will work. But do I truly need all those parameters? To determine how many parameters are mandatory and how many are optional, I return to the Object Browser, select the Application class (for my Excel.Application object), and select the ActiveWorkbook member (the method that I'm calling). The results pane indicates that the Application class's ActiveWorkbook property returns a Workbook object, which contains the SaveAs method. Consequently, I pick the Workbook class from the left-hand pane and select SaveAs (the method I'm using on the active workbook) from the right-hand pane. The results pane shows the SaveAs parameters. Now, I can click the question mark (?) in the Object Browser to bring up Help material about SaveAs. This material reveals that all the parameters are optional—which was already obvious in this case by looking at the results pane because all the SaveAs parameters are enclosed in square brackets (\[\]). However, I recommend that you still consult the Help material because it contains detailed information. In this case, that material tells me that all I need is the name and path of the sheet (i.e., the first parameter). So, my final VBScript code ends up as follows:
appExcel.ActiveWorkbook.SaveAs "C:\Book2.xls"
You might wonder why this line of code doesn't include the second parameter, which defines the file format (i.e., xlNormal) as I specified in the script in Listing 1 in the main article. The reason is that the second parameter sets the default file type of the file that you're saving. In this example, I'm already dealing with an Excel file, so I don't need to specify a different file type. In Listing 1, the script read in a .csv file, so if I didn't specify xlNormal, the script would save the file as a .csv file, which isn't what I want. For more detailed instructions, see "Generating Deployment Reports," March 2000, http://www.winnetmag.com, InstantDoc ID 8054.
