Change The Default Printer Using VBA in Microsoft Excel 2016

Hi There!

Is your default printer situated in the far corner of the office?

Want to print your sheet from the printer by your side?

You can change it in the print dialogue section but you are too lazy to do that too?

Well, most inventive people are lazy, like you and me.

Don’t worry. I’ve got you. Let’s do it using VBA for once and all…
Below is the code that changes your default printer to your choice of printer and sets back the original printer to the default printer.

Sub Change_Default_Printer()
Set mynetwork = CreateObject(“WScript.network”)
mynetwork.setdefaultprinter Your Printer Name    ‘write your printers name here
ActiveSheet.PrintOut
mynetwork.setdefaultprinter “original_Default_Printer”  set back your original printer to default”
End Sub

Let’s crack each line down.

  1. Set mynetwork = CreateObject(“WScript.network”) :  Here we have created an object “my network” of WScript.Network using VBA’s CreateObject() function. Internally it returns “IWshNetwork2” type of object.
    WshNetwork2 gives access to the network of computers. We can play around with all the devices connected to the computer. For now, we will use printers.
  2. mynetwork.setdefaultprinter Your Printer Name: In this line, we used to excel vba select printer. default printer property of WshNetwork2object is used to select the default printer to your choice. Write your printer’s name between double quotes (“ ”).
  3. ActiveSheet.PrintOut: This command prints your document from the changed printer. I have printed the active sheet here. You can give any sheet name to print it immediately.
  4. mynetwork.setdefaultprinter “original_Default_Printer”This line sets your original printer back to default. This is optional. If you don’t want to change, skip this line. Or you can set it to some other printer before your code ends.

wsae

So how was it? Did it work for you? Was it difficult? Let me know in the comments. Stay tuned for more innovative and easy lessons. Bye.

Users are saying about us...

  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)”

  3. How can I code in the fax number to dial from Excel VBA? I can call the Fax and Scan, but then I have to manually type in the phone number. I am using the Fax and scan form a workstation and using Fax and scan from a server if that matters. Thank you for any assistance you can give me on this. Jim.

Leave a Reply

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

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>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube