Print button on toolbar linked to specific printer, way to do?

[Bridget]

Argggh, they keep changing colour printers on us <lol>!

I'm in a govt dept that balloons up/deflates due to
election events! In the last 2 weeks, they've changed all
the printers around on us as they move them to accommodate
needs! At the end of the event, the printers will all
return back to their original configurations and locations
<phew>!

The colour printer is the one that concerns me. Is there
a way to just assign a printer, via a macro maybe, to a
button on the toolbar? That way, no matter what printer
was actually there and no matter where it was located, I'd
always press that button to bring the print dialogue box
up.

Here's hoping!

Thanks!

(p.s., I print colour mostly in Excel but if this works in
Excel, could do the same on my Word toolbar, etc.)

 

[Dave Peterson]

Record a macro while you change printers. Then assign that macro to a button on
a custom toolbar.

(Record one when you change it back to your favorite. Then you can easily
toggle between the printers.)

 

[StargateFan]

Record a macro while you change printers. Then assign that macro to a button on
a custom toolbar.

(Record one when you change it back to your favorite. Then you can easily
toggle between the printers.)

(Writing from home. <g>)

Kewl!

But as I mentioned in my original msg (see below), I was hoping to
bring up the print dialogue box for the colour printer, which is not
the default printer. If I record the macro, which I though of doing
(sorry, I should have mentioned that), that would sent out the pages
to print right away, afaik, and would bypass the print dialogue box.

I'm often printing duplex or multiple copies, collated, for the colour
printer so the above bypass is no good.

I know that the code to bring up the print dialogue box is this:

Application.Dialogs(xlDialogPrint).Show

Any way to put in a line of coding to choose the printer we need
before that line above?

Thanks!

 

[Dave Peterson]

Use that recorded line from the macro first:

Option Explicit

Sub testme01()
Dim myPrinter As String
With Application
myPrinter = .ActivePrinter
.ActivePrinter = "your colorprinter name"
.Dialogs(xlDialogPrint).Show
.ActivePrinter = myPrinter
End With
End Sub

 

[StargateFan]

Use that recorded line from the macro first:

Huh? I'm sorry, dunce here <lol>.

