Print

If you click Print on the File menu in Word, Excel, or PowerPoint, you see the Print dialog box. You use it to set print criteria such as the print range or number of copies, and then you click OK to print the file through the specified printer. When you need to print a document loaded in Word, Excel, or PowerPoint using Visual Basic code, the functional equivalent is the PrintOut method. The PrintOut method allows your programs to print documents loaded in an Office application using a specified printer. You can use the PrintOut method (or PrintOptions object in PowerPoint) to print any file with the settings provided in the Print dialog box.

What the PrintOut Method Does

The PrintOut method prints a document, and you can access it from the Document, Workbook, and Presentation objects in Word, Excel, and PowerPoint, respectively. The PrintOut methods in Word, Excel, and PowerPoint have four arguments in common: From, To, Copies, and Collate. The table on the next page lists the definition of the PrintOut method in Word, Excel, and PowerPoint. These definitions are copied from the Details pane of the Object Browser in the Visual Basic Editor and are listed to show a side-by-side comparison of the method in each application.

Application Definition of the PrintOut Method
Word—Document Sub PrintOut([Background], [Append], [Range], [OutputFileName], [From], [To], [Item], [Copies], [Pages], [PageType], [PrintToFile], [Collate], [ActivePrinterMacGX], [ManualDuplexPrint], [PrintZoomColumn], [PrintZoomRow], [PrintZoomPaperWidth], [PrintZoomPaperHeight])
Excel—Workbook Sub PrintOut([From], [To], [Copies], [Preview], [ActivePrinter], [PrintToFile], [Collate], [PrToFileName])
PowerPoint—Presentation Sub PrintOut([From As Long = -1], [To As Long = -1], [PrintToFile As String], [Copies As Long], [Collate As MsoTriState])

The following procedure lists the code that uses the PrintOut method in Word, Excel, and PowerPoint. You can copy it into a code module in the Visual Basic Editor in Word, Excel, or PowerPoint, but you need to remove the lines that don't apply to the application. For example, if you copy the procedure in the Visual Basic Editor in Word, remove the lines starting with ActiveWorkbook and ActivePresentation, which are used in Excel and PowerPoint, respectively.

Sub PrintDocument()
    ActiveDocument.PrintOut From:="1", To:="2", _
        Copies:=2, Collate:=True
    ActiveWorkbook.PrintOut From:=1, To:=2, _
        Copies:=2, Collate:=True
    ActivePresentation.PrintOut From:=1, To:=2, _
        Copies:=2, Collate:=True
End Sub

The code for all three applications is shown above in one procedure to show the similarity in the use of the PrintOut method when four of the most common arguments of the PrintOut method are used. The From and To arguments, however, mean different things in each application. In Word, Excel, and PowerPoint, the From and To arguments refer to pages, worksheets, and slides, respectively. Note that the type of value required for the From and To arguments in Word is a value of type String. Therefore, quotes are used around the value of the argument. In Excel and PowerPoint, the values of the From and To arguments are of type Integer.

Differences in PowerPoint

In addition to the PrintOut method on the Presentation object, which is similar to the PrintOut method in Word and Excel, PowerPoint provides a PrintOptions object, which contains print options for a presentation. The properties on the PrintOptions object in PowerPoint are functionally equivalent to passing arguments to the PrintOut method in Word and Excel. The unique functionality provided by the PrintOptions object is that its settings are stored with the presentation so that when you next open and print the presentation, you can use the same options without resetting them. The following procedure is functionally equivalent to the PrintOut method listed in the previous procedure. Two collated copies of the first and second slides in the active presentation are printed.

Sub UsingPrintOptionsInPowerPoint()
    With ActivePresentation
        With .PrintOptions
            .RangeType = ppPrintSlideRange
            .Ranges.Add 1, 2
            .NumberOfCopies = 2
            .Collate = True
            .PrintColorType = ppPrintColor
            .PrintHiddenSlides = True
            .FitToPage = True
            .FrameSlides = True
            .OutputType = ppPrintOutputSlides
        End With
        .PrintOut
    End With
End Sub

You use the RangeType property to indicate that only part of the presentation is to be printed. You then use the Ranges property to specify which slides to print.

Using ShellExecute to Print a File

As discussed earlier in this chapter in the "File Management" section, you can use the Windows API ShellExecute to print files without explicitly starting an application like Word, Excel, or PowerPoint, opening the appropriate file, and calling the PrintOut method. Insert a new standard code module in the Visual Basic Editor in any Office application, type the following code, place the cursor in the PrintMiscFile procedure, and then press F5. The file specified by the string "C:\Temp\docwrite.html" is printed. Change this file string to a valid file on your computer.

Declare Function ShellExecute Lib "shell32.dll" _
    Alias "ShellExecuteA" ( _
        ByVal Hwnd As Long, _
        ByVal lpOperation As String, _
        ByVal lpFile As String, _
        ByVal lpParameters As String, _
        ByVal lpDirectory As String, _
        ByVal nShowCmd As Long _
    ) As Long
Sub PrintFile(FileName As String)
    ShellExecute 0, "Print", FileName, "", "", 1
End Sub

Sub PrintMiscFile()
    PrintFile "C:\Temp\docwrite.html"
End Sub