I need to enumerate printers to get their properties (I'm looking for the
Generic Text ones only). How can I do this in Excel XP VBA?

Thanks,
Mike Ober.

Not sure how you tell the generic text ones:

http://support.microsoft.com/?ID=166008
ACC: Enumerating Local and Network Printers

Enumerating Windows' Available Ports
http://www.mvps.org/vbnet/code/enums/enumports.htm
=======================


This posting by Jim Rech may be useful as well - certainly simpler:

From: "Jim Rech"
Subject: Setting active printers will Excel 97 VBA
Date: Thu, 19 Oct 2000 14:04:56 -0400
Lines: 9
Newsgroups: microsoft.public.excel.programming

This macro enumerates printers and their connections. Parsing it you may be
able to construct the syntax ActivePrinter wants:

Sub a()
Set WshNetwork = CreateObject("WScript.Network")
Set oDrives = WshNetwork.EnumNetworkDrives
Set oPrinters = WshNetwork.EnumPrinterConnections
For i = 0 To oPrinters.Count - 1 Step 2
Debug.Print "Port " & oPrinters.Item(i) & " = " & _
oPrinters.Item(i + 1)
Next
End Sub


--
Jim Rech
Excel MVP
--------------------------


Code posted by Steven Kelder:

Option Explicit
Public WshNetwork As Object
Public oPrinters As Variant
Public oDrives As Variant

Sub ShowConnections()
Dim I As Integer
Set WshNetwork = CreateObject("WScript.Network")
Set oDrives = WshNetwork.EnumNetworkDrives
Set oPrinters = WshNetwork.EnumPrinterConnections

MsgBox "Printers:"
For I = 0 To oPrinters.Count - 1 Step 2
MsgBox "Port " & oPrinters.Item(I) & " = " & oPrinters.Item(I + 1)
Next

MsgBox "Drives:"
For I = 0 To oDrives.Count - 1 Step 2
MsgBox "Drive " & oDrives.Item(I) & " = " & oDrives.Item(I + 1)
Next

End Sub



In Windows NT:
when setting ActivePrinter, you have to
lookup in the registry (
HKEY_CURRENT_USER\Software\Microsoft\Windows NT\CurrentVersion\PrinterPorts
) to get the "port name" that Excel
expects (which has nothing to do with the actual port name of the
printer!).

--
Regards,
Tom Ogilvy

"Michael D. Ober" <[email protected] wrote in message
...
I need to enumerate printers to get their properties (I'm looking for the
Generic Text ones only). How can I do this in Excel XP VBA?

Thanks,
Mike Ober.

It doesn't need to be that complicated..


I've written this little function.. which returns an array
of the complete (internationalized) printer strings (as installed on the
active computer) ready for use with the active printer in Excel

Note the ActivePrinter string is an odd bag:
32 bit printer name and 16 bit port name plus the word "on" (which needs
to be translated for localized Excel versions... to "AUF" "OP" ?? )

This one is written for vba6 and uses the split command.
It woud need a minor rewrite for use in excel97.



Option Explicit

Private Declare Function GetProfileString Lib "kernel32" _
Alias "GetProfileStringA" _
(ByVal lpAppName As String, ByVal lpKeyName As String, _
ByVal lpDefault As String, ByVal lpReturnedString As String, _
ByVal nSize As Long) As Long

Sub showlist()
MsgBox Join(PrinterList, vbNewLine)
End Sub


Function PrinterList()
Dim lRet As Long
Dim sBuffer As String
Dim lSize As Long
Dim avTmp As Variant
Dim aPrn() As String
Dim n%, sPrn$, sConn$, sPort$

'Get localized Connection string
avTmp = Split(Excel.ActivePrinter)
sConn = " " & avTmp(UBound(avTmp) - 1) & " "
'Get Printers
lSize = 1024
sBuffer = Space(lSize)
lRet = GetProfileString("devices", vbNullString, vbNullString, _
sBuffer, lSize)
sBuffer = Left(sBuffer, lRet)
avTmp = Split(sBuffer, Chr(0))

