Prev: multiple crieria searches
Next: Programmatically Adding a Worksheet_Change Event Procedure to a Pr
From: Ludo on 22 Apr 2010 09:44
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 are:
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
From: JLatham on 22 Apr 2010 11:14
A little searching around lead me here:
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 are:
> 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
> .
>
From: Jim Cone on 22 Apr 2010 11:42

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" <ludo.soete(a)scarlet.be>
wrote in message news:07fc11f4-2f25-4ae1-9a65-36c4699c3666(a)c36g2000yqm.googlegroups.com...
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 are:
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
From: Ludo on 23 Apr 2010 06:18
On 22 apr, 17:42, "Jim Cone" <james.cone...(a)comcast.netXXX> 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/microsoft.public.excel.programming/browse_thread/thread/6438507eec4affc8/5be777945cc740c1?lnk=gst&q=printer+port#5be777945cc740c1

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


 | 
Pages: 1
Prev: multiple crieria searches
Next: Programmatically Adding a Worksheet_Change Event Procedure to a Pr