An alternative is to allow the user to specify the printer...
Application.Dialogs(xlDialogPrinterSetup).Show
--
Jim Cone
Portland, Oregon USA
(Special Sort info... http://www.contextures.com/excel-sort-addin.html)





"Ludo"
wrote in message ...
Hi,
Win XP Professional SP2
Excel 2003

I've been browsing through this forum, but couldn't find any usefull
info for my problem.

I have (for the moment) 3 PC's where i need to print to the same
multitray network printer.
I've setup different printers on one PC (testing purpose) so that i
can change from tray by changing the active printer.
As seen in a lot of previous answers, the printer name is followed
with a "on Nexx:"
This is a local number, so i guess it would be different on each PC.
The utility ( .XLT) i would like to run is located on a server, so by
clicking on a shortcut on one of those PC's i'll open the Excel
utility. This singe utility is easy in maintenance than installing it
on all those different PC's.

Depending on certain criteria, i need to print on white OR yellow
paper (tray1 or tray2), thats why i need to setup different printers
on those PC's.

So my question is:
How can i retreive the Nexx: number automaticly for those 2 printers
on those 3 different PC's, witouth hardcoding it?
The printernames a
Kyocera FS-3900DN KX FAT AV Tray1
Kyocera FS-3900DN KX FAT AV Tray2

I found this code, but can't get it working, think i need to select a
reference in the VBA editor, but don't know witch one.

Function GetPrinterPort(printer As String) As String
CreateObject ("RegObj.Registry")
Dim objReg As RegObj.Registry
Dim objRootKey As RegObj.RegKey
Dim sKey As String
Dim objVal As RegObj.RegValue
Dim sData As String
Dim vData As Variant

sKey = "\HKEY_CURRENT_USER\Software\Microsoft\Windows NT
\CurrentVersion\Devices\"
Set objReg = New RegObj.Registry
Set objRootKey = objReg.RegKeyFromString(sKey)

For Each objVal In objRootKey.Values
If objVal.Name = sPrinterName Then
sData = objVal.Value
Exit For
End If
Next objVal

If Len(sData) 0 Then
vData = Split(sData, ",")
GetPrinterPort = vData(UBound(vData))
Else
GetPrinterPort = ""
End If

Set objReg = Nothing

End Function


Any help welcome
Best regards,
Ludo