ReDim Preserve avTmp(UBound(avTmp) - 1)
For n = 0 To UBound(avTmp)
lSize = 128
sBuffer = Space(lSize)
lRet = GetProfileString("devices", avTmp(n), vbNullString, _
sBuffer, lSize)
sPort = Mid(sBuffer, InStr(sBuffer, ",") + 1, _
lRet - InStr(sBuffer, ","))
avTmp(n) = avTmp(n) & sConn & sPort
Next
PrinterList = avTmp
End Function



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Tom Ogilvy" wrote:

Not sure how you tell the generic text ones:

http://support.microsoft.com/?ID=166008
ACC: Enumerating Local and Network Printers

Enumerating Windows' Available Ports
http://www.mvps.org/vbnet/code/enums/enumports.htm
=======================


This posting by Jim Rech may be useful as well - certainly simpler:

From: "Jim Rech"
Subject: Setting active printers will Excel 97 VBA
Date: Thu, 19 Oct 2000 14:04:56 -0400
Lines: 9
Newsgroups: microsoft.public.excel.programming

This macro enumerates printers and their connections. Parsing it you
may be able to construct the syntax ActivePrinter wants:

Sub a()
Set WshNetwork = CreateObject("WScript.Network")
Set oDrives = WshNetwork.EnumNetworkDrives
Set oPrinters = WshNetwork.EnumPrinterConnections
For i = 0 To oPrinters.Count - 1 Step 2
Debug.Print "Port " & oPrinters.Item(i) & " = " & _
oPrinters.Item(i + 1)
Next
End Sub


--
Jim Rech
Excel MVP
--------------------------


Code posted by Steven Kelder:

Option Explicit
Public WshNetwork As Object
Public oPrinters As Variant
Public oDrives As Variant

Sub ShowConnections()
Dim I As Integer
Set WshNetwork = CreateObject("WScript.Network")
Set oDrives = WshNetwork.EnumNetworkDrives
Set oPrinters = WshNetwork.EnumPrinterConnections

MsgBox "Printers:"
For I = 0 To oPrinters.Count - 1 Step 2
MsgBox "Port " & oPrinters.Item(I) & " = " & oPrinters.Item(I +
1)
Next

MsgBox "Drives:"
For I = 0 To oDrives.Count - 1 Step 2
MsgBox "Drive " & oDrives.Item(I) & " = " & oDrives.Item(I + 1)
Next

End Sub



In Windows NT:
when setting ActivePrinter, you have to
lookup in the registry (
HKEY_CURRENT_USER\Software\Microsoft\Windows
NT\CurrentVersion\PrinterPorts ) to get the "port name" that Excel
expects (which has nothing to do with the actual port name of the
printer!).

Using your technique for internationalization, here is an adaptation of Jim
Rech's code. Should be simple enough.

Sub b()
#If VBA6 Then
avTmp = Split(Excel.ActivePrinter, " ")
#Else
avTmp = Split97(Excel.ActivePrinter, " ")
#End If
sConn = " " & avTmp(UBound(avTmp) - 1) & " "
Set WshNetwork = CreateObject("WScript.Network")
Set oPrinters = WshNetwork.EnumPrinterConnections
For i = 0 To oPrinters.Count - 1 Step 2
Debug.Print oPrinters.Item(i + 1) & _
sConn & oPrinters.Item(i)
Next
End Sub


Function Split97(sStr As String, sdelim As String) As Variant
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function

--
Regards,
Tom Ogilvy



keepitcool wrote in message
...

It doesn't need to be that complicated..


I've written this little function.. which returns an array
of the complete (internationalized) printer strings (as installed on the
active computer) ready for use with the active printer in Excel

Note the ActivePrinter string is an odd bag:
32 bit printer name and 16 bit port name plus the word "on" (which needs
to be translated for localized Excel versions... to "AUF" "OP" ?? )

