Setting ActivePrinter based on what is returned by EnumPrinters

[Toh Sain]

I am using Excel 2000 VBA.

Following Microsoft's Knowledge Base article 166008, I can enumerate the
networked printers on my client's XP system using PRINTER_INFO_1 and the
Win32 API EnumPrinters.

See http://support.microsoft.com/default.aspx?scid=kb;EN-US;166008

The problem is that I can't set Application.ActivePrinter to the printers that
are returned by EnumPrinters, correct though they are. This is because
Application.ActivePrinter requires printers named in the form
'<PrinterName> ON <PrinterPort>' and EnumPrinters does not return the
printer port for networked printers.

While recording a macro, I selected all of the printers, and I see a list
of printers such as the following:

"\\Server1Name\HP LaserJet 8000 Series PS on Ne01:"
"\\Server1Name\Xerox Phaser 7700DN on Ne02:"
"\\Server2Name\HP Color LaserJet 8500 on Ne03:"

EnumPrinters never returns the "Ne0?:" portion of these required strings. So
how can I write a function to create the correct strings?

(As a workaround, I have created an .INI file and handwritten all of the
available printers in it, from the macro. I read the .INI file instead of
calling EnumPrinters to populate my drop-down list of available printers.)

 

[Jonathan West]

Hi Toh Sain

This article shows you how to get the port details for any printer so that
you can set the ActivePrinter in Excel.

Controlling the Printer from Word VBA
Part 4: Getting printer driver details
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=183

 

[Toh Sain]

Jonathan, thank you, that was very helpful. It's working for some
printers but not all.

Using my local machine, when I record a macro and set all available
printers, I get a list such as follows:

x Application.ActivePrinter = "Win2PDF on Ne00:"
x Application.ActivePrinter = "HP LaserJet 5Si on Ne01:"
Application.ActivePrinter = "HP LaserJet 5L on LPT1:"
Application.ActivePrinter = "hp deskjet 9600 series on FILE:"
Application.ActivePrinter = "Generic / Text Only on LPT1:"
x Application.ActivePrinter = "Fax on Ne02:"
Application.ActivePrinter = "Epson Stylus Photo 700 ESC/P 2 on LPT1:"

When I enumerate the printers using
GetPrinterDetails(strPrintername).PortName from the article, I get a
list such as follows:

x Win2PDF on PDFFILE:
x HP LaserJet 5Si on PDFFILE:
HP LaserJet 5L on LPT1:
hp deskjet 9600 series on FILE:
Generic / Text Only on LPT1:
x Fax on MSFAX:
Epson Stylus Photo 700 ESC/P 2 on LPT1:

The port names for lines marked with an 'x' differ between the macro
code and the GetPrinterDetails code, and my program cannot select
those printers as the active printer. None of the other members of the
PrinterInfo structure returned by GetPrinterDetails contains the "Ne0?:"
that Excel is expecting. Am I close to getting it right?

TS

 

[Jonathan West]

Jonathan, thank you, that was very helpful. It's working for some
printers but not all.

Using my local machine, when I record a macro and set all available
printers, I get a list such as follows:

x Application.ActivePrinter = "Win2PDF on Ne00:"
x Application.ActivePrinter = "HP LaserJet 5Si on Ne01:"
Application.ActivePrinter = "HP LaserJet 5L on LPT1:"
Application.ActivePrinter = "hp deskjet 9600 series on FILE:"
Application.ActivePrinter = "Generic / Text Only on LPT1:"
x Application.ActivePrinter = "Fax on Ne02:"
Application.ActivePrinter = "Epson Stylus Photo 700 ESC/P 2 on LPT1:"

When I enumerate the printers using
GetPrinterDetails(strPrintername).PortName from the article, I get a
list such as follows:

x Win2PDF on PDFFILE:
x HP LaserJet 5Si on PDFFILE:
HP LaserJet 5L on LPT1:
hp deskjet 9600 series on FILE:
Generic / Text Only on LPT1:
x Fax on MSFAX:
Epson Stylus Photo 700 ESC/P 2 on LPT1:

The port names for lines marked with an 'x' differ between the macro
code and the GetPrinterDetails code, and my program cannot select
those printers as the active printer. None of the other members of the
PrinterInfo structure returned by GetPrinterDetails contains the "Ne0?:"
that Excel is expecting. Am I close to getting it right?

