My workbook is distributed to several users. In my macros, I have a
customized PRINT macto that prints to an ADOBE ACROBAT printer.

The printer has a different name on each of the PCs belonging to my users.
For example:
"Adobe PDF on Ne03:"
"Adobe PDF on Ne09:"
"Adobe PDF on Ne02:"
Is there any way I can programatically determine the printer name so that
the user does not have to customize the macro?

Thanks.

You can let the user decide, show him a list of printers...

If Application.Dialogs(xlDialogPrinterSetup).Show = True Then
'print something
MsgBox "selected a printer"
Else
'handle cancel
MsgBox "clicked cancel"
End If
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Marvin"
wrote in message
My workbook is distributed to several users. In my macros, I have a
customized PRINT macto that prints to an ADOBE ACROBAT printer.

The printer has a different name on each of the PCs belonging to my users.
For example:
"Adobe PDF on Ne03:"
"Adobe PDF on Ne09:"
"Adobe PDF on Ne02:"
Is there any way I can programatically determine the printer name so that
the user does not have to customize the macro?

Thanks.

You can just print to the active printer. If you use
for examle: ActiveSheet.PrintOut it automatically
selects the ActivePrinter for that workstation.

"Marvin" wrote:

My workbook is distributed to several users. In my macros, I have a
customized PRINT macto that prints to an ADOBE ACROBAT printer.

The printer has a different name on each of the PCs belonging to my users.
For example:
"Adobe PDF on Ne03:"
"Adobe PDF on Ne09:"
"Adobe PDF on Ne02:"
Is there any way I can programatically determine the printer name so that
the user does not have to customize the macro?

Thanks.

Thank you. What I want to do is remove the user from the decision process.
I want to save his printer name, select the ADOBE printer, print, then
restore his selected printer.

My experience with my users is that, if allowed, they will make errors.

"Jim Cone" wrote:

You can let the user decide, show him a list of printers...

If Application.Dialogs(xlDialogPrinterSetup).Show = True Then
'print something
MsgBox "selected a printer"
Else
'handle cancel
MsgBox "clicked cancel"
End If
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Marvin"
wrote in message
My workbook is distributed to several users. In my macros, I have a
customized PRINT macto that prints to an ADOBE ACROBAT printer.

The printer has a different name on each of the PCs belonging to my users.
For example:
"Adobe PDF on Ne03:"
"Adobe PDF on Ne09:"
"Adobe PDF on Ne02:"
Is there any way I can programatically determine the printer name so that
the user does not have to customize the macro?

Thanks.

The ADOBE printer is likely not the ActivePrinter for these users. I need a
way to force the output to ADOBE.

"JLGWhiz" wrote:

You can just print to the active printer. If you use
for examle: ActiveSheet.PrintOut it automatically
selects the ActivePrinter for that workstation.

"Marvin" wrote:

My workbook is distributed to several users. In my macros, I have a
customized PRINT macto that prints to an ADOBE ACROBAT printer.

The printer has a different name on each of the PCs belonging to my users.
For example:
"Adobe PDF on Ne03:"
"Adobe PDF on Ne09:"
"Adobe PDF on Ne02:"
Is there any way I can programatically determine the printer name so that
the user does not have to customize the macro?

Thanks.

You can also let the user provide the printer name and port with something
like this:

