Find Windows 2000 Network Printer Name

[Leslie]

I'm having a similar problem to others on proper printer
name and selection under VBA control.

The user's computer may have several different printer
setups, some local and some network. Under Windows 2000
Pro, each printer connection is identified with a 'on
NeXX:' suffix on the printer name. As a user installs
new printers, these NeXX numbers will change (at reboot).

I need to be able to set the active printer on any user's
system to a specific system printer name beginning
with 'HPLJ 8150'. What I don't know for each user is
which 'on NeXX:' suffix will apply. If I don't provide
the full specification including the appropriate suffix,
my printing macro will bomb.

Can Excel 2000 VBA extract an array or collection of the
full printer names available on a user's system. If so,
how? If not, is there another way to accomplish this?

Thanks,

Leslie

[Dick Kusleika]

Leslie

Here's a macro that was provided in a previous post by Jim Rech. It
displays a message box for each printer.

Dim WshNetwork As Object
Dim oDrives As Variant
Dim oPrinters As Variant
Dim I As Integer

Set WshNetwork = CreateObject("WScript.Network")
Set oDrives = WshNetwork.EnumNetworkDrives
Set oPrinters = WshNetwork.EnumPrinterConnections
For I = 0 To oPrinters.Count - 1 Step 2

MsgBox oPrinters.Item(I + 1) & " on " & oPrinters.Item(I)
Next

--
Dick Kusleika
MVP - Excel

Post all replies to the newsgroup.

"Leslie" <V...@innovum.net> wrote in message
news:013001c29a95$680f4130$8bf82ecf@TK2MSFTNGXA05...

[Leslie Scott]

Dick,

Thanks for the reply. That routine was informative, but not the same
information that I am looking for.

I am looking for the WinNT,Win2000,WinXP network connection ID (I don't
know if that's the right name).

For instance, when I run the routine you sent, my current printer is:
'HP DeskJet 990C Series on USB001'

However, I need to obtain the network identifier for the same printer as
follows:
'HP DeskJet 990C Series on Ne03:'

Any suggestions for trapping these identifiers???

Thanks again!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Dick Kusleika]

Leslie

I don't have Win2000, so this advice may be way off base. What I was
thinking was that you could test the first, say, 15 characters of the
printer name and set your ActivePrinter to that. But I see that the printer
name and the printer connection are separate in Jim's example. Anyway, I
think you could do something like this

Sub test()

Dim WshNetwork As Object

Dim oPrinters As Variant
Dim I As Integer

Set WshNetwork = CreateObject("WScript.Network")

Set oPrinters = WshNetwork.EnumPrinterConnections
For I = 0 To oPrinters.Count - 1 Step 2

If oPrinters.Item(I + 1) = "Phaser 860DP" Then
ActivePrinter = oPrinters.Item(I + 1) & " on " & _
oPrinters.Item(I)
End If
Next I

Debug.Print ActivePrinter

End Sub

Here it doesn't matter what the connection to Phaser 860DP is. Once the
code finds a printer with that name, it sets the active printer to that
printer on whichever connection is listed. You check oPrinters.Item(I+1)
and accept whichever oPrinters.Item(I) is associated with that printer.

Try this code on a couple of different machines (or after a couple of
reboots) and see if it picks it up correctly.

--
Dick Kusleika
MVP - Excel

Post all replies to the newsgroup.

"Leslie Scott" <v...@innovum.net> wrote in message
news:eewNUy1nCHA.2396@TK2MSFTNGP08...

[Rob Bouws]

I have been struggling with Leslie's problem also.

Dick, I tried your latest code. It doesn't work because the port
(oPrinters.Item(I)) that the Wscript.network object returns is different
than this mysterious NE0? identifier.

Leslie, please send me an email if you figure something out.

Thanks,
Rob

[Dick Kusleika]

Rob

Thanks for that information. I don't have 2000, so I was kind of guessing.
Hopefully Leslie will find an answer, but I will keep my eyes open.

--
Dick Kusleika
MVP - Excel

Post all replies to the newsgroup.

"Rob Bouws" <rob....@extolinc.com> wrote in message
news:On7CG0SoCHA.2596@TK2MSFTNGP08...

[jaf]

Hi Rob,
The Ne03: shows the network printer is connected to a print server.
"Ne03:" is the print servers name.

--
John
johnf202 at hotmail.com

"Rob Bouws" <rob....@extolinc.com> wrote in message
news:On7CG0SoCHA.2596@TK2MSFTNGP08...
>

[Dick Kusleika]

Rob and Leslie

You may want to read these

http://groups.google.com/groups?&threadm=txgS7.45882%24wL4.318168%40rwcrnsc51

http://groups.google.com/groups?&threadm=OR%24NGiPjCHA.2456%40tkmsftngp12

--
Dick Kusleika
MVP - Excel

Post all replies to the newsgroup.

"Rob Bouws" <rob....@extolinc.com> wrote in message
news:On7CG0SoCHA.2596@TK2MSFTNGP08...
>

[Rob Bouws]

Dick,

The second link was very helpful.

[Leslie Scott]

