Change the default printer

 2009-09-15    Printing    5    159

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
End Function

Leave a comment:

Your comment will only be published after it has been moderated and found spam free.
Your e-mail address will only be used to display your Gravatar.

OPE | 2016-05-13 22:13:50 (GMT)

If you take a look at the example code for Print to a network printer above, in the function GetNetworkPrinterName you can replace " on Ne" & Format(i, "00") with " on TS" & Format(i, "000").

Delain | 2016-05-13 18:03:07 (GMT)

Your PrintToNetworkPrinter code is wonderful and has really helped.

However, when I am working reomote via RDP, it will not work for my printer at my remote location since it is on a "port" that changes (i.e. TS016, then might be TS035, etc.)

Is there a way to modify your code to account for this scenario?

Thank you,

Flemming | 2009-09-08 09:16:44 (GMT)

The above code will fail, if your language does not match..

Do While i < 100
strTempPrinterName = strNetworkPrinterName & " on Ne" & Format(i, "00") & ":"

For my language (Danish, the On is replaced with På (on in Danish))

' Language fix - 1030 = Danish
If Application.LanguageSettings.LanguageID(msoLanguageIDUI) = 1030 Then
strTempPrinterName = strNetworkPrinterName & " på Ne" & Format(i, "00") & ":" ' On is replaced with på

strTempPrinterName = strNetworkPrinterName & " on Ne" & Format(i, "00") & ":"

End If

You can find your language - and the correct "printer string" by do this.

Record a macro where you print a sheet (on the printer you want)....
look in the macro - it will probably say yourprinter on Neo1 something.

If used on several computers, neo1 will probably change (therefore this script is brilliant).

You can see your language code by making a sub:

sub myLang
msgbox Application.LanguageSettings.LanguageID(msoLanguageIDUI)
end sub

THANX for at great script - I can now delete the many subs I have to create on several computers when using BullZip PDF printer :)

OPE | 2009-03-22 16:46:43 (GMT)

You don't need to add any references, the ActivePrinter property belongs to the Excel Application object.

Rod | 2009-03-21 22:33:07 (GMT)

What refernce do i need to add to get .ActivePrinter working?