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

A little searching around lead me he
http://support.microsoft.com/default.aspx/kb/297292?p=1
it may be that you need the file available there to set the library
reference needed and use your code. Untested - theory only!

"Ludo" wrote:

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
.

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

On 22 apr, 17:42, "Jim Cone" wrote:
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)

<SNIP

Hi all,

Thanks a lot for the proposals.
Just because i don't want to change manual that i would like to do it
full automaticly.
By changing the search criteria a found something that i can use.
see:
http://groups.google.be/group/micros...77945c c740c1

Works great.
Now still need to test in reality.
I hope only that, when changing to another active printer and a slow
network, that the page is printed on the right printer and not to the
previous one due to the slow network connection.

By this, following question:
Is it possible to read the printer status before printing start?

for example:
I change the active printer from printer A to Printer B
I read the 'ready' status from printer B until this one is ready.
When printer B is ready, then print.

any answer welcome.

Best regards,
Ludo