Select a network Printer

[Jat999]

Hi all

I have written a macro to generate pallet labels. These labels need to be printed on specific printers as they will have self adhesive labels in the tray. The IT dept. have created these printers on the network with a specific naming of "Barcode" in the description of the printer.

As part of the user form, the user selects one of the three possible printers available. The assumption is that the user will have the printer installed but not necessarily the default printer.

The age-old problem I have encountered is around the network number (NExx) will be different for each PC the macro is run from, so therefore cannot be hard-coded into the printer string.

The question for me, is it possible using xlDialogPrinterSetup list to find the relevant printer from the list and set this as being the active printer.

I would like the code to compare the hard-coded name in the macro to compare with the xlDialogPrinterSetup list and find the correct printer.

I do not want the user to have to select the printer each time as this would be time consuming.

I know there have been a lot of posts around this, but i am struggling to find a suitable solution.

Thanks in advance

 

[Jat999]

Self solved

 

[smozgur]

Self solved

Would you like to share the solution with the community, this way the future readers also learn from it? Then it is perfectly ok to mark your own solution as the answer to this question.

 

[Jat999]

Hi
Sure to share solution. Probably not the neatest solution, but it works.
As mentioned above, the issue is that the printer network number changes for each user - this is the NExx number at the end of the full path of the printer address.
The other critical point that must be considered with this solution is that the printer MUST already be listed in the user's available printers. If this is not possible, then you would have to resort to Windows coding to find the printer on the network and install the drivers etc - well beyond my level of skill.

Please note, the code is working from a form, but the essence of the code is as follows:

Dim Myprinter As String
Dim curNePrint As String
Dim curNePrint As String

Myprinter = Application.ActivePrinter ' Get the current printer to allow to reset the user back to their original printer

        If Leo_Prn = True Then ' This is determined based on a tick-box on the form. In my application, depending where the user is based, they will have their own local printer
       
            For I = 0 To 25  ' The counter will need to respect the maximum possible NExx address points
            curNePrint = Format(I, "00") 'Ensure the formatting is alway two digits
            On Error Resume Next
              Select_Prn = "\\Your network path of printer\Ricoh Barcode on Ne" & curNePrint & ":"
              Application.ActivePrinter = Select_Prn
            Next I
           On Error GoTo 0
        End If

ws.Range("Print_Area").PrintOut ActivePrinter:=Select_Prn, Copies:=Me.Num_Lbl 'Now print the number of necessary copies on the chosen printer
           
Application.ActivePrinter = Myprinter 'Reset the user default printer

In summary, the code above loops from NE00 to NE25, each time it does NOT find the printer address with the correct NExx number in the user's printer list, it will move on to the next "I" as an error, should it find the printer in the list, then the printer is set as the new activate printer

 

[smozgur]

Sure to share solution. Probably not the neatest solution, but it works.

Wonderful! Thank you!

 

[dnorm]

Hi All

Quick query, I need only the one worksheet printing each Monday (report day linked to cell W1):

If i know the address of the printer I need/want, could I just use:

Sub Printer_Trial()
'
' Print Trial
'

'
    If Range("W1").Value = "Mon" Then
    ActivePrinter "\\Server002\PRT037"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
End Sub

Printer 37 is a network printer used by all who will be generating the reports I need this printed from.

 

[Jat999]

Hi All

Quick query, I need only the one worksheet printing each Monday (report day linked to cell W1):

If i know the address of the printer I need/want, could I just use:

Sub Printer_Trial()
'
' Print Trial
'

'
    If Range("W1").Value = "Mon" Then
    ActivePrinter "\\Server002\PRT037"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
End Sub

Printer 37 is a network printer used by all who will be generating the reports I need this printed from.

Hi dnorm

The challenge you will face is that unless the network printer is assigned a static address which most IT departments do not like doing, although each user will have that printer assigned in their printer list, the printer will have different "NExx" address for each user (Where xx is a different numeric value for each user). So this becomes a user specific problem.