SelPrn = InputBox("TO SELECT A SPECIFIC PRINTER, ENTER THE SERVER NAME AND
PRINTER NAME IN THE FORMAT SHOWN IN THE SAMPLE BELOW:" & Chr(10) & Chr(10) &
"SAMPLE: \\PrtServer\" & ActivePrinter & Chr(10) & Chr(10) & "OR CLICK ON
CANCEL TO USE DEFAULT PRINTER.", "SELECT PRINTER")
SelPrn = UCase(SelPrn)
If SelPrn = False Or SelPrn = "" Then
Prn = ActivePrinter
Else
Prn = SelPrn
End If
ActiveSheet.PrintOut ActivePrinter:=Prn

Where PrtServer would be replaced with the users server name, and
ActivePrinter will show their current printer data.

"Marvin" wrote:

My workbook is distributed to several users. In my macros, I have a
customized PRINT macto that prints to an ADOBE ACROBAT printer.

The printer has a different name on each of the PCs belonging to my users.
For example:
"Adobe PDF on Ne03:"
"Adobe PDF on Ne09:"
"Adobe PDF on Ne02:"
Is there any way I can programatically determine the printer name so that
the user does not have to customize the macro?

Thanks.

Yor are missing the point. I do NOT want user involvement in the selection.

"JLGWhiz" wrote:

You can also let the user provide the printer name and port with something
like this:

SelPrn = InputBox("TO SELECT A SPECIFIC PRINTER, ENTER THE SERVER NAME AND
PRINTER NAME IN THE FORMAT SHOWN IN THE SAMPLE BELOW:" & Chr(10) & Chr(10) &
"SAMPLE: \\PrtServer\" & ActivePrinter & Chr(10) & Chr(10) & "OR CLICK ON
CANCEL TO USE DEFAULT PRINTER.", "SELECT PRINTER")
SelPrn = UCase(SelPrn)
If SelPrn = False Or SelPrn = "" Then
Prn = ActivePrinter
Else
Prn = SelPrn
End If
ActiveSheet.PrintOut ActivePrinter:=Prn

Where PrtServer would be replaced with the users server name, and
ActivePrinter will show their current printer data.

"Marvin" wrote:

My workbook is distributed to several users. In my macros, I have a
customized PRINT macto that prints to an ADOBE ACROBAT printer.

The printer has a different name on each of the PCs belonging to my users.
For example:
"Adobe PDF on Ne03:"
"Adobe PDF on Ne09:"
"Adobe PDF on Ne02:"
Is there any way I can programatically determine the printer name so that
the user does not have to customize the macro?

Thanks.

Dim iCtr as long
dim FoundIt as boolean

foundit = false
for ictr = 0 to 99
on error resume next
Application.activeprinter = "Adobe PDF on Ne" & format(ictr,"00") & ":"
if err.number = 0 then
foundit = true
exit for
else
'keep looking
err.clear
end if
next ictr
on error goto 0

if foundit = false then
msgbox "No printer close to that name"
else
'do the real work
end if

Marvin wrote:

My workbook is distributed to several users. In my macros, I have a
customized PRINT macto that prints to an ADOBE ACROBAT printer.

The printer has a different name on each of the PCs belonging to my users.
For example:
"Adobe PDF on Ne03:"
"Adobe PDF on Ne09:"
"Adobe PDF on Ne02:"
Is there any way I can programatically determine the printer name so that
the user does not have to customize the macro?

Thanks.

--

Dave Peterson

'This is untested and printers must be on a network.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Sub WhichOneIsTheRightOneForMe()
Dim WshNetwork As Object
Dim oPrinters As Variant
Dim strPrinter As String
Dim i As Long

Set WshNetwork = CreateObject("WScript.Network")
Set oPrinters = WshNetwork.EnumPrinterConnections
For i = 0 To oPrinters.Count - 1 Step 2
strPrinter = oPrinters.Item(i + 1) & " on " & oPrinters.Item(i)
If InStr(1, strPrinter, "Adobe PDF", vbTextCompare) 0 Then
Exit For
Else
strPrinter = vbNullString
End If
Next 'i
If Len(strPrinter) Then
MsgBox strPrinter
Else
MsgBox "Not found. "
End If
Set WshNetwork = Nothing
End Sub
'----------------


"Marvin"
wrote in message
Thank you. What I want to do is remove the user from the decision process.
I want to save his printer name, select the ADOBE printer, print, then
restore his selected printer.

My experience with my users is that, if allowed, they will make errors.

"Jim Cone" wrote:
You can let the user decide, show him a list of printers...

If Application.Dialogs(xlDialogPrinterSetup).Show = True Then
'print something
MsgBox "selected a printer"
Else
'handle cancel
MsgBox "clicked cancel"
End If
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Marvin"
wrote in message
My workbook is distributed to several users. In my macros, I have a
customized PRINT macto that prints to an ADOBE ACROBAT printer.

The printer has a different name on each of the PCs belonging to my users.
For example:
"Adobe PDF on Ne03:"
"Adobe PDF on Ne09:"
"Adobe PDF on Ne02:"
Is there any way I can programatically determine the printer name so that
the user does not have to customize the macro?
Thanks.

Here is an example

Dim activePtr
activePtr = Application.ActivePrinter
Application.ActivePrinter = "Auto HP Deskjet890C On PHILLSERVER:"
ActiveSheet.PrintOut
Application.ActivePrinter = activePtr


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Marvin" wrote in message
...
My workbook is distributed to several users. In my macros, I have a
customized PRINT macto that prints to an ADOBE ACROBAT printer.

The printer has a different name on each of the PCs belonging to my users.
For example:
"Adobe PDF on Ne03:"
"Adobe PDF on Ne09:"
"Adobe PDF on Ne02:"
Is there any way I can programatically determine the printer name so that
the user does not have to customize the macro?

Thanks.

Bob-

Perhaps I am missing something, but I don't see how your code example helps
me identify the ADOBE printer unique to each of my users. Perhaps you can
add some verbage to your example.

Thanks.

"Bob Phillips" wrote:

Here is an example

Dim activePtr
activePtr = Application.ActivePrinter
Application.ActivePrinter = "Auto HP Deskjet890C On PHILLSERVER:"
ActiveSheet.PrintOut
Application.ActivePrinter = activePtr


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Marvin" wrote in message
...
My workbook is distributed to several users. In my macros, I have a
customized PRINT macto that prints to an ADOBE ACROBAT printer.

The printer has a different name on each of the PCs belonging to my users.
For example:
"Adobe PDF on Ne03:"
"Adobe PDF on Ne09:"
"Adobe PDF on Ne02:"
Is there any way I can programatically determine the printer name so that
the user does not have to customize the macro?

Thanks.