I have an Excel macro that sets a printer based on user input, and then prints. I built it on inherited code and I’ve been learning as I go, so there are parts that work and I don’t know why.

The code sets a string variable to the printer name that the user chose; the printer name includes the server name but not the port. Then I set the ActivePrinter property of the PrintOut method to that printer name. This works, but I don’t understand why it works without the port.

If I check the printer-name variable immediately after the PrintOut line, it’s still ‘Printername’; at that same moment, Application.ActivePrinter is ‘Printername on Portname.’ So Application.ActivePrinter added the port name on it’s own, but I don’t know how it got that data.

Until now, it’s worked, and it was fine to let it be.

The problem is, now I want to update it to set the ActivePrinter BEFORE I use PrintOut, and I can’t figure out how to do that without knowing the port name. If I set ActivePrinter the current way [.PrintOut copies:=1, Collate:=False, ActivePrinter:=Printername], I don’t need the port name, because Application.ActivePrinter acquires it some other way; but the only other way to set it, that I can find, is [Application.ActivePrinter = Printername], and that seems to require a built-in port name – otherwise, I get “Runtime Error ‘1004’: Method ‘ActivePrinter’ of object ‘_Application’ failed.”

(My goal is to set the active printer before actually printing anything (so, before PrintOut), because if the user’s profile loses the connection to the printer they selected (which happens all the time, for reasons I don’t understand), the macro will default to the user’s default printer, and if that’s the wrong kind of printer it wastes a bunch of paper).

Here is the current code (with irrelevant sections removed). This will print correctly, but it has the wasting-paper problem that I’m trying to solve:

'User marks a cell in the sheet to choose their printer, then runs the macro

Dim printerChoice() As Variant 'Array will take in the cells where the user can choose a printer
Dim printer As String

With ThisWorkbook.Sheets("Sheet1")
    printerChoice = .Range("G1:G5").Value 'Printer-choice cells copied to array
End With

For i = LBound(printerChoice, 1) To UBound(printerChoice, 1)
    If printerChoice(i, 1) <> "" Then 'If the current printer is chosen
        j = j + 1
        If i = 1 Then
        printer = "\PRINTSERVER2Printer1"
        ElseIf i = 2 Then
        printer = "\PRINTSERVER2Printer2"
        ElseIf i = 3 Then
        printer = "\PRINTSERVER2Printer3"
        ElseIf i = 4 Then
        printer = "\PRINTSERVER2Printer4"
        ElseIf i = 5 Then
        printer = "\PRINTSERVER2Printer5"
        End If
    End If
Next i

'Code to do some things here

With ThisWorkbook.Sheets("Sheet1")

'Code to set print area here

.PrintOut copies:=1, Collate:=False, ActivePrinter:=printer

'At this point, printer = whatever it was set as above (example: "\PRINTSERVER2Printer2"), but Application.ActivePrinter = printer on Port (for example, "\PRINTSERVER2Printer2 on Ne23:")

End With

This is what I would like to do instead:

(same initial code as above...)

Next i

**'Set the ActivePrinter here, instead of later
Application.ActivePrinter = printer**

'Code to do some things here

With ThisWorkbook.Sheets("Sheet1")

'Code to set print area here

.PrintOut copies:=1, Collate:=False


End With

That code generates the Runtime error, unless I add a port name to the ActivePrinter, like this:

Application.ActivePrinter = printer & " on portname:"

Then it works (assuming I use the correct port name).

I know there are ways to get a macro to find the port name for a given printer, and I’ll do that if I have to, but they all add complication, and I’d like to avoid that if I can. And I also just want to understand vba better, and I haven’t been able to find the answer anywhere.