Printing from Scripts

Many scripts redirect their text or HTML output to a text file, and some scripts even use automation to redirect their output to Microsoft Excel. These approaches are fine if you're reading the output on screen or emailing it to someone, but if you want to print the results, you need to open the file, then choose the Print option. If you typically want to print a particular script's results, you might prefer to have the script print the file. Let's look at the several ways you can print from within your scripts.

Simple Does It
The simplest way to print from a script is to use Notepad's print functionality. When you use the /p switch, Notepad prints the file to your default printer. For example, the following code causes Notepad to print C:mylog.txt to your default printer:

Notepad /p c:mylog.txt

Listing 1, page 2, shows a snippet of VBScript code that uses this command. This code should work fine as long as your PATH environmental variable includes the directory that contains Notepad; otherwise, you need to specify Notepad's full path.

The drawback to this approach is that it opens an instance of Notepad that remains on the screen. Consequently, you don't want to use this solution in a script that runs unattended. For such jobs, you can use one of the other options I describe.

Using CopyFile and UNCs
For unattended jobs, you can have the script send a file that it has created to a port on your computer that maps to the printer you want to use. Listing 2, page 2, shows how to use the FileSystemObject object's CopyFile method to copy the file to a printer represented by a Universal Naming Convention (UNC) path. You'll need to edit the strFilePath and strPrinterUNC variables in the code to specify the file to print and the printer to use on your network, respectively. Make sure that the file to be printed is formatted; the printer logic will truncate any lines that are too long to print on the page.

To find the correct name to use for a printer on your network, you can use the Scriptomatic tool (scriptomatic.exe, available at http://www.microsoft.com/technet/scriptcenter/tools/wmimatic.asp). From the tool's primary screen, select the Win32_Printer Windows Management Instrumentation (WMI) class. (For more information about using Scriptomatic, see "AD and WMI Reporting," May 2003, http://www.winscriptingsolutions.com, InstantDoc ID 38401.) If you want to devise a more automated solution, you can also use Scriptomatic to obtain the correct Win32_Printer properties to use.

What if you simply want to print to the default printer without looking up the printer's name? On later OSs that have expanded WMI classes, such as Windows Server 2003 or Windows XP, you can use the sample code that Listing 3 shows, which retrieves the port name of the default printer for you. To develop this code, I used Scriptomatic to show me which properties to manipulate. The code at callout A in Listing 3 connects to the rootcimv2 namespace on the local computer and retrieves a collection of the installed printers and their attributes. A For Each...Next statement then enumerates the collection, determines which printer is the default, and sets the strPrinterUNC variable to the PortName property. After exiting the loop, the script prints the file to the default printer's port. Because the WMI objItem.PortName property isn't available in Windows 2000, the code in Listing 3 doesn't work with Win2K. If you're running Win2K, you can use Scriptomatic on the Win32_Printer class to see the available printers and choose the one you want, then hard-code that printer's name in your script.

An alternative is to use the TextStream object's Write and WriteLine methods to write the text to a file, then use code like that in Listing 3 to print the file. Or, you can simply print each line to the destination printer port, then let the spooler print the file after all lines have been printed to the port. Listing 4 (another Windows 2003 and XP script) shows code that uses a simple For...Next statement to print the numbers from 1 to 50. The FileSystemObject object's OpenTextFile method provides a TextStream connection to the printer that will stay open until the TextStream object's Close method closes the connection and prints the document.

Printing HTML
Like many scripters, I often dump report data as HTML-formatted text. Although you can easily use a Microsoft Internet Explorer (IE) automation object to print an HTML file, IE 5.0 and later always display a print dialogue box because Microsoft considers printing through IE to be a security concern. For a detailed discussion of this concern, see the Microsoft article "Printing with the Internet Explorer WebBrowser Control," http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnie55/html/wb_print.asp.

One way to print HTML files from a script is to use an external component, such as MeadCo's ScriptX. A subset of ScriptX's printing functionality is available at http://www.meadroid.com. I don't discuss the use of ScriptX in this article, but you can download it and play with it.

Another approach to programmatically printing HTML files is to use Microsoft Office. You can use Office two ways. The obscure approach is to use msohtmed.exe, a little-used program that typically resides in the C:program filesmicrosoft officeoffice11 folder (for Office System 2003) or in the office 10 folder (for Office 2000). You call msohtmed.exe as you do Notepad. For example, to print a file called MyLog.htm, you'd use the code

"C:Program FilesMicrosoft  OfficeOffice11msohtmed.exe"  /p "C:MyLog.htm"

A more common solution is to automate Microsoft Word. Automating Word is easy, and Word can open, read, and print text files. Listing 5 shows how to print from Word. The PRNT_BACKGROUND constant is set to False to ensure that printing occurs in the foreground so that the script doesn't continue to execute until after printing finishes. The DONT_SAVE constant has a value of False to ensure that Word doesn't overwrite the HTML document upon closing it.

After setting the strFilePath variable to the HTML file's path, the script creates a new instance of Word and a handle to the application in the appWord variable. The Document object's Open method opens the HTML file that's defined in strFilePath, then the Application object's PrintOut method prints the document. The PrintOut method lets you specify optional arguments for many attributes, such as background printing, duplex mode, number of pages to print, and number of copies. For more information about the PrintOut method's parameters, see the Microsoft Word Visual Basic Reference, which is installed optionally with Word (look in Word's Help file under Contents, Programming Information). After printing is finished, the Application object's Quit method quits Word without saving the document.

