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
=======================

Posting by KeepItcool

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




================================

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

" wrote in
message ...
is there a way to retrive the list of available printers
in a macro, so as to display the list in a msgbox.

Thanks
Sonia