Change ActivePrinter?

[Charlotte E]

I have a macro, which should change the active printer.

I'm doing this with...

Application.ActivePrinter = "Adobe PDF"

....but it doesn't work?!?

I suspect this has to do with the fact that on some computers the printer is
located "on Ne03:", "on Ne09:", etc...

How to change active printer, no matter "on what" the printer is located???


Thanks in advance...

 

[Dave Peterson]

I think you should record a macro when you change printers to the Adobe PDF just
to make sure that you get the exact string (minus the NE stuff).

I saved this from a previous post.

When I do this kind of thing, I'll let the user decide what printer to use.

Maybe you can just show a dialog and let the user check/verify their printer
choices:

Application.Dialogs(xlDialogPrinterSetup).Show

But if you wanted to automate it, you could use something like:

Option Explicit
Sub testme()
Dim UseThisPrinter As String
Dim CurPrinter As String

'save the current printer
CurPrinter = Application.ActivePrinter

'make sure that the string is correct with your recorded macro
UseThisPrinter = GetPrinter(myPrinterName:="\\mrafp1\MRA-ADMINCP1 on Ne")

If UseThisPrinter = "" Then
MsgBox "Printer not found--what should happen"
Exit Sub '???
End If

'and change (temporarily to the network printer you want)
Application.ActivePrinter = UseThisPrinter

'your code to print


'change their printer back to what they like
Application.ActivePrinter = CurPrinter

End Sub
Function GetPrinter(myPrinterName As String) As String

Dim iCtr As Long
Dim myStr As String
Dim FoundIt As Boolean
Dim CurPrinter As String

CurPrinter = Application.ActivePrinter

FoundIt = False
For iCtr = 0 To 99
On Error Resume Next
myStr = myPrinterName & Format(iCtr, "00") & ":"
Application.ActivePrinter = myStr
If Err.Number = 0 Then
FoundIt = True
Exit For
Else
'keep looking
Err.Clear
End If
Next iCtr
On Error GoTo 0

Application.ActivePrinter = CurPrinter

If FoundIt = True Then
GetPrinter = myStr
Else
GetPrinter = ""
End If

End Function

 

[Charlotte E]

Thanks for your suggestion, but I came up with another solution myself...

....which only required 4 short lines of VBA

CE

 

[GS]

After serious thinking Charlotte E wrote :

Thanks for your suggestion, but I came up with another solution myself...

...which only required 4 short lines of VBA

CE

So then.., can you share that solution with us?

 

[Gord Dibben]

Care to share those 4 short lines?


Gord Dibben MS Excel MVP

 

[Charlotte E]

Care to share those 4 short lines?

But of cause


On Error Resume Next
For counter = 99 to 0 step -1
Application.ActivePrinter = "Adobe PDF on Ne" & Counter & ":"
Next


Not a fancy solution, and maybe a bit of an overkill, but it works
Tried it on 4 different computers, running WinXP or Win7, and XL2003 or
XL2010.

Just shows that a little brute force should never be underestimated


Best regards...

CE

Care to share those 4 short lines?


Gord Dibben MS Excel MVP

 

[Charlotte E]

Ups, forgot....

Third line should of cause format the number:

Application.ActivePrinter = "Adobe PDF on Ne" & Format(Counter,"00") & ":"


CE

 

[Gord Dibben]

Thanks.

Something to squirrel away.


Gord

Ups, forgot....

Third line should of cause format the number:

Application.ActivePrinter = "Adobe PDF on Ne" & Format(Counter,"00") & ":"


CE

 

[GS]

I don't see where this lets you set the actvie printer to any specific
one since it cycles through the entire loop changing the setting each
iteration. At the end the ActivePrinter is "Adobe PDF on NE99;".

I thought you were going to show us how you set the active printer to
THE one that actually exists, with only 4 lines of code!

What if "Adobe PDF on NE99;" isn't the PDF printer? Now what do you do?

 

[GS]

GS explained on 4/10/2011 :

At the end the ActivePrinter is "Adobe PDF on NE99;".

Correction: the above line should read...

At the end the ActivePrinter is "Adobe PDF on NE00;".

 

[Charlotte E]

It DOES set the printer correcly!

Yes, it loops through the Nexx, but works like this:

Say I want to set the active printer to "Adobe PDF", but I don't know on
which Nexx: it is located.
If I set it to "Adobe PDF on Ne99:" it will not do so, if the "Adobe PDF" is
on, say, Ne09.
So, by looping through the possible Ne numbers, I make sure the active
printer gets set no matter what Ne it is located on.

If the code ends on Ne00: when you run it, it is because the printer is
located on Ne00:

Anyway, it works by me, and then I'm happy


CE

 

[GS]

Thanks for clarifying! I suspected that's what you were doing but
wasn't sure.