Here is what I have been able to put together. I went to Microsoft for
help claiming that it was an O/S problem. They told me they don't offer
programming services through tech support. I complained that I was not
asking for them to write me a routine, but only inform how to access the
printer information under Win2000. I had not had this problem before
the users machines were upgraded from Win98 to Win2000. Tech support
did inform me that there was no Windows query or property that would
return the printer information including the suffix in a list that I
could step through. They suggested that the only way to do so was trial
and error using the Applciation.ActivePrinter property and sent a sample
routine. With some modifications their routine is the only solution
that I have found.

'The following routines demonstrate a method of obtaining the
Network Connection ID
'(NCid) setting for printers under Windows NT, Windows 2000, and
Windows XP. Under these
'operating systems, printers must be identified by their name and a
special NCid
'represented in the form 'on NeXX:' where XX is the sequential
number
'of the NCid.
'
'Two problems are encountered when writing VBA routines that require
printing to a
'specific printer. The first problem is identifying the NCid
associated with the printer
'that you want to use on the specific machine from which the
application is run. The
'NCid may be different for different users on the same network if
those users do not have
'exactly the same quantity and model of printers set up on their
machine. An impossible
'situation for a programmer. The second problem is that if any user
adds a printer to
'his/her machine, the NCid for the target printer may change since
the O/S resets the
'NCid numbers at bootup depending on a number of unknown factors.
'
'This routine is the only known method (although not a very pretty
one) to trap and set
'a particular printer as the active printer for Excel. The first
routine below is just
'set up as an example of how the 'SetPrinter' routine could be used
in code. The second
'routine actually sets the active printer to the target printer.
'
'
'
'NOTE: This routine is designed around Excel VBA. Other versions of
VBA may require
'different methods to locate and set the desired printer. The
routine is built
'around the 'Application.ActivePrinter' property of Excel.
'
'

Sub SetTheProperSequence()
' This macro calls the SetPrinter macro to retrieve the printer
number.

Dim CurPRINTER As String
'this variable will hold the currently active printer so that we can
'reset the active printer pointer to this printer when complete

CurPRINTER = Application.ActivePrinter
'set the current printer network name (including the 'on NeXX:'
suffix)

MsgBox "Active Printer is set to:" & vbLf & CurPRINTER
'Display current printer setting (includes 'on NeXX:' suffix)

SetPrinter ("CAPTURE FAX BVRP")
'call the SetPrinter Subroutine and set the active printer to
'CAPTURE FAX BVRP'
'where 'CAPTURE FAX BVRP' is the printer name argument for the
subroutine

'NOTE: in this sub call, the 'on NeXX:' suffix is not necessary
since this
' routine will try this name with the various printer
specifications
' that are currently established on the network. The printer
name
' argument must be the full printer name (except for the 'on
NeXX:'
' suffix) exactly as set on the network

'******* MORE CODE HERE *************



SetPrinter ("HP LaserJet 8150 Series PCL")
'call the SetPrinter Subroutine and set the active printer to 'HP
LaserJet 8150 Series PCL'
'where 'HP LaserJet 8150 Series PCL' is the printer name argument
for the subroutine

'******* MORE CODE HERE *************




Application.ActivePrinter = CurPRINTER
'Reset the printer to the printer that was current before changing
printers

MsgBox "Active Printer is set to:" & vbLf &
Application.ActivePrinter
'Display current printer setting (includes 'on NeXX:' suffix)


End Sub

Sub SetPrinter(TARGET As String)
'this routine will try to set the active printer to the specified
'TARGET' name
'and the additional 'on NeXX:' suffix. For each attempt to set the
active printer
'to an invalid network connection ID, the routine will just continue
to the next
'option
'

On Error Resume Next
'if the TARGET name and suffix guess is invalid, go to next
iteration

For i = 0 To 10
'this routine will attempt 11 times to set the active printer. If
your network has
'more network connection IDs, set the upper bound of the 'For'
statement to handle
'all possible connections on your network.

Application.ActivePrinter = TARGET & " on Ne" & Format(i, "00")
& ":"
' attempt to set the Active Printer to the TARGET name with a
suffix of 'on NeXX:'
' where the XX is filled in by the value of the For/Next loop
' If unsuccessful, the Resume Next command directs VBA to
continue with the
' next iteration

Next

'After all of the attempts of the For/Next loop, only one will be
successful. The
'ActivePrinter will be set to that printer.



MsgBox "Active Printer is set to:" & vbLf &
Application.ActivePrinter

On Error GoTo 0

End Sub

Leslie Scott
innovum
V...@innovum.net

[Leslie Scott]

P.S. I posted my last message before reading the message from Dick
Kusleika. The second link in the Dick's message provides a solution
similar to mine. One difference is that my routines do not use the full
network path of the printer. Instead, in my routines the printers are
identified by the designated name rather than the full network path.

I have not tested the full path name style in my routines. Individual
users may have to try each to determine which works for their particular
application and network/OS configuration.

Dick, thanks for your help in tracking down an answer.

[Dick Kusleika]

Leslie

You're welcome. Thank you for posting a follow up so that everyone can
benefit from your suffering.

--
Dick Kusleika
MVP - Excel

Post all replies to the newsgroup.

"Leslie Scott" <v...@innovum.net> wrote in message
news:udr#EE7rCHA.1656@TK2MSFTNGP09...