Select a network Printer

Jat999

New Member
Joined
May 7, 2016
Messages
44
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
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,264
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

New Member
Joined
May 7, 2016
Messages
44
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:

VBA Code:
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
 
Last edited by a moderator:
Solution

dnorm

Board Regular
Joined
Dec 28, 2017
Messages
96
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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

New Member
Joined
May 7, 2016
Messages
44

ADVERTISEMENT

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:
VBA Code:
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

Board Regular
Joined
Dec 28, 2017
Messages
96
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 7, 2016
Messages
44
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,244
Messages
5,595,032
Members
413,962
Latest member
PedroGomez9

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
Top