List Network Printers

[desktopace]

I have Excel worksheets that need printed, but from different printers. I am trying to get a listing of the Networked printers for that specific workstation that is being used. I need to know the printer name and port # in order to change the printer being used for that specific worksheet. I am trying to make this simple for the user as opposed to changing the printer every time that print a worksheet that needs to be from a different Network printer. I have the VBA to change the Active printer, but without the full port #, it comes up as a Run-time error '1004':.

The VBA Code I am currently trying to get the printers displayed to, and then use those actual cells to populate the change printer is...

  Sub ListNetworkPrinters()
     Dim WshNetwork As Object
     Dim objPrinters As Variant
     Dim i As Integer
     Dim ws As Worksheet
     Dim row As Integer
     ' Create a new worksheet to list the printers
     Set ws = ThisWorkbook.Sheets.Add
     ws.Name = "Network Printers"
     ws.Cells(1, 1).Value = "Printer Name"
    ws.Cells(1, 2).Value = "Port"
     ' Initialize the WScript.Network object
     Set WshNetwork = CreateObject("WScript.Network")
     objPrinters = WshNetwork.EnumPrinterConnections
     ' Loop through the printers and list them in the worksheet
     row = 2
     For i = 0 To objPrinters.Count - 1 Step 2
         ws.Cells(row, 1).Value = objPrinters.Item(i + 1)
         ws.Cells(row, 2).Value = objPrinters.Item(i)
         row = row + 1
     Next i
     ' Autofit the columns for better readability
     ws.Columns("A:B").AutoFit
     ' Inform the user that the process is complete
     MsgBox "Network printers have been listed in the 'Network Printers' sheet.", vbInformation
  End Sub

If I can get this to work, I can modify it to overwrite the printer names in the worksheet I have, without having to create a worksheet every time the Sub is run.
Thank you so much for any help you can offer.
Kenny

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

List All Network Printers - OzGrid Free Excel/VBA Help Forum

https://www.mrexcel.com/board/threads/list-network-printers.1264292/ I have Excel worksheets that need printed, but from different printers. I am trying to get a listing of the Networked printers for that specific workstation that is being used. I need…

forum.ozgrid.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.