Obtaining a list of network printers

[Aussiebear]

There used to be a complex process to get a listing of the network printers in the early versions of Excel/Word and not having seen anything in the last few years, was wondering how much it might have changed with all the newer versions of VBA and Office. The code below is AI generated and I'm wondering if anyone could improve upon it.



Sub ListNetworkPrinters()
Dim objNetwork As Object
Dim objPrinter As Object
Dim ws As Worksheet
Dim i As Long
' Create a new worksheet to store the printer list
Set ws = ThisWorkbook.Sheets.Add ws.Name = "Network Printers"
' Add headers
ws.Cells(1, 1).Value = "Printer Name"
ws.Cells(1, 2).Value = "Port Name"
ws.Cells(1, 3).Value = "Driver Name"
ws.Cells(1, 4).Value = "Location"
ws.Cells(1, 5).Value = "Shared"
ws.Cells(1, 6).Value = "Network"
' Get the Network object
Set objNetwork = CreateObject("WScript.Network") i = 2
' Start writing printer info from row 2
' Loop through the network printers
For Each objPrinter In objNetwork.EnumPrinterConnections
ws.Cells(i, 1).Value = objPrinter
ws.Cells(i, 2).Value = objNetwork.EnumPrinterConnections(objPrinter) ' Port
' Get WMI object to get driver and location information.
Dim objWMIService As Object
Dim colInstalledPrinters As Object
Dim objInstalledPrinter As Object
Set objWMIService = GetObject("winmgmts:\\.\root\cimv2")
Set colInstalledPrinters = objWMIService.ExecQuery("Select * from Win32_Printer Where Name = '" & Replace(objPrinter, "\", "\\") & "'")
If colInstalledPrinters.Count > 0 Then
For Each objInstalledPrinter In colInstalledPrinters
ws.Cells(i, 3).Value = objInstalledPrinter.DriverName
ws.Cells(i, 4).Value = objInstalledPrinter.Location
ws.Cells(i, 5).Value = objInstalledPrinter.Shared
ws.Cells(i, 6).Value = True
' Indicates network printer, since the connection was enumerated.
Next objInstalledPrinter
Else
ws.Cells(i, 3).Value = "Information Unavailable"
ws.Cells(i, 4).Value = "Information Unavailable"
ws.Cells(i, 5).Value = "Information Unavailable"
ws.Cells(i, 6).Value = True
End If
i = i + 1
Set objWMIService = Nothing
Set colInstalledPrinters = Nothing
Set objInstalledPrinter = Nothing
Next objPrinter
' Clean up objects
Set objNetwork = Nothing
Set objPrinter = Nothing
Set ws = Nothing
MsgBox "Network printer list created successfully!", vbInformation
End Sub

[June7]

Set a reference to Windows Script Host Object Model and following code lists printers my PC has connected:

Dim nwo As New WshNetwork
Dim i As Integer
For i = 0 To (nwo.EnumPrinterConnections.Count / 2) - 1
Debug.Print nwo.EnumPrinterConnections(i * 2 + 1)
Next

But if you want all printers on network, even if the computer is not connected to them, that is likely much more complicated.

Review https://stackoverflow.com/questions/31575572/trouble-listing-printers-with-excel-vba