Print Excel Directly to Printer

[Windsong1948]

I am in a situation that I need to send an active sheet in Excel directly to
the current printer bypassing the already set printer settings. My printer
has 4 drawers w/different paper in each drawer. When I write a macro it does
not change the drawer to the one I want to use and the settings I have in my
books change from what I have them set at. I wish to make a macro for each
drawer and use HP Printer control codes that will send my print to the proper
drawer. Can this be done? I'm hoping you can help.

 

[Jim Cone]

Add three copies of your current printer, each with a different feeder
drawer set....HpLaserJet_dw2 and so on.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Windsong1948"
<Windsong1948@discussions.microsoft.com>
wrote in message
I am in a situation that I need to send an active sheet in Excel directly to
the current printer bypassing the already set printer settings. My printer
has 4 drawers w/different paper in each drawer. When I write a macro it does
not change the drawer to the one I want to use and the settings I have in my
books change from what I have them set at. I wish to make a macro for each
drawer and use HP Printer control codes that will send my print to the proper
drawer. Can this be done? I'm hoping you can help.

 

[Windsong1948]

I have tried what you have suggested - the HP only uses one driver so even
though I setup a new printer the same driver works for each so if I set the
default drawer on the new printer I installed to drawer 3 it changes the
drawer to 3 on the original setup. Do you know how a macro could be written
to include HP's ASCII printer codes for the drawer settings?

 

[Jim Cone]

re: "Do you know how a macro could be written
to include HP's ASCII printer codes for the drawer settings?"...
Sorry, can't help you there.

For what it's worth, I used multiple printer copies for a while with different
settings. Didn't have a problem.
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html



"Windsong1948"
<Windsong1948@discussions.microsoft.com>
wrote in message
I have tried what you have suggested - the HP only uses one driver so even
though I setup a new printer the same driver works for each so if I set the
default drawer on the new printer I installed to drawer 3 it changes the
drawer to 3 on the original setup. Do you know how a macro could be written
to include HP's ASCII printer codes for the drawer settings?

 

[Windsong1948]

Thank you anyway - At least you tried to help.

re: "Do you know how a macro could be written
to include HP's ASCII printer codes for the drawer settings?"...
Sorry, can't help you there.

For what it's worth, I used multiple printer copies for a while with different
settings. Didn't have a problem.
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html



"Windsong1948"
<Windsong1948@discussions.microsoft.com>
wrote in message
I have tried what you have suggested - the HP only uses one driver so even
though I setup a new printer the same driver works for each so if I set the
default drawer on the new printer I installed to drawer 3 it changes the
drawer to 3 on the original setup. Do you know how a macro could be written
to include HP's ASCII printer codes for the drawer settings?

 

[NickHK]

Excel/VBA does not expose a Printer object (or the Printers collection) that
other apps and vb5/6 have.
However, you may be able to Open a file to printer port if the printer is
local. Not sure if the changes will for Excel and subsequent print jobs as
you are bypassing all drivers.
http://vb-helper.com/wanted.htm#print, point # 16.

Otherwise there all the *Printer API routines, where you should be able to
do pretty much anything.

NickHK

 

[Tom Ogilvy]

If you just want to send the command to the printer, that can probably be
done, but I doubt it would achieve what you want.

When you then print the worksheet through Excel and windows, it will send it
own settings that will probably override what you sent. However you might
as well give it a whirl and see.


This is how I print directly and it works for me:

Sub Macro5()
Dim ctrl as Long
Dim tmpstr as String
Open "LPT1:" For Output As #1
Print #1, "[Start of Printing Test]"
For ctrl = 1 To 10
tmpstr = "Printing ine " + Str(ctrl)
Print #1, tmpstr
Next
tmpstr = "[End of printing test]" + Chr(12)
Print #1, tmpstr
Close #1
End Sub


To a Network printer


First, I went to the immediate window in the VBE to query the activeprinter
string


? activePrinter
\\ARDAPS01\1D343E on Ne02:

then I used the first part in the below code:

Sub Macro5()
Dim ctrl As Long
Dim tmpstr As String
Open "\\ARDAPS01\1D343E" For Output As #1
Print #1, "[Start of Printing Test]"
For ctrl = 1 To 10
tmpstr = "Printing Line " + Str(ctrl)
Print #1, tmpstr
Next
tmpstr = "[End of printing test]" + Chr(12)
Print #1, tmpstr
Close #1
End Sub

Worked for me.

Regards,
Tom Ogilvy