VBA - Printing

Printing a Workbook

Workbooks("Book2.xls").PrintOut 
ThisWorkbook.PrintOut(From, To, Copies) 

Printing to a File

ActiveSheet.PrintOut PrintToFile:=Time, PrToFileName:="name of file.prn"             'Printing to a file  

Print to a network printer

The example macros below shows how to get the full network printer name (useful when the network printer name can change) and print a worksheet to this printer:

Sub PrintToNetworkPrinterExample() 
Dim strCurrentPrinter As String, strNetworkPrinter As String 
    strNetworkPrinter = GetFullNetworkPrinterName("HP LaserJet 8100 Series PCL") 
    If Len(strNetworkPrinter) > 0 Then ' found the network printer  
        strCurrentPrinter = Application.ActivePrinter 
        ' change to the network printer
        Application.ActivePrinter = strNetworkPrinter 
        Worksheets(1).PrintOut ' print something  
        ' change back to the previously active printer
        Application.ActivePrinter = strCurrentPrinter 
    End If 
End Sub 

Function GetFullNetworkPrinterName(strNetworkPrinterName As String) As String 
' returns the full network printer name
' returns an empty string if the printer is not found
' e.g. GetFullNetworkPrinterName("HP LaserJet 8100 Series PCL") 
' might return "HP LaserJet 8100 Series PCL on Ne04:"
Dim strCurrentPrinterName As String, strTempPrinterName As String, i As Long 
    strCurrentPrinterName = Application.ActivePrinter 
    i = 0 
    Do While i < 100 
        strTempPrinterName = strNetworkPrinterName & " on Ne" & Format(i, "00") & ":" 
        On Error Resume Next ' try to change to the network printer  
        Application.ActivePrinter = strTempPrinterName 
        On Error GoTo 0 
        If Application.ActivePrinter = strTempPrinterName Then 
            ' the network printer was found
            GetFullNetworkPrinterName = strTempPrinterName 
            i = 100 ' makes the loop end  
        End If 
        i = i + 1 
    Loop 
    ' remove the line below if you want the function to change the active printer
    Application.ActivePrinter = strCurrentPrinterName ' change back to the original printer  
End Function 

Change the default printer

This example macro shows how to print a selected document to another printer then the default printer. This is done by changing the property Application.ActivePrinter:

Sub PrintToAnotherPrinter() 
Dim strCurrentPrinter As String 
    strCurrentPrinter = Application.ActivePrinter ' store the current active printer  
    On Error Resume Next ' ignore printing errors  
    Application.ActivePrinter = "microsoft fax on fax:" ' change to another printer  
    ActiveSheet.PrintOut ' print the active sheet  
    Application.ActivePrinter = strCurrentPrinter ' change back to the original printer  
    On Error Goto 0 ' resume normal error handling  
End Sub 

Identify Default Printer

Private Declare Function GetProfileStringA Lib "kernel32" (ByVal lpAppName As String, ByVal lpKeyName As String, ByVal lpDefault As String,  ByVal lpReturnedString As String, ByVal nSize As Long) As Long 

Sub DefaultPrinterInfo() 
    Dim strLPT As String * 255 
    Dim Result As String 
    Call GetProfileStringA _ 
       ("Windows", "Device", "", strLPT, 254) 
    
    Result = Application.Trim(strLPT) 
    ResultLength = Len(Result) 

    Comma1 = Application.Find(",", Result, 1) 
    Comma2 = Application.Find(",", Result, Comma1 + 1) 

'   Gets printer's name
    Printer = Left(Result, Comma1 - 1) 

'   Gets driver
    Driver = Mid(Result, Comma1 + 1, Comma2 - Comma1 - 1) 

'   Gets last part of device line
    Port = Right(Result, ResultLength - Comma2) 

'   Build message
    Msg = "Printer:" & Chr(9) & Printer & Chr(13) 
    Msg = Msg & "Driver:" & Chr(9) & Driver & Chr(13) 
    Msg = Msg & "Port:" & Chr(9) & Port 

'   Display message
    MsgBox Msg, vbInformation, "Default Printer Information" 
End Sub 

Printing to PDF

Range("B2:O37").PrintOut 

ActiveWorkbook.ExportAsFixedFormat 
ActiveSheet.ExportAsFixedFormat _ 

Dim cht As Chart 
Set cht = ws.ChartObjects("Chart 1").Chart 
cht.ExportAsFixedFormat 

Saving a Range as PDF

Private Sub Range_SaveAsPDF 
   Call Range.ExportAsFixedFormat( _ 
      Type:=xlFixedFormatType.xlTypePDF, _ 
      Filename:="C:\Temp\FileName", _ 
      Quality:=xlFixedFormatQuality.xlQualityStandard, _ 
      IncludeDocProperties:=True, _ 
      IgnorePrintAreas:=False, _ 
      OpenAfterPublish:=True) 
End Sub 

Type - Can be either xlTypePDF or xlTypeXPS
Filename - A string that indicates the name of the file to be saved. You can include a full path, or Excel saves the file in the current folder.
Quality - Can be set to either xlQualityStandard or xlQualityMinimum.
IncludeDocProperties - Set to True to indicate that document properties should be included, or set to False to indicate that they are omitted.
IgnorePrintAreas - If set to True, ignores any print areas set when publishing. If set to False, uses the print areas set when publishing.
OpenAfterPublish - If set to True, displays the file in the viewer after it is published. If set to False, the file is published but not displayed.
From - The number of the page at which to start publishing. If this argument is omitted, publishing starts at the beginning.
To - The number of the last page to publish. If this argument is omitted, publishing ends with the last page.
FixedFormatExtClassPtr - Pointer to the FixedFormatExt class.