And what do you do if there is no Adobe PDF printer? In v2003 and
earlier I've been prompting users to change to the PDF printer of their
choosing when they click a menuitem to print to PDF. This obviates any
need to set ActivePrinter. In v2007+ we can execute the Home menuitem
and let the user configure the output.

 

[Charlotte E]

All users of this macro has "Adobe PDF"

PS: And I'm still using XL2003 - at least until the day there's no other
choice - then I switch to "Open Office" or another menu/toolbar based
spreadsheet - ribbons is a HUGE step backwards, and not even remotely as
flexible and userfriendly!

And, if they start using ribbons in Windows itself - as rumored: Linux, here
I come!!!



CE

 

[GS]

Charlotte E used his keyboard to write :

All users of this macro has "Adobe PDF"

PS: And I'm still using XL2003 - at least until the day there's no other
choice - then I switch to "Open Office" or another menu/toolbar based
spreadsheet - ribbons is a HUGE step backwards, and not even remotely as
flexible and userfriendly!

And, if they start using ribbons in Windows itself - as rumored: Linux, here
I come!!!



CE

Hi Charlotte,

Well, I will say that I agree with you about the ribbon. I don't have
much problem with it now, though, since the only tab my apps use when
running in XL12+ is the Addins tab, where I have all the menus just as
in earlier versions. (In fact, I use the same XLA)

The Addins tab is the only one that displays because my apps are
configured to run in automated instances rather than the user's default
instance, and so I lock it right down to only allow access to what my
apps need.

When MSO12 first came out with the ribbon I was really disappointed
with M$ for doing that. So much so that I decided to make all my XL
addins standalone Windows apps using VB6 and FarPoint's Spread.OCX.
(It's nice that we can use 3rd party components and make our own
spreadsheet apps that run outside MSO<bg>)

Also at the time, I was driven to look closely at OpenOffice. That
would be a good alternative to MSO for now, but I'm waiting to see what
unfolds for x64.

Sigh, ..I guess as long as Windows persists as THE popular OS platform
for my clients, I'm stuck having to work with whatever my clients
want.<g>

 

[Charlotte E]

Sigh, ..I guess as long as Windows persists as THE popular OS platform for

my clients, I'm stuck having to work with whatever my clients want.<g>

It's not really want your clients want, is it?!?
It's want Microsoft forces upon your clients - everybody hates ribbons, but
since Microsoft loves it, we're stuck with it...

Actually had XL2007 installed for 6 month, trying to force myself into
learing to use it - finally, after those 6 months, I gave up - it simply
took me too loog to find things, that I could find in a micro second on XL
2003.

I guess that those who really don't care about the ribbons are those using
mostly hotkeys, since the hotkeys are more or less the same on both systems.

But, I'm a mouse person myself, and I hate spending my time searching those
#?&?& ribbons for functions that should be right there....

And, also, menus and toolbars is MUCH MUCH easier to work with in VBA!

So, the day I can no longer use menu and toolbar based Excel, I find another
spreadsheet, that are using these....


CE

 

[GS]

Charlotte E formulated on Thursday :

It's not really want your clients want, is it?!?
It's want Microsoft forces upon your clients - everybody hates ribbons, but
since Microsoft loves it, we're stuck with it...

Very true

Actually had XL2007 installed for 6 month, trying to force myself into
learing to use it - finally, after those 6 months, I gave up - it simply took
me too loog to find things, that I could find in a micro second on XL 2003.

I guess that those who really don't care about the ribbons are those using
mostly hotkeys, since the hotkeys are more or less the same on both systems.

But, I'm a mouse person myself, and I hate spending my time searching those
#?&?& ribbons for functions that should be right there....

And, also, menus and toolbars is MUCH MUCH easier to work with in VBA!

I have an XLA that installs the earlier menubar in the Addins tab so I
can use the familiar menus in v12.

As for using them via VBA goes, you can do that easily enough in v12
with macros. Or am I misunderstanding your context here?

So, the day I can no longer use menu and toolbar based Excel, I find another
spreadsheet, that are using these....

That's why I use FP's Spread.ocx in VB6.EXEs. I've been able to
duplicate anything I've developed in Excel as a stand-alone Windows
app.

Also, check out Ron DeBruin's website for his fine examples of how to
manipulate the ribbon.

http://www.rondebruin.nl/ribbon.htm

Thanks to his examples I've been able to use my XLAs in v12 with a
dummy XLAM to set up the ribbon however I need it. Allows me to
maintain one app that runs in both early and newer versions. Since most
my projects are 'dictator apps', they pretty much lock down the Excel
UI to the point where most my users don't even realize they're working
in Excel. Ron's examples contributed largely to being able to do the
same in the newer versions.