Print with paper from a specific printer-tray

[Guest]

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]

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.

 

[Guest]

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

"davegb" skrev:

 

[dominicb]

Good afternoon (e-mail address removed)

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

 

[Guest]

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

"dominicb" skrev:

 

[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

 

[SRI]

Hi,

Can some one help me in resolving an issue with making the VBA macro to make
the printer print in 'Duplex' mode? The printer model I am using is: HP
Laserjet 4350 PCL6 and is in our network. I am able to select the coorect
tray of the printer theough this macro, but unable to make the command for
'Duplex' printing to work. The default printer setting my my somputer is set
in general for 'Simplex' mode as we print most of the documents in Simplex
mode and only selected few documents in Duplex mode. I am giving the macro
code for your reference here -----

------------------------------
Sub YellowPaperPrinting()
'
' YellowPaperPrinting Macro
'

With ActiveDocument.Styles(wdStyleNormal).Font
If .NameFarEast = .NameAscii Then
.NameAscii = ""
End If
.NameFarEast = ""
End With
With ActiveDocument.PageSetup
.LineNumbering.Active = False
.Orientation = wdOrientPortrait
.TopMargin = InchesToPoints(1)
.BottomMargin = InchesToPoints(1)
.LeftMargin = InchesToPoints(1)
.RightMargin = InchesToPoints(1)
.Gutter = InchesToPoints(0)
.HeaderDistance = InchesToPoints(0.5)
.FooterDistance = InchesToPoints(0.5)
.PageWidth = InchesToPoints(8.5)
.PageHeight = InchesToPoints(11)
.FirstPageTray = 261
.OtherPagesTray = 261
.SectionStart = wdSectionNewPage
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.VerticalAlignment = wdAlignVerticalTop
.SuppressEndnotes = False
.MirrorMargins = False
.TwoPagesOnOne = False
.BookFoldPrinting = False
.BookFoldRevPrinting = False
.BookFoldPrintingSheets = 1
.GutterPos = wdGutterPosLeft
End With
Printer.Duplex = acPRDPVertical
' Abovecommand allows the printer to print in DUPLEX mode
ActiveDocument.PrintOut
End Sub
'**********************************

Let me know if this is possible in any different tool or technique other
than manually set the mode of printing to Duplex in the printer properties.

Thanks in Advance for your time,
Sri.