Change the default printer using VBA in Microsoft Excel





Most of the computer systems will be mapped to one or more printers in the office.  If you have a printer already setup and mapped to your computer but do not want to print the document from it, you can print it from another printer.  A simple vba macro / code will accomplish this.

The Application.ActivePrinter property needs to be changed in the code in order to print from a different printer other than the default printer.

If we need to print just the active sheet, we can use this code.  However, if you have to print multiple sheets, you can modify the code as required.

Here is the code –
 
img1
 
Here is the code –

Option Explicit

Sub PrintToAnotherPrinter()
Dim STDprinter As String

STDprinter = Application.ActivePrinter
Application.ActivePrinter = “Microsoft fax on fax:”
Activesheet.Printout
Application.ActivePrinter = STDprinter

End Sub
 
Lets understand the code line by line -

First we declare the variable STDprinter as string.  This will hold the name of the default printer.

STDprinter = Application.ActivePrinter
Here, the default printer name is assigned to the variable STDprinter.
 
Application.ActivePrinter = “Microsoft fax on fax:”
“Microsoft fax on fax:” is used as an example.  Using this line, you can change the printer to the one you want to use for the printing.
 
Activesheet.Printout
The active sheet will be printed.  In case you want to print more than one sheet, you can put in a loop in this place in the code which will circulate through the sheets and print the required ones.  Or if you want to print all the sheets in the workbook, you can loop from the 1st to last sheet too.
 
Application.ActivePrinter = STDprinter
This re-assigns the default printer which was assigned to the variable STDprinter to the ActivePrinterproperty and makes it 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 :

To copy the code to the workbook, press Alt + F11 on the keyboard.  Then right click on Microsoft Excel Objects and select Insert.  Then click on Module and copy the code to the code window on the right.

 



3 thoughts on “Change the default printer using VBA in Microsoft Excel

  1. “I want to select which printer from a list of the printers attached to the PC.

    Can do in VB, but cannot see how to do in VBA for Excel
    (Preferably 2000)”

  2. “I want to select which printer from a list of the printers attached to the PC.

    Can do in VB, but cannot see how to do in VBA for Excel
    (Preferably 2000)”

Leave a Reply

Your email address will not be published. Required fields are marked *


7 + four =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>