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: . . .
Register