Because newly created instances of Word are invisible by default, Word is hidden from view. If you want to see what Word is doing while the script runs (e.g., during testing), you can add the line

appWord.Visible = TRUE

to the script after the line that opens the document.

Printing from Excel
Scripts that produce large result sets often print their output to Excel for manipulation. Like printing from Word, printing from Excel uses the PrintOut method. However, you can apply Excel's PrintOut method to objects such as charts, ranges, sheets, worksheets, and workbooks.

Listing 6, page 4, shows a simple script that populates an Excel workbook with a small set of data and prints it. After the variable declarations, the script creates an Excel Application object and assigns it to the appExcel variable. The script then adds a workbook to the Application object, renames the first sheet to My Data, and selects the sheet.

The script uses two For...Next statements to populate the sheet's first nine columns with nine rows of data. Within the second loop, the Worksheet object's Cells property accesses each cell and uses the cell's FormulaR1C1 property to put a formula into the cell. The formula uses Excel's RAND() function to generate a random number between 0 and 1 in each cell.

Finally, the script makes the Excel application visible to the user and prints the selected sheet from the Application object's active window. I set the Visible property to True only to show you how it works. When you use Excel in a script, you typically wouldn't make the application visible but would simply use the following code to quit Excel after the script prints the file:

appExcel.DisplayAlerts = FalseappExcel.Quit

If unsaved workbooks remain open when you use the Application object's Quit method, Excel displays a dialog box asking whether you want to save the changes. One way to prevent the dialog box from appearing (e.g., in an unattended job) is to save all workbooks before using the Quit method. Alternatively, you can set the Application object's DisplayAlerts property to False to make Excel quit without displaying the dialog box or saving the workbooks.

Unlike Word, Excel will complete all printing before the script continues execution. Excel's PrintOut method has parameters that let you specify which page number to start on, which page number to end on, and how many copies to print. For details about these parameters, see the Microsoft Excel Visual Basic Reference, which is installed optionally with Excel (you'll find it in Excel's Help file under Contents, Programming Information). However, to print only part of a worksheet or perform the other tasks that you can perform manually through Excel's Page Setup option, you need to use Excel's PageSetup method.

The code snippet in Listing 7 shows how to use some of the PageSetup method's capabilities. The three constants specify the paper type and orientation using the names and values that appear within Excel. The PrintArea property specifies a portion of the worksheet to print—in this case, cells B7 to D9. To activate a specific sheet, you use the Worksheet object's Activate method. After you activate the sheet you want, you use the Application object's ActiveSheet property to refer to the specific sheet, then use the ActiveSheet object's PageSetup method to set the properties for that sheet. The first PageSetup call uses the Orientation property to set the page orientation, whereas the second PageSetup call uses the PaperSize property to set the paper size. The third and fourth PageSetup calls use the FitToPagesWide and FitToPagesTall properties, respectively, to tell Excel to compress the type sizes to fit all the data on the specified number of pages. I find that the FitToPagesWide and FitToPagesTall properties are especially helpful and use them frequently.

The easiest way to find the right parameters for these interface methods and properties is to record a macro that performs the tasks you want to automate, then edit the macro. To find the values for the constants that you need to define in your script, press F2 after you open the macro editor or select Object Browser from the View menu, then examine the Excel class, which lists all the Excel constants. When you click a constant, its definition appears in the results pane. You use that definition to define that constant in your scripts. For more details about how to use macros to obtain information about Excel constants, see "Formatting the Reports," May 2003, http://www.winscriptingsolutions.com, InstantDoc ID 38402.

Setting Printers in Word and Excel
The final aspect you need to know about printing from scripts is how to set the printer. When you print a Word or Excel file from a script, you need to use the Application object's ActivePrinter property to set the printer you want to use. The code samples in Listing 8 show how to set various printers and a fax machine.

The first line in Listing 8 points to the second virtual network port—Ne02 on my computer—which is the port to which the printer connects. The second line uses the LPT1 printer port. The third line shows how to use a UNC path. The fourth line sets a virtual fax port. To determine the port to which your printer is currently set, you can use the Application object's ActivePrinter property.