Identifying active printer with changing NE?

[Ben171]

Hi, hopefully a simple one.

I have created a little database in excel which creates documents and prints it to a specific printer. I got the names of the printers using the following Macro

Debug.Print Application.ActivePrinter

an example of the result this would produce is: \\Server\PrinterName on Ne22:

Therefore I can put this printer in my code and it works great. However i may then come on a couple of days later, and my documents won't print. I then run the find printer macro and the 'Ne' Number has changed. I have tried to do some digging on this but couldn't find anything. Is it possible to stop this Ne number from changing? Or is it possible to get this printer into excel code without listing the Ne?

 

[RoryA]

See for example this post: Selecting another network printer vba - Office 365

The code there will return the correct port for a given printer name.

 

[Ben171]

See for example this post: Selecting another network printer vba - Office 365

The code there will return the correct port for a given printer name.

Thanks for the response. This looks good I will give this a try!

 

[johnnyL]

This is similar code that I had previously written:

Sub AutoSetPrinterPortFromPrinterName()
'
    Dim PrinterName         As String
    Dim strCurrentPrinter   As String
    Dim strsetting          As String
    Dim PrinterPort         As Variant
'
    Application.ScreenUpdating = False
'
    PrinterName = "Fax"                                          ' <--- Set this to the name of your printer that you want to use
'
    strsetting = CreateObject("WScript.Shell").RegRead("HKEY_CURRENT_USER\Software\Microsoft\Windows NT\CurrentVersion\Devices\" & PrinterName)
    PrinterPort = Split(strsetting, ",")                        ' Get the Printer port ... example: PrinterPort(1) might yield Ne01: as a response
'
    Application.ActivePrinter = PrinterName & " on " & PrinterPort(1)
'
    strCurrentPrinter = Application.ActivePrinter
'
MsgBox strCurrentPrinter
    Application.ScreenUpdating = True
End Sub

 

[Ben171]

This is similar code that I had previously written:

Sub AutoSetPrinterPortFromPrinterName()
'
    Dim PrinterName         As String
    Dim strCurrentPrinter   As String
    Dim strsetting          As String
    Dim PrinterPort         As Variant
'
    Application.ScreenUpdating = False
'
    PrinterName = "Fax"                                          ' <--- Set this to the name of your printer that you want to use
'
    strsetting = CreateObject("WScript.Shell").RegRead("HKEY_CURRENT_USER\Software\Microsoft\Windows NT\CurrentVersion\Devices\" & PrinterName)
    PrinterPort = Split(strsetting, ",")                        ' Get the Printer port ... example: PrinterPort(1) might yield Ne01: as a response
'
    Application.ActivePrinter = PrinterName & " on " & PrinterPort(1)
'
    strCurrentPrinter = Application.ActivePrinter
'
MsgBox strCurrentPrinter
    Application.ScreenUpdating = True
End Sub

Hi Johnny, thanks for this it is a great idea. Think this is going to be how I go about sorting this issue. Problem is I have am having trouble finding networked printers I've got on servers, it errors out on the line:

strsetting = CreateObject("WScript.Shell").RegRead("HKEY_CURRENT_USER\Software\Microsoft\Windows NT\CurrentVersion\Devices\" & PrinterName)

Even though I have printer name as "\\Server11\PrinterName"

And i can see that in the registry this exists exactly like that

 

[RoryA]

I suggest you use Kyle's code in the thread I linked to. It works fine with network printers.

 

[Ben171]

I suggest you use Kyle's code in the thread I linked to. It works fine with network printers.

Hi Rory,

I am having a stupid moment with this, I'm new to this and cannot find how to call a function within VBA code *Facepalm*

 

[RoryA]

You'd add Kyle's function to your module, then in your code you'd have something like:

Dim PrinterName as string
PrinterName = "\\Server11\PrinterName"
Dim FullPrinterName as string
Fullprintername = printername & " on " & getprinterport(printername)
application.activeprinter = fullprintername

Make sense?

 

[Ben171]

You'd add Kyle's function to your module, then in your code you'd have something like:

Dim PrinterName as string
PrinterName = "\\Server11\PrinterName"
Dim FullPrinterName as string
Fullprintername = printername & " on " & getprinterport(printername)
application.activeprinter = fullprintername

Make sense?

Oh durrr, I see. This makes complete. I was being stupid trying to create it as it's own Macro. I will give this a try.

Thanks again

 

[Ben171]

You'd add Kyle's function to your module, then in your code you'd have something like:

Dim PrinterName as string
PrinterName = "\\Server11\PrinterName"
Dim FullPrinterName as string
Fullprintername = printername & " on " & getprinterport(printername)
application.activeprinter = fullprintername

Make sense?

Works perfectly now i've done it correctly.

Thank you so much for your help and patience