I adapted that code from a rather larger class written in VB by Karl
Peterson, which you can see if you visit his website www.mvps.org/vb/, I'm
going to ping him and see if he can join the conversation here.

In the meantime, can you rpovide any further information about what kinds of
ports these "Ne0?" interfaces are? Are they network connections? It might
help us work out what is going on.

 

[jaf]

Hi Jonathan,
Ne01 etc. is a network print server. Like these
http://h10010.www1.hp.com/wwpc/us/en/sm/WF02a/18972-236253-64302.html

Toh,
Have you tried VBA's LIKE operator?

Application.ActivePrinter = "HP LaserJet 5L on LPT1:" like "HP LaserJet
5Si*"


--
John
johnf 202 at hotmail dot com


|
| | > Jonathan, thank you, that was very helpful. It's working for some
| > printers but not all.
| >
| > Using my local machine, when I record a macro and set all available
| > printers, I get a list such as follows:
| >
| > x Application.ActivePrinter = "Win2PDF on Ne00:"
| > x Application.ActivePrinter = "HP LaserJet 5Si on Ne01:"
| > Application.ActivePrinter = "HP LaserJet 5L on LPT1:"
| > Application.ActivePrinter = "hp deskjet 9600 series on FILE:"
| > Application.ActivePrinter = "Generic / Text Only on LPT1:"
| > x Application.ActivePrinter = "Fax on Ne02:"
| > Application.ActivePrinter = "Epson Stylus Photo 700 ESC/P 2 on
LPT1:"
| >
| > When I enumerate the printers using
| > GetPrinterDetails(strPrintername).PortName from the article, I get a
| > list such as follows:
| >
| > x Win2PDF on PDFFILE:
| > x HP LaserJet 5Si on PDFFILE:
| > HP LaserJet 5L on LPT1:
| > hp deskjet 9600 series on FILE:
| > Generic / Text Only on LPT1:
| > x Fax on MSFAX:
| > Epson Stylus Photo 700 ESC/P 2 on LPT1:
| >
| > The port names for lines marked with an 'x' differ between the macro
| > code and the GetPrinterDetails code, and my program cannot select
| > those printers as the active printer. None of the other members of the
| > PrinterInfo structure returned by GetPrinterDetails contains the "Ne0?:"
| > that Excel is expecting. Am I close to getting it right?
|
| I adapted that code from a rather larger class written in VB by Karl
| Peterson, which you can see if you visit his website www.mvps.org/vb/, I'm
| going to ping him and see if he can join the conversation here.
|
| In the meantime, can you rpovide any further information about what kinds
of
| ports these "Ne0?" interfaces are? Are they network connections? It might
| help us work out what is going on.
|
|
| --
| Regards
| Jonathan West - Word MVP
| http://www.multilinker.com
| Please reply to the newsgroup
|
|
|

 

[Toh Sain]

I adapted that code from a rather larger class written in VB by Karl
Peterson, which you can see if you visit his website www.mvps.org/vb/, I'm
going to ping him and see if he can join the conversation here.

In the meantime, can you rpovide any further information about what kinds of
ports these "Ne0?" interfaces are? Are they network connections? It might
help us work out what is going on.


--
Regards
Jonathan West - Word MVP
http://www.multilinker.com
Please reply to the newsgroup

Well, on the system at the client, the NE0? printers are indeed networked. But
on my home computer, I have no networked printers. Fax is just the Windows
2000 fax printer, and the other ones are printing to Win2PDF, I believe.

TS

 

[Toh Sain]

I'm not familiar with this construct, but when I did this,
at the line in question, there is the following message:

Run-time error 1004:
Method 'ActivePrinter' of object '_Application' failed.

Here is the whole macro. Only the last line failed.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 5/31/2004 by Toh Sain
'
Application.ActivePrinter = "Epson Stylus Photo 700 ESC/P 2 on LPT1:"
Application.ActivePrinter = "Fax on Ne02:"
Application.ActivePrinter = "Generic / Text Only on LPT1:"
Application.ActivePrinter = "hp deskjet 9600 series on FILE:"
Application.ActivePrinter = "HP LaserJet 5L on LPT1:"
Application.ActivePrinter = "HP LaserJet 5Si on Ne01:"
Application.ActivePrinter = "Win2PDF on Ne00:"
Application.ActivePrinter = "HP LaserJet 5L on LPT1:" Like "HP LaserJet 5Si*"
End Sub

 

