Excel VBA: How to Set a Printer for Printing and Advanced Techniques

When working with Excel VBA, printing is a common task. Many professionals automate report generation, invoices, or labels directly from Excel. But one frequent challenge is ensuring that VBA prints to the correct printer. By default, Excel prints to the system’s default printer, which may not always be what you need.

In this comprehensive guide, we’ll cover how to set a specific printer in VBA before printing, show practical code examples, and introduce advanced techniques such as selecting printers dynamically, checking printer availability, and printing different sheets to different printers.

✅ Why Specify a Printer in Excel VBA?

Normally, when you run a simple print command in VBA:

ActiveSheet.PrintOut

Excel sends the job to your default printer. But in real business scenarios, you may need more control:

Printing reports to a network printer.
Sending invoices to a PDF printer for digital storage.
Printing labels to a special label printer.
Assigning different worksheets to different printers.
Avoiding mistakes by forcing the macro to use the correct printer.

Specifying the printer inside your VBA code ensures accuracy and consistency.

✅ The Basic VBA Print Command

The standard way to print from VBA is:

ActiveSheet.PrintOut Copies:=1, Collate:=True

This prints the active sheet, but does not let you choose which printer to use. To control the printer, we use the ActivePrinter property.

✅ Method 1: Set a Specific Printer with ActivePrinter

The ActivePrinter property in VBA stores the currently selected printer. You can assign it to a different printer by name.

Sub PrintToSpecificPrinter()
Dim originalPrinter As String
'Store the current default printer
originalPrinter = Application.ActivePrinter
'Set printer by name
Application.ActivePrinter = "Microsoft Print to PDF on Ne00:"
'Print the active sheet
ActiveSheet.PrintOut
'Restore the original printer
Application.ActivePrinter = originalPrinter
End Sub

Notes:

The printer name must exactly match what Windows shows in Devices and Printers.
The suffix (e.g., on Ne00:) may vary between systems.
Always restore the original printer to avoid affecting future print jobs.

✅ Method 2: Let the User Choose a Printer

Sometimes you want the user to decide which printer to use. VBA can display a list of available printers.

Sub SelectPrinterAndPrint()
Dim prt As String
Dim originalPrinter As String
'Save current printer
originalPrinter = Application.ActivePrinter
'Ask user to input printer name
prt = InputBox("Enter the printer name:", "Select Printer", Application.ActivePrinter)
'Set the chosen printer
If prt <> "" Then
Application.ActivePrinter = prt
ActiveSheet.PrintOut
End If
'Restore original
Application.ActivePrinter = originalPrinter
End Sub

This is useful when multiple users run the macro on different computers.

✅ Method 3: Dynamic Printer Selection from a Cell

For automation, you can store the printer name inside a cell and let VBA read it.

Sub PrintUsingCellValue()
Dim originalPrinter As String
Dim targetPrinter As String
originalPrinter = Application.ActivePrinter
targetPrinter = Sheets("Settings").Range("B2").Value
If targetPrinter <> "" Then
Application.ActivePrinter = targetPrinter
ActiveSheet.PrintOut
Application.ActivePrinter = originalPrinter
Else
MsgBox "No printer specified in Settings!B2"
End If
End Sub

With this setup, you can change the printer by editing the Settings sheet instead of modifying code.

✅ How to Find the Correct Printer Name

One tricky part is getting the exact printer name, including the port (Ne00:).

You can run this snippet to list all installed printers:

Sub ListPrinters()
Dim pr As Variant
For Each pr In Application.Printers
Debug.Print pr.DeviceName
Next pr
End Sub

Check the Immediate Window (Ctrl + G) to see the results. Copy the exact name into your code.

✅ Printing Multiple Sheets to Different Printers

In some workflows, you may want different sheets to print on different printers. For example:

Print invoices to the office printer.
Print labels to the label printer.
Print reports to PDF.