This one is written for vba6 and uses the split command.
It woud need a minor rewrite for use in excel97.



Option Explicit

Private Declare Function GetProfileString Lib "kernel32" _
Alias "GetProfileStringA" _
(ByVal lpAppName As String, ByVal lpKeyName As String, _
ByVal lpDefault As String, ByVal lpReturnedString As String, _
ByVal nSize As Long) As Long

Sub showlist()
MsgBox Join(PrinterList, vbNewLine)
End Sub


Function PrinterList()
Dim lRet As Long
Dim sBuffer As String
Dim lSize As Long
Dim avTmp As Variant
Dim aPrn() As String
Dim n%, sPrn$, sConn$, sPort$

'Get localized Connection string
avTmp = Split(Excel.ActivePrinter)
sConn = " " & avTmp(UBound(avTmp) - 1) & " "
'Get Printers
lSize = 1024
sBuffer = Space(lSize)
lRet = GetProfileString("devices", vbNullString, vbNullString, _
sBuffer, lSize)
sBuffer = Left(sBuffer, lRet)
avTmp = Split(sBuffer, Chr(0))

ReDim Preserve avTmp(UBound(avTmp) - 1)
For n = 0 To UBound(avTmp)
lSize = 128
sBuffer = Space(lSize)
lRet = GetProfileString("devices", avTmp(n), vbNullString, _
sBuffer, lSize)
sPort = Mid(sBuffer, InStr(sBuffer, ",") + 1, _
lRet - InStr(sBuffer, ","))
avTmp(n) = avTmp(n) & sConn & sPort
Next
PrinterList = avTmp
End Function



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Tom Ogilvy" wrote:

Not sure how you tell the generic text ones:

http://support.microsoft.com/?ID=166008
ACC: Enumerating Local and Network Printers

Enumerating Windows' Available Ports
http://www.mvps.org/vbnet/code/enums/enumports.htm
=======================


This posting by Jim Rech may be useful as well - certainly simpler:

From: "Jim Rech"
Subject: Setting active printers will Excel 97 VBA
Date: Thu, 19 Oct 2000 14:04:56 -0400
Lines: 9
Newsgroups: microsoft.public.excel.programming

This macro enumerates printers and their connections. Parsing it you
may be able to construct the syntax ActivePrinter wants:

Sub a()
Set WshNetwork = CreateObject("WScript.Network")
Set oDrives = WshNetwork.EnumNetworkDrives
Set oPrinters = WshNetwork.EnumPrinterConnections
For i = 0 To oPrinters.Count - 1 Step 2
Debug.Print "Port " & oPrinters.Item(i) & " = " & _
oPrinters.Item(i + 1)
Next
End Sub


--
Jim Rech
Excel MVP
--------------------------


Code posted by Steven Kelder:

Option Explicit
Public WshNetwork As Object
Public oPrinters As Variant
Public oDrives As Variant

Sub ShowConnections()
Dim I As Integer
Set WshNetwork = CreateObject("WScript.Network")
Set oDrives = WshNetwork.EnumNetworkDrives
Set oPrinters = WshNetwork.EnumPrinterConnections

MsgBox "Printers:"
For I = 0 To oPrinters.Count - 1 Step 2
MsgBox "Port " & oPrinters.Item(I) & " = " & oPrinters.Item(I +
1)
Next

MsgBox "Drives:"
For I = 0 To oDrives.Count - 1 Step 2
MsgBox "Drive " & oDrives.Item(I) & " = " & oDrives.Item(I + 1)
Next

End Sub



In Windows NT:
when setting ActivePrinter, you have to
lookup in the registry (
HKEY_CURRENT_USER\Software\Microsoft\Windows
NT\CurrentVersion\PrinterPorts ) to get the "port name" that Excel
expects (which has nothing to do with the actual port name of the
printer!).

