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