Print with paper from a specific printer-tray

[[email protected]]

Hi,
when I use Excel with VBA and record a macro to select a printer the result
is like this:

Application.ActivePrinter = "\\SWLJUNFS\Ljungby_1 on Ne03:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"\\SWLJUNFS\Ljungby_1 on Ne03:", Collate:=True

but I like to use the lower paper-tray. How to solve it?

Torbjörn Pettersson, Sweden

[davegb]

[email protected] wrote:
> Hi,
> when I use Excel with VBA and record a macro to select a printer the resu=
lt
> is like this:
>
> Application.ActivePrinter =3D "\\SWLJUNFS\Ljungby_1 on Ne03:"
> ActiveWindow.SelectedSheets.PrintOut Copies:=3D1, ActivePrinter:=3D _
> "\\SWLJUNFS\Ljungby_1 on Ne03:", Collate:=3DTrue
>
> but I like to use the lower paper-tray. How to solve it?
>
> Torbj=F6rn Pettersson, Sweden

Record the macro, but change the paper tray while recording it. You'll
be able to see the difference in the code and copy it to your code.

[[email protected]]

Sorry, I tried that but I couldn't see ny difference. The information about
the the tray were not recorded.

"davegb" skrev:

>
> [email protected] wrote:
> > Hi,
> > when I use Excel with VBA and record a macro to select a printer the result
> > is like this:
> >
> > Application.ActivePrinter = "\\SWLJUNFS\Ljungby_1 on Ne03:"
> > ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
> > "\\SWLJUNFS\Ljungby_1 on Ne03:", Collate:=True
> >
> > but I like to use the lower paper-tray. How to solve it?
> >
> > Torbjörn Pettersson, Sweden
>
> Record the macro, but change the paper tray while recording it. You'll
> be able to see the difference in the code and copy it to your code.
>
>

[dominicb]

Good afternoon [email protected]

The paper tray is not controlled by Excel, but by the printer driver softwarewhich excel calls when you click on the properties button in the print dialog.

The easiest way of doing this is to work shortcut keys to use to get to the paper tray selection, change it and exit and use the SendKeys command to do the job. It's messy and not ideal but using VBA outside of Excel you have few choices.

HTH

DominicB

[[email protected]]

Thnks for your answer. I can see that it is a hard question...

"dominicb" skrev:

>
> Good afternoon [email protected]
>
> The paper tray is not controlled by Excel, but by the printer driver
> softwarewhich excel calls when you click on the properties button in
> the print dialog.
>
> The easiest way of doing this is to work shortcut keys to use to get to
> the paper tray selection, change it and exit and use the SendKeys
> command to do the job. It's messy and not ideal but using VBA outside
> of Excel you have few choices.
>
> HTH
>
> DominicB
>
>
> --
> dominicb
> ------------------------------------------------------------------------
> dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
> View this thread: http://www.excelforum.com/showthread...hreadid=480105
>
>

[Ken Macksey]

Hi

If you are using windows 98, it is easier to go to control panel, printers,
and make another copy of your printer. You can then set the defaults for
each copy to be different. Name one copy HP 8100 Tray 1 and the other HP
8100 Tray 2 ect depending on what your printers are. Then set and save the
appropriate printer defaults for each one and then use
application.activeprinter to select with code the one you want.

If you are using win xp, I don't think you can have 2 copies of the same
printer installed anymore. At least I haven't figured out how to do it.
Another great advancement backwards from M.S.

With Xp if you want to program this to happen automatically, you are stuck
with the dreaded sendkeys which is not recomended, but can be made to work.
Otherwise you need users who will select the correct tray using the printer
Dialog.


If you want a sample workbook with some info on changing printer settings
using sendkeys, email me and I will send you one that should get you
started.


HTH

Ken