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