Sub PrintDifferentSheets()
Dim originalPrinter As String
originalPrinter = Application.ActivePrinter
'Print Sheet1 to PDF
Application.ActivePrinter = "Microsoft Print to PDF on Ne00:"
Sheets("Sheet1").PrintOut
'Print Sheet2 to Office Printer
Application.ActivePrinter = "HP LaserJet on Ne01:"
Sheets("Sheet2").PrintOut
'Restore
Application.ActivePrinter = originalPrinter
End Sub

This ensures each type of document goes to the correct printer.

✅ Advanced Technique: Error Handling for Missing Printers

Sometimes the specified printer may not exist on another user’s machine. Without checks, this will cause VBA to throw an error.

Sub SafePrint()
Dim originalPrinter As String
Dim targetPrinter As String
Dim found As Boolean
Dim pr As Variant
originalPrinter = Application.ActivePrinter
targetPrinter = "HP LaserJet on Ne01:"
found = False
For Each pr In Application.Printers
If pr.DeviceName = targetPrinter Then
found = True
Exit For
End If
Next pr
If found Then
Application.ActivePrinter = targetPrinter
ActiveSheet.PrintOut
Else
MsgBox "Target printer not available. Printing to default."
ActiveSheet.PrintOut
End If
Application.ActivePrinter = originalPrinter
End Sub

This makes your macro more portable and user-friendly.

✅ Other Useful Print Options in VBA

The PrintOut method has several parameters:

ActiveSheet.PrintOut From:=1, To:=2, Copies:=2, Collate:=True, IgnorePrintAreas:=False

From / To → Print specific pages.
Copies → Number of copies.
Collate → Whether to collate multi-page prints.
IgnorePrintAreas → Whether to ignore defined print areas.

By combining these with printer selection, you can fully automate your Excel printing workflow.

✅ Real-World Use Cases

Invoice Automation
- Print invoices to PDF automatically for archiving.
- Send physical copies to a network printer.
Manufacturing Labels
- Print labels directly to a thermal label printer.
- Ensure reports still go to the standard office printer.
Financial Reports
- Automate monthly report printing to a specific department printer.
- Save digital copies as PDFs at the same time.
Multi-user Environments
- Centralize printer selection via a Settings sheet.
- Avoid errors when default printers differ across systems.

✅ Common Mistakes to Avoid

Mistake	Why It Happens	Fix
Wrong printer name	Missing port suffix (`on Ne00:`)	Use `ListPrinters` to confirm names
Not restoring original printer	Leaves Excel stuck on another printer	Always save and restore ActivePrinter
Hardcoding for one system	Fails on other machines	Use dynamic lookup or error handling
Ignoring user permissions	Some printers may be restricted	Add clear error messages

✅ Frequently Asked Questions (FAQ)

❓ Can VBA change the system’s default printer?

No. VBA can only set Excel’s ActivePrinter for the current session. It does not change Windows’ default printer permanently.

❓ Do I need the port number (`Ne00:`)?

Yes. Excel requires the full printer name with port. Otherwise, you’ll get an error.

❓ Can I print directly to PDF with VBA?

Yes, if you set the printer to Microsoft Print to PDF. You can also specify the output file using .ExportAsFixedFormat.

❓ What if the printer is not installed?

VBA will throw an error. Use error handling and provide a fallback to the default printer.

✅ Summary

Specifying a printer in Excel VBA ensures your reports, invoices, and labels are always printed to the correct location.

Key techniques include:

Using Application.ActivePrinter to set the printer.
Restoring the original printer after printing.
Letting users choose printers via input or cells.
Printing different sheets to different printers.
Adding error handling for unavailable printers.

By mastering these methods, you can build reliable, professional VBA printing solutions that save time and reduce mistakes.

✅ Final Thoughts

Printing may seem simple, but in business environments, controlling where and how Excel prints is essential. With VBA, you can go beyond manual printing and create automated workflows tailored to your needs. Whether you’re producing invoices, labels, or large reports, specifying printers through VBA ensures accuracy and efficiency.

Once you apply these techniques, you’ll no longer worry about Excel sending documents to the wrong printer. Instead, you’ll have full control—making your automation projects more professional and dependable.