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

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

and one more

application.Dialogs(xlDialogPrinterSetup).Show

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

Sonia,

Here is some code adapted from Randy Birch's site. I have assumed NT/XP< if
you have Win95 post back

If you want to read the comments, go see Randy's site.

Option Explicit

Public Type PRINTER_INFO_1
Flags As Long
prescription As Long
Pane As Long
Comment As Long
End Type

Public Type PRINTER_INFO_4
pPrinterName As Long
pServerName As Long
Attributes As Long
End Type

'SIZEOFxxx are non-windows constants defined for this method
Public Const SIZEOFPRINTER_INFO_1 = 16
Public Const SIZEOFPRINTER_INFO_4 = 12

Public Const PRINTER_LEVEL1 = &H1
Public Const PRINTER_LEVEL4 = &H4

'EnumPrinters enumerates available printers,
'print servers, domains, or print providers.
Public Declare Function EnumPrinters Lib "winspool.drv" _
Alias "EnumPrintersA" _
(ByVal Flags As Long, _
ByVal Name As String, _
ByVal Level As Long, _
pPrinterEnum As Any, _
ByVal cdBuf As Long, _
pcbNeeded As Long, _
pcReturned As Long) As Long

'EnumPrinters Parameters:
Public Const PRINTER_ENUM_DEFAULT = &H1

Public Const PRINTER_ENUM_LOCAL = &H2

Public Const PRINTER_ENUM_CONNECTIONS = &H4

Public Const PRINTER_ENUM_NAME = &H8

Public Const PRINTER_ENUM_REMOTE = &H10

Public Const PRINTER_ENUM_SHARED = &H20

Public Const PRINTER_ENUM_NETWORK = &H40

'PRINTER_INFO_4 returned Attribute values
Public Const PRINTER_ATTRIBUTE_DEFAULT = &H4
Public Const PRINTER_ATTRIBUTE_DIRECT = &H2
Public Const PRINTER_ATTRIBUTE_ENABLE_BIDI = &H800&
Public Const PRINTER_ATTRIBUTE_LOCAL = &H40
Public Const PRINTER_ATTRIBUTE_NETWORK = &H10
Public Const PRINTER_ATTRIBUTE_QUEUED = &H1
Public Const PRINTER_ATTRIBUTE_SHARED = &H8
Public Const PRINTER_ATTRIBUTE_WORK_OFFLINE = &H400

'PRINTER_INFO_1 returned Flag values
Public Const PRINTER_ENUM_CONTAINER = &H8000&
Public Const PRINTER_ENUM_EXPAND = &H4000
Public Const PRINTER_ENUM_ICON1 = &H10000
Public Const PRINTER_ENUM_ICON2 = &H20000
Public Const PRINTER_ENUM_ICON3 = &H40000
Public Const PRINTER_ENUM_ICON4 = &H80000
Public Const PRINTER_ENUM_ICON5 = &H100000
Public Const PRINTER_ENUM_ICON6 = &H200000
Public Const PRINTER_ENUM_ICON7 = &H400000
Public Const PRINTER_ENUM_ICON8 = &H800000

Public Const LB_SETTABSTOPS As Long = &H192

Public Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
(ByVal hwnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
lParam As Any) As Long

Public Declare Function lstrcpyA Lib "kernel32" _
(ByVal RetVal As String, ByVal Ptr As Long) As Long

Public Declare Function lstrlenA Lib "kernel32" _
(ByVal Ptr As Any) As Long



Sub EnumPrintersWin()
Dim Success As Boolean
Dim cbRequired As Long
Dim cbBuffer As Long
Dim pntr() As PRINTER_INFO_4
Dim cEntries As Long
Dim iPrinter As Long
Dim sAttr As String
Dim sMsg As String

Call EnumPrinters(PRINTER_ENUM_CONNECTIONS Or PRINTER_ENUM_LOCAL, _
vbNullString, PRINTER_LEVEL4, _
0, 0, cbRequired, cEntries)

ReDim pntr((cbRequired \ SIZEOFPRINTER_INFO_4))

cbBuffer = cbRequired

If EnumPrinters(PRINTER_ENUM_CONNECTIONS Or PRINTER_ENUM_LOCAL, _
vbNullString, PRINTER_LEVEL4, _
pntr(0), cbBuffer, _
cbRequired, cEntries) Then

For iPrinter = 0 To cEntries - 1

With pntr(iPrinter)

sMsg = sMsg & GetStrFromPtrA(.pPrinterName) & vbCrLf

End With

Next iPrinter

MsgBox sMsg, vbInformation, "List of Printers"
Else
MsgBox "Error enumerating printers."
End If 'EnumPrinters


End Sub


Public Function GetStrFromPtrA(ByVal lpszA As Long) As String

GetStrFromPtrA = String$(lstrlenA(ByVal lpszA), 0)
Call lstrcpyA(ByVal GetStrFromPtrA, ByVal lpszA)

End Function



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

" 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