[Jonathan West]

Well, on the system at the client, the NE0? printers are indeed networked. But
on my home computer, I have no networked printers. Fax is just the Windows
2000 fax printer, and the other ones are printing to Win2PDF, I believe.

TS

I've had an email exchange with Karl Peterson, and his response was as
follows

"What's interesting is the Ne0x portnames. I remember those from back in
the NT3.5x days and even Win3.x, but can't say I've seen those in years now.
They're likely most easily retrieved from HKCU\Software\Microsoft\Windows
NT\CurrentVersion\Devices. I think that section is directly mapped to the
GetProfileString... Yes, it is. See the attached module. This appears to
create the list he's looking for, when I run it here."

Then he included the following code - hope it helps! (Some lines might wrap
in the message, so you might need to reconstitute them when you paste it
into your project)


*************************************************************************
' Copyright ©2004 Karl E. Peterson
' All Rights Reserved, http://www.mvps.org/vb
' *************************************************************************
' You are free to use this code within your own applications, but you
' are expressly forbidden from selling or otherwise distributing this
' source code, non-compiled, without prior written consent.
' *************************************************************************
Option Explicit

' Win32 API declarations
Private Declare Function GetProfileString Lib "kernel32" Alias
"GetProfileStringA"
(ByVal lpAppName As String, ByVal lpKeyName As Any, ByVal lpDefault As
String, ByVal
lpReturnedString As String, ByVal nSize As Long) As Long

Public Sub DumpPrinterList()
Dim Buffer As String
Dim BufSize As Long
Dim nChars As Long
Dim Devices() As String
Dim NetPort As String
Dim i As Long

' VB5 fails because it never expands this value!
' Start with a reasonably sized buffer.
BufSize = 512

' Attempt to get list of installed printers
' by looping until successful.
Do
Buffer = Space$(BufSize)
nChars = GetProfileString("Devices", vbNullString, "", Buffer,
BufSize)
If nChars = (BufSize - 2) Then
' MSDN: If either lpAppName or lpKeyName is NULL and
' the supplied destination buffer is too small to hold
' all the strings, the last string is truncated and
' followed by two null characters. In this case, the
' return value is equal to nSize minus two.
BufSize = BufSize * 2
ElseIf nChars = 0 Then
' The call failed entirely.
Exit Do
Else
' We got a reasonable return.
Exit Do
End If
Loop

' Build a list compatible with Application.ActivePrinter?
Call ExtractStringZ(Buffer, Devices())
For i = LBound(Devices) To UBound(Devices)
nChars = GetProfileString("Devices", Devices(i), "", Buffer, BufSize)
NetPort = Mid$(TrimNull(Buffer), InStr(Buffer, ",") + 1)
Debug.Print Devices(i); " on "; NetPort
Next i
End Sub

' *********************************************
' Private Methods
' *********************************************
Private Function ExtractStringZ(Buffer As String, OutArray() As String) As
Long
Dim StartPos As Long
Dim NullPos As Long
Dim BuffLen As Long
Dim Elements As Long

' Extract null terminated strings from large
' double-null terminated buffer.
StartPos = 1
Elements = 0
BuffLen = Len(Buffer)

' Loop through buffer looking for nulls.
Do While StartPos < BuffLen
NullPos = InStr(StartPos, Buffer, vbNullChar)
If NullPos = StartPos Then
' We've hit the double-null terminator.
Exit Do
Else
' Expand array, store new substring, and
' increment counters.
ReDim Preserve OutArray(0 To Elements) As String
OutArray(Elements) = Mid$(Buffer, StartPos, NullPos - StartPos)
StartPos = NullPos + 1
Elements = Elements + 1
End If
Loop

' Return number of substrings found.
ExtractStringZ = Elements
End Function

Private Function TrimNull(ByVal StrIn As String) As String
Dim nul As Long

' Truncate input string at first null.
' If no nulls, perform ordinary Trim.
nul = InStr(StrIn, vbNullChar)
Select Case nul
Case Is > 1
TrimNull = Left$(StrIn, nul - 1)
Case 1
TrimNull = ""
Case 0
TrimNull = Trim$(StrIn)
End Select
End Function