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/...with-excel-vba