Do you mean that I go ahead and record a macro using the colour
printer so as to get the name or data from the macro to use below (I'm
guessing that I use info from macro to replace the words in "your
colorprinter name")???

Second question, does the macro include the "Option Explicit" words
that you show below (sorry, I'm no VBA expert <g>)?

Thanks!

 

[Dave Peterson]

Yep. That's what I meant about recording the macro. After you record the
macro, take a look at the name that was used in the recorded code.

Then look at Windows start button|settings|printers and see if you would have
guessed that exact string.

My bet is that they differed by "on XXXX" at the end.

====
Option explicit

This isn't really part of the macro. It's a directive to the compiler to make
sure you declare any variable you use in the code.

If you don't use it, then you could spend hours looking at:

ctrl = ctr1 + 1

It looks like you're incrementing the same variable. But one of them is CTRL
and the other is CTR1 (ctr one).

And one of the nicest things about declaring variables is that you can get the
VBE's intellisense to help you with your typing.

declare a worksheet variable
dim wks as worksheet
As soon as you hit the dot in then next line, you'll see a list of available
methods and properties.

wks.(intellisense starts up here)


This may seem like a little extra work (declaring your variables), but man, oh,
man, you'll be happy that you do.

In fact, inside the VBE:
tools|Options|editor tab
Check the box: Require Variable Declaration

Any new module will have "option explicit" added to it as soon as you create it.

 

[StargateFan]

It all worked absolutely wonderfully!

There was one snag, though, that I'm hoping can be resolved for this
button/macro to work properly. It isn't a problem with the macro,
it's the setting that was changed in the printer. My supervisor made
it so that the default print on this machien is black and not colour
<sigh>.

I recorded the macro, as advised, to get the printer name. In the
first try I used the mouse to untick the black option. These steps
didn't get recorded. Fine. I wasn't worried. When that happens in
WordPerfect, I just use the keyboard shortcuts the next time and that
does the trick. But it didn't here.

To manually untick the black one has to press the following keys:

- alt+p to bring up the print properties box
- alt+k to untick the restriction
- enter key to apply

Can someone direct me to how I can put the "alt+p", "alt+k" and
"enter" commands into the macro itself using VB language? I'd greatly
appreciate it!

I found that bringing up the print dialogue box via this macro is
great so the macro works really great for that. But I kept forgetting
to untick the black! Since I only ever use this printer for colour
printing, it's just so stupid that the default was set to this. But
that's the way it's been done, so nothing I can do about it except
hopefully change the macro so that it takes off that restriction for
me! <g>

Thanks!

 

[Dave Peterson]

Sometimes VBA can't control all the options that are supported by your printer.

If you recorded a macro and don't get the code to do what you want, you could
struggle with SendKeys (look at help's description).

But it might be easier to set up another printer that has all the settings that
you like. You'll use the same printer driver and the same physical printer, but
you'll have another printer option in Start|Settings|printer.

Then life gets a lot easier.

 

[StargateFan]

Sometimes VBA can't control all the options that are supported by your printer.

If you recorded a macro and don't get the code to do what you want, you could
struggle with SendKeys (look at help's description).

But it might be easier to set up another printer that has all the settings that
you like. You'll use the same printer driver and the same physical printer, but
you'll have another printer option in Start|Settings|printer.

Then life gets a lot easier.

Oh, wouldn't that be nice?!! <lol>

Okay, will look at this SendKeys. It sounds similar to what I used in
WP. I know that when the coding in WP didn't reflect the keystrokes
used, that they didn't get recorded somehow, then I would put the
coding in for those keystrokes. If I can do that in the script here,
editing the macro manually, that whould do the job. Yes, perhaps
clumsily in the eyes of a pro, but then, as long as it gets the job
done, eh?

Your idea re setting up another printer sounds intriguing, though,
even if I'm not sure where to start. If I've not understood you
correctly, I'm setting up a dummy printer that uses everything the
real printer does but which has the added benefits of using settings I
set up? In other words, that would allow me to work around the colour
restriction?

We're the clerks who do the most printing and the restriction isn't
meant for either my supervisor or myself. I'm the one maintaining all
the paperwork for my supervisor and boss; it's just that everyone else
can have access to the printer. So to get around overusing it, my
supervisor opted to have it default to non-colour. That has left me
in a bind since I only use this printer, as I mentioned, for colour
anyway. We have our own regular laserjet for all our other printing
needs.

So did I understand you correctly re setting up the printer? If so, I
unfortunately am not sure how to do that esp. since our printers are
network ones. I know how to set up printers and everything but not
how to set up a dummy one. Do you have a link, by any chance,
describing the basic concept? Well, perhaps basic might not be
detailed enough, something that would help a newbie do this at any
rate?

Well, in the meantime, will look into the SendKeys. That sounds like
a very good thing to learn how to do whether or not it fixes my macro.
<g>

Thanks!

 

[Dave Peterson]

Your idea re setting up another printer sounds intriguing, though,
even if I'm not sure where to start. If I've not understood you
correctly, I'm setting up a dummy printer that uses everything the
real printer does but which has the added benefits of using settings I
set up? In other words, that would allow me to work around the colour
restriction?

Yep. I think you may find setting up the additional printer easier than working
with sendkeys--and it should be more reliable, too. (Lots of stuff can
interfere with sendkeys that you have no control over.)

Just pretend that you're adding a new printer.

Windows Start Button|Settings|Printers|Add printer
(is how I'd start in win98)

<<snipped>>

 

[StargateFan]

Yep. I think you may find setting up the additional printer easier than working
with sendkeys--and it should be more reliable, too. (Lots of stuff can
interfere with sendkeys that you have no control over.)

Just pretend that you're adding a new printer.

Windows Start Button|Settings|Printers|Add printer
(is how I'd start in win98)

<lol> Yes, I figured out all the way right to THERE! <g> Um, I
needed help beyond that point.

Actually I think that will work better as the macros I made on Friday
that worked perfectly wouldn't work at all today. I repeated the
steps several times and re-did everything, but no go. btw, I had all
the correspondence from this thread along and followed the same steps
but no luck. I don't know what changed. Anyway, very disconcerting
and lowering! <sigh>

So I'm hoping that this other way will work as I just have too much to
print with the colour printer on any given day that fiddling is a
nuisance. Also, I can't tell you how many times I forget to untick
the black colour option! <g>

Anyway, can you or anyone point me in the right direction re setting
up a printer in this way - a bogus one type-of-thing, yet putting in
the settings that I need? I'm dealing with a networked printer.

Thanks so much!

 

[Dave Peterson]

In win98, I just add the printer as a new printer.

Then I can name it something nicer and rightclick on it and choose properties.
Set those properties the way you want this printer to behave.

 

[StargateFan]

Since checking for replies is such a pain here from the MS
site, I went this morning over to Google! Much easier to
find threads there! <g>

Dave, your message isn't showing up here in the MS ng, but
I saw that you posted a reply yesterday. I'll quote here
below:

******************************************
"From: Dave Peterson ([email protected])
Subject: Re: Print button on toolbar linked to specific
printer, way to do?


View this article only
Newsgroups: microsoft.public.excel.misc
Date: 2004-06-01 19:14:40 PST


In win98, I just add the printer as a new printer.

Then I can name it something nicer and rightclick on it
and choose properties.
Set those properties the way you want this printer to
behave."
******************************************

Well, this doesn't work. Again, to reiterate, my printer
is networked and even if I call it up a second time, it
doesn't appear in the printers folder a second time. I
have no administrative control over this colourjet, as you
can imagine. Also, I have Windows 2000, which I should
have mentioned. I'm sorry about that.

I don't need this at home, where indeed I do have Win98SE,
the whole problem is here at the office. At home I only
have one printer, anyway <g>.

So if you or anyone can point me in the right direction
(i.e., a set of instructions online) where something like
this can be done on a network printer, I'd really, really
appreciate it. I don't know why the macros that I created
on Friday are no longer working. I tried them again this
morning and no go.

Trouble is, too, that I don't have the kind of time here
at work that I do at home to really track down problems.
If I can get another printer defined for the colour
printer that would allow me to "override" that darn black
restriction, then a simpler macro to call up the colour
dialogue box might work. I don't know, I'll have to try
that part again.

At any rate, getting the printer setup done correctly will
be a great thing!

Again, I'm using a networked printer where I have no
administrative control over yet the restrictions are there
not for my supervisor and myself but for the dozen or so
new people that will come on board during this election
time. Once the election is over, we'll get our old colour
printer back and things will go back to normal. But right
now, I must have access to an easier way to print in
colour.

Thank you so very, very much! I really appreciate it!

 

[Dave Peterson]

We used to use WinNT at work and our printers were also networked. And with
winNT, the printer drivers are usually stored on some server (not locally).

Our printers were set up to do simplex printing. I didn't want to change it
each time I wanted to print, so I installed a local printer driver for that
network printer.

We used HP printers, so I visited the HP printer site and grabbed the drivers.

Then I installed using that driver onto my local harddrive. I could change
those settings anyway I wanted.

I also installed it to use LPT1 as my printer port.

Then I had to connect to that printer each time I booted up my pc.

I used a simple .bat file.

net use lpt1: \\myserver\myprinter /persist:true

(I _think_ lpt1: or lpt1 (no colon) will work, but you're testing my memory!)

The persist means that you shouldn't have to run it more than once. Windows
should remember it (just like a mapped drive) each time you start it up.

I saved that .bat file in a nice location.

And I added that .bat file to my "windows start button|programs|startup" group.

But it's been a little while since I did this.

You could test it to see if the /persist stuff works for you. Just open windows
start button|run and type in your equivalent command. Then restart your pc to
see if LPT1 is still there. If it is, you can dump the .bat file (but keep the
notes--just in case something gets reset sometime).

Now I had a printer connected to the correct network printer (via net use) and
using my local drivers. I set it for back to back printing.

This worked until that printer jammed each time it printed something in duplex
mode and the IT solution was to remove the duplex module from the printer!

===
I was going to come back here to ask what version of windows do you use and if
the printer was a network printer. (I must have missed that in one of your
previous posts--and there's too much here to go through all of them!)

======
And if you really want to get to the newsgroup quicker, here's a link with some
notes:

http://groups.google.com/[email protected]

 

[StargateFan]

Hi, Dave!

[So much easier to check things here from home with Agent!! (Thanks
for the link below re that at the bottom; I'll check into it. <g>]

It sounds neat re getting the driver. I'm a little overwhelmed by the
steps at this point but I'll read them a few times until I feel sure I
know what to do. <fingers crossed> I'm also technically not supposed
to install stuff at work but jeez, it's such a pain the way things are
now that I'm going to have to do something like this.

It really sounds like a neat workaround. Again, thanks.

I'll report back.

D