| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printer Names on a Network
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? -- Corey .... The Silliest Question is generally the one i forgot to ask. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Printer Names on a Network
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 wrote: 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? -- Corey .... The Silliest Question is generally the one i forgot to ask. -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Printer Names on a Network
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.... "Dave Peterson" wrote in message ... 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 wrote: 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? -- Corey .... The Silliest Question is generally the one i forgot to ask. -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Printer Names on a Network
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.... "Dave Peterson" wrote in message ... 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 wrote: 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? -- Corey .... The Silliest Question is generally the one i forgot to ask. -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Printer Names on a Network
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" wrote in message ... 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 wrote: 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? -- Corey .... The Silliest Question is generally the one i forgot to ask. -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Printer Names on a Network
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. Corey wrote: 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" wrote in message ... 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 wrote: 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? -- Corey .... The Silliest Question is generally the one i forgot to ask. -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Available Printer names to be placed in a range on a sheet and Force selection based on part of Printer name | Excel Programming | |||
| members on my network printer not able to print to default printer | Excel Discussion (Misc queries) | |||
| How do I send a spreadsheet to a printer outside the network? | Excel Discussion (Misc queries) | |||
| Selecting a Printer on a network | Excel Programming | |||
| selecting network printer | Excel Programming | |||
![Corey[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