I suggest to create a simple sheet with your macro embeded and share with a couple of other users. If it works, then fine, then your network is stable, if not, then you need to follow the coding i put together. You need to establish the path of the printer including the NE element and then create a loop variable to step through all permatations. In my code, very important is the "On error resume next" statement, because each failed attempt will error the macro but this will continue to loop until successful.

 

[dnorm]

Hi dnorm

The challenge you will face is that unless the network printer is assigned a static address which most IT departments do not like doing, although each user will have that printer assigned in their printer list, the printer will have different "NExx" address for each user (Where xx is a different numeric value for each user). So this becomes a user specific problem.

I suggest to create a simple sheet with your macro embeded and share with a couple of other users. If it works, then fine, then your network is stable, if not, then you need to follow the coding i put together. You need to establish the path of the printer including the NE element and then create a loop variable to step through all permatations. In my code, very important is the "On error resume next" statement, because each failed attempt will error the macro but this will continue to loop until successful.

Thanks Jat999

Thankfully our network printers do have fixed addresses i.e. \\fileserver2\prt37 , \\fileserver1\prt08 or \\fileserverGB51\prt501 - not IP addresses i.e. \\10.10.10.10\dept$\prt37
so across all my login locations I can still access and network functions.

 

[Jat999]

Thanks Jat999

Thankfully our network printers do have fixed addresses i.e. \\fileserver2\prt37 , \\fileserver1\prt08 or \\fileserverGB51\prt501 - not IP addresses i.e. \\10.10.10.10\dept$\prt37
so across all my login locations I can still access and network functions.

OK Good

As I mentioned previously, i suggest you create a simple macro with the printer within to print a small test print range and share with a couple of colleagues to confirm if it works.

 

[IamBatman]

Hi
Sure to share solution. Probably not the neatest solution, but it works.
As mentioned above, the issue is that the printer network number changes for each user - this is the NExx number at the end of the full path of the printer address.
The other critical point that must be considered with this solution is that the printer MUST already be listed in the user's available printers. If this is not possible, then you would have to resort to Windows coding to find the printer on the network and install the drivers etc - well beyond my level of skill.

Please note, the code is working from a form, but the essence of the code is as follows:

Dim Myprinter As String
Dim curNePrint As String
Dim curNePrint As String

Myprinter = Application.ActivePrinter ' Get the current printer to allow to reset the user back to their original printer

        If Leo_Prn = True Then ' This is determined based on a tick-box on the form. In my application, depending where the user is based, they will have their own local printer
      
            For I = 0 To 25  ' The counter will need to respect the maximum possible NExx address points
            curNePrint = Format(I, "00") 'Ensure the formatting is alway two digits
            On Error Resume Next
              Select_Prn = "\\Your network path of printer\Ricoh Barcode on Ne" & curNePrint & ":"
              Application.ActivePrinter = Select_Prn
            Next I
           On Error GoTo 0
        End If

ws.Range("Print_Area").PrintOut ActivePrinter:=Select_Prn, Copies:=Me.Num_Lbl 'Now print the number of necessary copies on the chosen printer
          
Application.ActivePrinter = Myprinter 'Reset the user default printer

In summary, the code above loops from NE00 to NE25, each time it does NOT find the printer address with the correct NExx number in the user's printer list, it will move on to the next "I" as an error, should it find the printer in the list, then the printer is set as the new activate printer

I'm trying to use this to work around the Ne## change issue, but I'm running into the following issues:
1. The second "Dim curNePrint As String" is marked as a duplicate, and holds up the code. If I remove it then
2. The code prints to the wrong printer. I suspect this is because Leo_Prn isn't true so it skips down to the print line and just prints. I tried changing it to Leo_Prn = False, but then nothing happens (and I did change the printer name to match mine)*. What is this Leo_Prn, and what can I use since I don't have a form with a tick box?

My end game is to set a pair of macros as buttons, one to print to each printer.

*I got the name of the printer using the following code (which I got from a thread on here somewhere, but I've lost it), and just knocked the numbers off the end of the Ne

Sub test()
MsgBox Application.ActivePrinter
End Sub