Macro to Select Printer?

edzzz

Board Regular
Joined
Aug 19, 2002
Messages
145
Hi,

I'd asked a question a few weeks ago how to do this, but it was never answered. Now I'm just wondering if it's even possible.

I know that a macro can select print areas of various tabs and send them to your printer, but can a macro be written to select 1 of several printers? Just curious.

Thanks,
Ed
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Something like this.

Application.ActivePrinter = "\\MDTNTB\bri1pl on Ne01:"

You can record a macro to get the exact path for your printer
 
Upvote 0
I don't understand that formula. I copied and saved it as a macro in a test spreadsheet, but nothing happens if I run it. Obviously I'm a bit challenged when it comes to programming/code.

Ed :(
 
Upvote 0
Thanks! I did figure out how to run the macro and it showed the path to my 2 printers. Now my question is where would I put the command in the following macro to send it to a particular printer?

Sub printabrenewal()
Application.ScreenUpdating = False
Sheets("Input").Select
Range("A1:h151").Select
ActiveSheet.PageSetup.PrintArea = Selection.Address
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("Census").Select
Range("A1:I60").Select
ActiveSheet.PageSetup.PrintArea = Selection.Address
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub

My active printer is \\ATLREG\ATR07_130 but I'd like to make this macro-button send to the other printer, which is \\ATLREG\ATR07_63 .. can it do this without changing the active printer on the PC?

I tried putting your command (with my printer path) in the 2nd line of the macro, but I got the error

Run-time error '1004':

Method 'Active.Printer' of object '_Application' failed

Any suggestions?
 
Upvote 0
Is this the correct code to select a printer in a macro?

Application.ActivePrinter = "\\ATLREG\ATR07_130"

I've tried inserting this line in the above macro in several different places, but I keep getting the Run-time error.
 
Upvote 0
No, it isn't. Because you have to supply the port to which is connected.

Try this. Manually change the printer to the one that you want.

Then, go to the VBE, and press Ctrl G. In there, type the following

?Application.ActivePrinter

the text that it spits out, is the one that you need to use in your macro.
 
Upvote 0
Wow, thanks! That worked. But it actually changed the default printer of the PC.

Is there no way to send the file to the printer I insert in the code without changing the default printer of the PC? Or do I need to just hardcode a line at the end of the macro to switch back to the original printer?

Ed
 
Upvote 0
A different approach......

What I have is one printer with two trays of different size paper. I reinstalled this printer through control panel. It shows up as 'printer name' (copy 2). Paper tray #1 is the default tray for the original printer. Paper tray #2 is set to be the default of the 'copy 2' printer. Then I use the following macros in my "print" macro:
Sub Ptray1x()
Application.ActivePrinter = "SHARP AL-1000 Series on LPT1:"
End Sub
Sub Ptray2x()
Application.ActivePrinter = "SHARP AL-1000 Series (Copy 2) on LPT1:"
End Sub

Now if I want to print from paper tray #1, I add Ptray1x to my print macro. Likewise, Ptray2x uses tray #2. It works quite well. Maybe this will help.
 
Upvote 0
Correction .. it changed the printer setting for anything else I do in Excel. All other programs still send to the original/default printer. Hmm ...
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top