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

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

Document last updated 2005-06-03 13:32:32 Printer friendly version

User Contributed Comments (in descending order):

Tim from Washington, DC wrote (2006-11-21 21:47:36 CET):

Thank you so much!
After flailing around on many sites without success, your code finally got me where i wanted to go -- thank you!!

William Szabo from USA wrote (2006-09-19 21:58:22 CET):

Your Code for Printing to Network Printers
I had tried many different solutions to this issue and none of them had worked out satisfactorily. Yours is by far the most elegant and simple to adjust to my specific application. I simply called my macro on the line that you remarked as 'Print Something. I also modified the line

strNetworkPrinter = GetFullNetworkPrinterName(Sheet8.Range("A2").Value)

to look at a cell (drop down List) in the worksheet to find the network printer name (end user does not have to get into the code to change printers).

Thank you very much for your help.

Ole P. from Norway wrote (2005-12-21 18:20:58 CET):

Re: Doesn't work with Outlook
The simple answer is: you can't change the active printer in Outlook using a macro since Outlook has no ActivePrinter property.
You can use other applications such as Excel or Word in your Outlook macros to change the active printer.
Or you can simply save your Outlook documents as a file and print it from e.g. Word, you'll probably find some examples here:
http://www.outlookcode.com/

Oracle from Australia wrote (2005-12-21 13:59:59 CET):

Doesn't work with Outlook
Outlook's Application object does not have the ActivePrinter property. How can one change the active printer in an Outlook macro without the use of one of the other Office applications as a middle-man?

pcaballero from Texas wrote (2005-08-24 23:30:25 CET):

Thank you for the printer help
The network priter solution worked like a charm. THANKS!

Bart B from Belgium wrote (2005-06-03 11:23:17 CET):

Thanks
Thanks for the Nexx changing solution... it helped some coworkers of me solving their problem...

Ole P. from Norway wrote (2003-09-10 09:44:02 CET):

Re: How can I list the installed printers in VBA?
You can use the code from this KnowledgeBase article to list the available printers.

Hampen from Sweden wrote (2003-09-09 21:29:34 CET):

How can I list the installed printers in VBA?
I would like to list all the installed printers on the PC thats running my Excel app.

Is it possible?