So here's the original problem:
I have an Excel file with a button that runs a macro. This macro needs to print the sheet to 2 separate network printers. The workbook will be run on multiple different computers on the network.
My current code looks like so:
Application.ActivePrinter = "Printer-A on Ne02:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
Application.ActivePrinter = "Printer-B on Ne05:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
The problem lies with the on Ne02:
and on Ne05:
. These values differ from one computer to another. E.g.:
- Printer-A on Ne03:
- Printer-B on Ne02:
- Printer-A on Ne07:
- etc
I can circumvent the issue by displaying the "select a printer" dialog and forcing users to choose the printer manually, but I would prefer for this to be automatic.
I have tried using the Windows Script Host Object Model to list all of the printers like so:
Function FindPrinter(name As String)
Dim nwo As New WshNetwork
Dim i As Integer
Dim fullName As String
For i = 0 To (nwo.EnumPrinterConnections.Count / 2) - 1
If InStr(nwo.EnumPrinterConnections(i * 2 + 1), name) > 0 Then
fullName = nwo.EnumPrinterConnections(i * 2 + 1)
End If
Next i
' Returns the LAST printer that matches
FindPrinter = fullName
End Function
Sub MyMacro()
ActivePrinter = FindPrinter("Printer-A")
End Sub
However this fails. If I look at the output of FindPrinter
, it's only returning the string "Printer-A" without the "on Ne02:"
How can I get a full list of printers installed, including this "on NeXX:" bit? Or, alternatively, how can I set a printer as active knowing only its name?
Best Answer
Run the Test sub at the bottom: