How to 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.

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.
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 (“ ”).
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.
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.

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.

Popular Articles:

50 Excel Shortcut to Increase Your Productivity

How to use the VLOOKUP Function in Excel

How to use the COUNTIF function in Excel 2016

How to use the SUMIF Function in Excel

Luc Mallié
October 23, 2019 at 2:54 pm

Hi,
I am using excel 2016 64 bits (1909).
I received "object is required" when executing the instruction Set mynetwork = CreateObject(“WScript.network”) .
Why ,
thank you

Luc

Reply ↓
Jim Ramey
December 25, 2016 at 8:23 pm

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.

Reply ↓
Evan
August 27, 2013 at 8:31 am

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

Reply ↓
Aiden
August 26, 2013 at 11:47 am

"Worked like a dream.

Thanks, for the fast response!"

Reply ↓
Adam
August 26, 2013 at 11:47 am

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

Reply ↓
- Mario
  December 15, 2014 at 9:11 am
  
  It is an old post, but maybe if some else is looking for it
  This will give you a dialog box
  
  Application.Dialogs(xlDialogPrinterSetup).Show
  
  Reply ↓
  - Avi
    May 30, 2019 at 9:52 am
    
    Helped me alot too, thanks!
    
    Reply ↓