Printer Names on a Network

[Corey]

On our office network we share 2 network printers.
Each PC will have these listed as the same name, but each PC also has some
other printers listed.

I have a workbook each user uses, that when a macro runs, the 1st page is
sent to an OKI Printer, and page 2 is sent to HP printer.

The problem is that for some reason the printers on each PC move up and down
in the list, and do not stay in the Alphabetical list i placed them in.

If i record a macro in Excel to select each printer they are recorded like:

Sub PC2PrinterA() ' Printer Settings
Application.ActivePrinter = "AAA OKI C 5200 n on Ne02:"
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1,
ActivePrinter:="AAA OKI C 5200 n on Ne02:"
Application.ActivePrinter = "AAA HP LaserJet 5000 Series PS on Ne01:"
ActiveWindow.SelectedSheets.PrintOut From:=2, To:=2, Copies:=1,
ActivePrinter:="AAA HP LaserJet 5000 Series PS on Ne01:"
End Sub

I placed the 'AAA' on the printer names to try to keep them in order
Alphabetically, but that does not work.

Is there a way to refer to the Printer by it's IP Address?
or
Is there a way to place each Printer name on a worksheet, and then to Search
for PART of the Printer's name (eg. OKI or HP) from the sheet cell to ensure
each of the sheets will print to each of the printers?

 

[Dave Peterson]

I would do something like:

Option Explicit
Sub PC2PrinterA() ' Printer Settings
Dim CurPrinter As String
Dim NewPrinter As String

'save the current printer so that we can set things back
CurPrinter = Application.ActivePrinter

NewPrinter = ChangePrinterName("OKI C 5200 n on Ne")
If NewPrinter = "" Then
MsgBox "oki not found!"
Else
Application.ActivePrinter = NewPrinter
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1
End If

NewPrinter = ChangePrinterName("HP LaserJet 5000 Series PS on Ne")
If NewPrinter = "" Then
MsgBox "HP not found!"
Else
Application.ActivePrinter = NewPrinter
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1
End If

Application.ActivePrinter = CurPrinter

End Sub
Function ChangePrinterName(PrtrPfx As String) As String

Dim pCtr As Long
Dim TestPrinter As String
Dim NewPrinter As String
Dim CurPrinter As String

CurPrinter = Application.ActivePrinter

NewPrinter = ""
On Error Resume Next
For pCtr = 0 To 99
TestPrinter = PrtrPfx & Format(pCtr, "00") & ":"
Application.ActivePrinter = TestPrinter
If Err.Number <> 0 Then
'keep looking
Err.Clear
Else
NewPrinter = TestPrinter
Exit For
End If
Next pCtr

ChangePrinterName = NewPrinter

End Function

 

[Corey]

Thanks for the reply Dave.

I will have a look through your code and see if it solves my problem.

Will post back when i give it a run.

Corey....

 

[Corey]

Dave,
I tried the code, but each time i get the Not Found msg on each printer.

Do i keep the Function code in the same module as the print code?

Corey....

 

[Corey]

On second thoughts, Dave your code sem to work a treat.

I found as i had the 'AAA' at the start of the printer name, and you had
removed it, the code would not work.

When i placed the 'AAA' back int he code, hey-presto


Thanks Dave

 

[Dave Peterson]

I wasn't sure why you added the AAA stuff and thought that only you would have
it there. If the other users don't have it, then you should remove it.