Thanks.

Something to squirrel away.


Gord

On Sun, 10 Apr 2011 07:33:40 +0200, "Charlotte E" wrote:

Ups, forgot....

Third line should of cause format the number:

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


CE




"Charlotte E" wrote in message
...
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




"Gord Dibben" wrote in message
...
Care to share those 4 short lines?


Gord Dibben MS Excel MVP

On Sat, 9 Apr 2011 22:04:54 +0200, "Charlotte E" wrote:

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

...which only required 4 short lines of VBA :-)

CE



"Dave Peterson" wrote in message
...
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

On 04/09/2011 03:12, Charlotte E wrote:
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