Help improve my variable network printer code?

[NessPJ]

Hey guys,

After some searching and experimenting i have found some code here, that would run in my Excel Sheet and find a network printer (independant of client desktops).

I have the code here:

    'The current default printer is defined and stored.    
    Dim DefaultPrinter As String
    DefaultPrinter = Application.ActivePrinter

    'This routine allows you to address a Network printer independant of the Desktop Client:
    Dim q As Long, xUNC As String
    On Error Resume Next
    For q = 0 To 99
    xUNC = Sheets("Admin").Range("B26").Value & Sheets("Admin").Range("B14").Value & " on Ne" & Format(q, "00") & ":"
    Application.ActivePrinter = xUNC
    If Err.Number = 0 Then 'if UNC is valid, print
    
    'The Page Setup for the printing assignment is defined here. Some settings are not available to default printers:
     With Sheets("VVAAH2").PageSetup
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 1
    .LeftMargin = Application.InchesToPoints(0.708661417322835)
    .RightMargin = Application.InchesToPoints(0.708661417322835)
    .TopMargin = Application.InchesToPoints(0.15748031496063)
    .BottomMargin = Application.InchesToPoints(0.748031496062992)
    .HeaderMargin = Application.InchesToPoints(0.31496062992126)
    .FooterMargin = Application.InchesToPoints(0.31496062992126)
    .PaperSize = 342
    .CenterHorizontally = False
    .CenterVertically = False
    .Orientation = xlPortrait
    End With
    
    'Print de Pagina af met de correcte Netwerkprinter.
    Sheets("VVAAH2").PrintOut Copies:=1, ActivePrinter:= _
    xUNC
        
    Else
        Err.Clear
    End If
        Next q
        On Error GoTo 0

    'The stored default printer is selected again.
    Application.ActivePrinter = DefaultPrinter

The Server + Printername are referred to a Cell in my worksheet, so they can be parameter controlled (call it ready-for-the-future functionality).

Now i have noticed there are still some desktops which will not work because the Windows Network name still seems to deviate.
In this case i noticed the extension of the name was no longer " on Ne0"#:" but " on e0#:".

I was wondering if there was a way to make the VBA code above loop through both types of network names? Or i could rule out the "N" to be optional in the printer name somehow?

Thanks in advance!

 

[NessPJ]

I finally found the solution to all the problems that a single client was giving me!
It was the only client using another display/input language for Excel. I switched back to English and voila!

I guess there's nothing easy to comprehend this? (Shouldnt all the functions always work in enlgish for VBA?).

 

[NessPJ]

Hello all,

I ran into a slight issue here, with my printer code of which i once hoped would tick all random scenario's.

What happening is, that i now have 1 workstation where the printername on the network is defined with "Ne03" and another workstation where it is defined as "e03". Since my code is only working for "Ne03" printernames i was wondering if someone here could help me adapt it for both Ne0 and e0 network names.
Here is the code:

    Dim q As Long, xUNC As String
    On Error Resume Next
    For q = 0 To 99
    xUNC = Sheets("Admin").Range("B26").Value & Sheets("Admin").Range("B14").Value & " on Ne" & Format(q, "00") & ":"
    Application.ActivePrinter = xUNC
    If Err.Number = 0 Then 'if UNC is valid, print
    
    Sheets("VVAAH2").PrintOut Copies:=1, ActivePrinter:= _
    xUNC
        
    Else
        Err.Clear
    End If
        Next q
        On Error GoTo 0

Thanks a lot in advance!

 

[NessPJ]

Wow... 4 hours later and the topic just moves 5-6 pages!
Anyone have an idea please?

 

[Kyle123]

Why doesn't my original code give you that?

 

[NessPJ]

Hello Kyle,

I just tried the code you posted again earlier in the thread and this does indeed fix my issue!
I've tried it on some virtual workstations as well now and it seems to be working without a hitch.
I do agree this code is better than using error messages to determine whether you hit the right printerport or not.
Thanks for reminding me.