Change the default printer

 2009-09-15    Printing    5    165

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

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
    strCurrentPrinter = Application.ActivePrinter ' save the current active printer
    strNetworkPrinter = GetNetworkPrinterName("HP LaserJet 8100 Series PCL", True)
    If Len(strNetworkPrinter) > 0 Then ' found the network printer (and activated it)
        Worksheets(1).PrintOut ' print something
        ' change back to the previously active printer
        Application.ActivePrinter = strCurrentPrinter
    End If
End Sub

Function GetNetworkPrinterName(strNetworkPrinterName As String, Optional blnChangePrinter As Boolean = False) As String
' returns the full network printer name
' returns an empty string if the printer is not found
' changes the active printer if blnChangePrinter is True
' example: GetNetworkPrinterName("HP LaserJet 8100 Series PCL")
' example result: "HP LaserJet 8100 Series PCL on Ne04:"
Dim strCurrentPrinter As String, strTempPrinter As String, i As Long
    strCurrentPrinter = Application.ActivePrinter
    i = 0
    Do While i < 100
        On Error Resume Next ' try to change to the network printer
        Select Case Application.International(xlCountryCode)
            Case 47
                strTempPrinter = strNetworkPrinterName & " på Ne" & Format(i, "00") & ":" ' norwegian
            Case Else
                strTempPrinter = strNetworkPrinterName & " on Ne" & Format(i, "00") & ":" ' english
        End Select
        Application.ActivePrinter = strTempPrinter
        On Error GoTo 0
        If Application.ActivePrinter = strTempPrinter Then
            GetNetworkPrinterName = strTempPrinter ' the network printer was found
            If Not blnChangePrinter Then
                On Error Resume Next
                Application.ActivePrinter = strCurrentPrinter ' change back to the original printer
                On Error GoTo 0
            End If
            i = 100 ' makes the loop end
        End If
        i = i + 1
    Loop
End Function