[Excel VBA] Two Ways to Select or Switch Printers

When automating printing tasks with VBA, you may need to switch the output destination depending on the situation—such as choosing between multiple office printers or outputting as a PDF.

VBA allows you to either “let the user select a printer at runtime” or “fix a specific printer for automatic printing.” This article explains two methods for selecting and switching printers based on your specific needs.

Method 1: Displaying a Dialog Box for User Selection

This method is ideal when you want to give the macro user the freedom to choose the output printer. It calls the built-in “Printer Setup” dialog box in Excel.

Code and Explanation

Sub SelectPrinterViaDialog()

    ' Display the "Printer Setup" dialog box
    Application.Dialogs(xlDialogPrinterSetup).Show
    
    ' Display the name of the selected (currently active) printer
    If Application.ActivePrinter <> "" Then
        MsgBox "Currently selected printer: " & vbCrLf & Application.ActivePrinter
    Else
        MsgBox "No printer was selected."
    End If
    
End Sub

The single line Application.Dialogs(xlDialogPrinterSetup).Show handles the entire process.

• Application.Dialogs(…): A feature to call various built-in Excel dialog boxes.
• xlDialogPrinterSetup: The specific constant name for the “Printer Setup” dialog.
• .Show: This method displays the dialog box on the screen.

When the user selects a printer in the dialog and clicks “OK,” that printer becomes active. You can then retrieve its name using the Application.ActivePrinter property.

Method 2: Specifying the Printer Directly in Code

This method is suitable for fully automated processes where the output destination is fixed—for example, “Invoices always go to Printer A” and “Reports always go to Printer B.”

Important: How to Confirm the Exact Printer Name

Before trying this method, you must know the exact name of the printer registered on your PC.

1. Open Windows Settings.
2. Click Bluetooth & devices > Printers & scanners.
3. From the list of printers, check and copy the printer name exactly as it appears (e.g., “Microsoft Print to PDF”).

Code and Explanation

Sub SetSpecificPrinter()

    ' Declare variables
    Dim targetPrinterName As String
    
    ' Set the exact printer name confirmed beforehand
    ' Example: Specifying "Microsoft Print to PDF"
    targetPrinterName = "Microsoft Print to PDF"
    
    On Error Resume Next ' Avoid error if the printer does not exist
    
    ' Set the active printer
    Application.ActivePrinter = targetPrinterName
    
    On Error GoTo 0 ' Reset error handling
    
    ' Check if the setting was changed correctly
    If Application.ActivePrinter = targetPrinterName Then
        MsgBox "Printer set to: " & targetPrinterName
    Else
        MsgBox "The specified printer '" & targetPrinterName & "' was not found or could not be set."
    End If

End Sub

By assigning the printer name to the ActivePrinter property in the format Application.ActivePrinter = "Printer Name", you can directly switch the active printer.

While this allows for hands-free processing, be aware that it will cause an error if the specified printer name does not exist on the PC running the macro.

Summary

There are two main approaches to selecting printers in VBA:

1. To give the user freedom of choice: Use Application.Dialogs(xlDialogPrinterSetup).Show. This offers high flexibility.
2. To fully automate and fix the output: Use Application.ActivePrinter = "Printer Name". This is effective for standardized tasks.

In both methods, you can follow up with print commands like .PrintOut or .PrintPreview to output to the target printer. Choose the method that best fits your macro’s purpose.