Selecting another network printer vba - Office 365

MyersEPS

New Member
Joined
Nov 2, 2018
Messages
14
Hello, everything that I can find on this is saying I have the correct code, but is still not working. I recorded the marco to select the printer, so I am assuming that is correct. Any help would be great. This is what I have;

Sub CHANGE_PRINTER_ONE()
Dim sCurrentPrinter As String
sCurrentPrinter = Application.ActivePrinter

Application.ActivePrinter = "\\IAN1516\Brother HL-2240 series LV Cell on Ne06:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"\\IAN1516\Brother HL-2240 series LV Cell on Ne06:", Collate:=True

Application.ActivePrinter = sCurrentPrinter
End Sub
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
I'm no expert at all on this, but what's "...still not working...?"
Is the code bugging out, just not doing anything, not printing to the desired printer, or what?


Two observations:
1. When I run
Code:
debug.print Application.ActivePrinter
... I only get the printer name, and port, not the server name as per your code. It should be noted that I'm not in a network environment, and I guess that you are, so this is perhaps, why.

2. Also, I don't think there's any need for the separate line:
Code:
Application.ActivePrinter = "\\IAN1516\Brother HL-2240 series LV Cell on Ne06:"
... as the printer's being set later in your code, anyway.

To be honest, I don't think it's going to make any difference, but it may be worth trying:
Code:
Sub CHANGE_PRINTER_ONE()
Dim sCurrentPrinter As String
sCurrentPrinter = Application.ActivePrinter

ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"\\IAN1516\Brother HL-2240 series LV Cell on Ne06:", Collate:=True

Application.ActivePrinter = sCurrentPrinter
End Sub

... and as a last resort, perhaps ditch the server name, too:
Code:
Sub CHANGE_PRINTER_ONE()
Dim sCurrentPrinter As String
sCurrentPrinter = Application.ActivePrinter

ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Brother HL-2240 series LV Cell on Ne06:", Collate:=True

Application.ActivePrinter = sCurrentPrinter
End Sub
 
Upvote 0
Sorry about that, I am getting a Run-time error 1004, Method 'ActivePrinter' of object'_Application' failed.

I tired both of your suggestions and it is printing to my printer, not the Brother 2240.
 
Upvote 0
Mmm
I'm all out of ideas, I'm afraid. Hopefully, this will have pushed your post back to the top, so that someone else may be able to help.
All the best
 
Upvote 0
This has been bugging me (pardon the pun!).

I've done some research, and the only other thing I can come up with, is that Windows appears to regularly change the numbered suffix for a printer. I'm not sure why, or when, but it's not constant - that's for sure.
I don't know whether this is only when on a network, or at each user login, but the port number's definitely not static.

When you did your original post, you obtained the full printer name by using a macro, and (understandably) hard-coded the name into your procedure.
There seem to be several methods of obtaining the current port numbers - including going into the registry - but I'm suggesting the following method, as the most simple one I've found, which might (?) achieve your goal. This particular one comes from Juan Pablo Gonzalez - from this website, but there are quite a few variations on a theme, out there.

The idea seems to be to iterate through the likely printer ports (appending them to the name of the printer you're trying to access), until the code doesn't error, and Bob's your uncle - you've found the CURRENT port for that printer!

Code:
Sub CHANGE_PRINTER_ONE()
Dim sCurrentPrinter As String
sCurrentPrinter = Application.ActivePrinter

Dim intgr As Integer

    For intgr = 0 To 16
       On Error Resume Next
        Application.ActivePrinter = "\\IAN1516\Brother HL-2240 series LV Cell on Ne" & Format$(intgr, "00") & ":"
       'Debug.Print "Active printer is " & Application.ActivePrinter & " Integer is now: " & Format$(intgr, "00") & " Error number is now: " & Err.Number
    
       If Err.Number = "0" Then Exit For
    Next intgr

If Not Application.ActivePrinter Like "\\IAN1516\Brother HL-2240 series LV Cell on Ne*" Then
    MsgBox "Unable to set the printer", vbCritical
    Exit Sub
End If

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Application.ActivePrinter = sCurrentPrinter
End Sub
I've made several changes to both his original code, and yours - to make it pertinent to your scenario.

Hopefully, this might just work...
Do tell!
 
Upvote 0
Sykes, I am getting "Unable to set the printer". I am going to see how they have the printed networked. Something is weird.
 
Upvote 0
Probably a silly question, but is that particular printer actually on line, at the moment?
 
Upvote 0
Don't iterate printer ports, you can read it from the registry very easily:

Code:
Public Function GetPrinterPort(strPrinterName As String) As String
   Dim objReg As Object, strRegVal As String, strValue As String
   Const HKEY_CURRENT_USER = &H80000001
   Set objReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
   strRegVal = "Software\Microsoft\Windows NT\CurrentVersion\PrinterPorts\"
   objReg.getstringvalue HKEY_CURRENT_USER, strRegVal, strPrinterName, strValue
   GetPrinterPort = Split(strValue, ",")(1)
End Function
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top