Since I'n not on a network... ERROR
-2147023674 Method 'EnumPrinterConnections' of object 'IWshNetwork2'
failed


Why not write the conditional compile like:

Sub b()
On Error GoTo errH:
avTmp = Split(Excel.ActivePrinter, " ")
sConn = " " & avTmp(UBound(avTmp) - 1) & " "
Set WshNetwork = CreateObject("WScript.Network")
Set oPrinters = WshNetwork.EnumPrinterConnections
For i = 0 To oPrinters.Count - 1 Step 2
Debug.Print oPrinters.Item(i + 1) & _
sConn & oPrinters.Item(i)
Next
Exit Sub
errH:
Debug.Print Err; Err.Description
End Sub

#If Not VBA6 Then
Function Split(sStr As String, sdelim As String) As Variant
Split = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function
#End If


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Tom Ogilvy" wrote:

Sub b()
#If VBA6 Then
avTmp = Split(Excel.ActivePrinter, " ")
#Else
avTmp = Split97(Excel.ActivePrinter, " ")
#End If
sConn = " " & avTmp(UBound(avTmp) - 1) & " "
Set WshNetwork = CreateObject("WScript.Network")
Set oPrinters = WshNetwork.EnumPrinterConnections
For i = 0 To oPrinters.Count - 1 Step 2
Debug.Print oPrinters.Item(i + 1) & _
sConn & oPrinters.Item(i)
Next
End Sub


Function Split97(sStr As String, sdelim As String) As Variant
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function

I am not on a network and no error for me. I am connected to the internet,
so perhaps that is the difference, but I would think you are as well.

I am happy with the conditional compilation the way it is.

--
Regards,
Tom Ogilvy


keepitcool wrote in message
...

Since I'n not on a network... ERROR
-2147023674 Method 'EnumPrinterConnections' of object 'IWshNetwork2'
failed


Why not write the conditional compile like:

Sub b()
On Error GoTo errH:
avTmp = Split(Excel.ActivePrinter, " ")
sConn = " " & avTmp(UBound(avTmp) - 1) & " "
Set WshNetwork = CreateObject("WScript.Network")
Set oPrinters = WshNetwork.EnumPrinterConnections
For i = 0 To oPrinters.Count - 1 Step 2
Debug.Print oPrinters.Item(i + 1) & _
sConn & oPrinters.Item(i)
Next
Exit Sub
errH:
Debug.Print Err; Err.Description
End Sub

#If Not VBA6 Then
Function Split(sStr As String, sdelim As String) As Variant
Split = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function
#End If


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Tom Ogilvy" wrote:

Sub b()
#If VBA6 Then
avTmp = Split(Excel.ActivePrinter, " ")
#Else
avTmp = Split97(Excel.ActivePrinter, " ")
#End If
sConn = " " & avTmp(UBound(avTmp) - 1) & " "
Set WshNetwork = CreateObject("WScript.Network")
Set oPrinters = WshNetwork.EnumPrinterConnections
For i = 0 To oPrinters.Count - 1 Step 2
Debug.Print oPrinters.Item(i + 1) & _
sConn & oPrinters.Item(i)
Next
End Sub


Function Split97(sStr As String, sdelim As String) As Variant
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function

Michael,

I reacted to Tom's reply rather then read your post.

So my printerList function is probably NOT what you need.

But if you really need to manipulate printer settings
(in the registry) you'll probably need plenty of API handling.


Have a look at my MultiTrayPrint addin.

It doesnt show all of what it does behind the scenes...
as it was an exercise in API and a hierarchy of class modules.
Printers/Printer/Paperbins/PaperSize

Probable does a lot of what you need.
EnumPrinters/Devmode etc..

Code is open. Credits / comments/ criticism appreciated.


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Michael D. Ober" <[email protected] wrote:

I need to enumerate printers to get their properties (I'm looking for
the
Generic Text ones only). How can I do this in Excel XP VBA?

Thanks,
Mike Ober.