Printing To A Specified Printer

  • Thread starter Thread starter Steven Pearl
  • Start date Start date
S

Steven Pearl

What would be the syntax to print 5 copies of the current
worksheet to a designated printer (and where do we get the
printer name from)

Thanks,

Steve
 
Try this

Sub Printtest()
Dim DPrinter As String
DPrinter = Application.ActivePrinter
Application.ActivePrinter = "hp officejet k series on Ne00:"
ActiveSheet.PrintOut Copies:=5
Application.ActivePrinter = DPrinter
End Sub

This macro you can assign to a button

To know the names of your printers
Change to it in the ctrl P dialog (Cancel after choose the printer ) and run
this so you know the name that you must use in the macros

Sub nameprinter()
MsgBox Application.ActivePrinter
End Sub

The PrintOut also have a ActivePrinter argument
I don't know if this will set it back to the old printer when it is done??
 
There is an unforseen consequence of using the suggestion proposed by Ron de
Bruin that you should be aware of: Suppose you specify a specific printer as an
ActivePrinter object, for example,

Application.ActivePrinter="HP LaserJet 4SI on LPT1:"

Then what happens is that the system default printer across the entire Windows
environment is changed to that printer, just as though you went to the Control
Panel and made that change. Not only that, but it will make this change on an
instantaneous basis, so other users in the middle of printing on the system
will have their output messed up accordingly. This not-so-well-known bug in
Excel is documented in MS Knowledge Base Article 209722.

-- Dennis Eisen
 
That was perfect. One other question. If I want to specify
a specific tray to print to on that printer, can that be
handled by the VB code as well? If so, how?

Thanks a ton.
-----Original Message-----
Try this

Sub Printtest()
Dim DPrinter As String
DPrinter = Application.ActivePrinter
Application.ActivePrinter = "hp officejet k series on Ne00:"
ActiveSheet.PrintOut Copies:=5
Application.ActivePrinter = DPrinter
End Sub

This macro you can assign to a button

To know the names of your printers
Change to it in the ctrl P dialog (Cancel after choose the printer ) and run
this so you know the name that you must use in the macros

Sub nameprinter()
MsgBox Application.ActivePrinter
End Sub

The PrintOut also have a ActivePrinter argument
I don't know if this will set it back to the old printer when it is done??




--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




"Steven Pearl" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
Back
Top