| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change ActivePrinter?
I have a macro, which should change the active printer.
I'm doing this with... Application.ActivePrinter = "Adobe PDF" ....but it doesn't work?!? I suspect this has to do with the fact that on some computers the printer is located "on Ne03:", "on Ne09:", etc... How to change active printer, no matter "on what" the printer is located??? Thanks in advance... |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change ActivePrinter?
I think you should record a macro when you change printers to the Adobe PDF just
to make sure that you get the exact string (minus the NE stuff). I saved this from a previous post. When I do this kind of thing, I'll let the user decide what printer to use. Maybe you can just show a dialog and let the user check/verify their printer choices: Application.Dialogs(xlDialogPrinterSetup).Show But if you wanted to automate it, you could use something like: Option Explicit Sub testme() Dim UseThisPrinter As String Dim CurPrinter As String 'save the current printer CurPrinter = Application.ActivePrinter 'make sure that the string is correct with your recorded macro UseThisPrinter = GetPrinter(myPrinterName:="\\mrafp1\MRA-ADMINCP1 on Ne") If UseThisPrinter = "" Then MsgBox "Printer not found--what should happen" Exit Sub '??? End If 'and change (temporarily to the network printer you want) Application.ActivePrinter = UseThisPrinter 'your code to print 'change their printer back to what they like Application.ActivePrinter = CurPrinter End Sub Function GetPrinter(myPrinterName As String) As String Dim iCtr As Long Dim myStr As String Dim FoundIt As Boolean Dim CurPrinter As String CurPrinter = Application.ActivePrinter FoundIt = False For iCtr = 0 To 99 On Error Resume Next myStr = myPrinterName & Format(iCtr, "00") & ":" Application.ActivePrinter = myStr If Err.Number = 0 Then FoundIt = True Exit For Else 'keep looking Err.Clear End If Next iCtr On Error GoTo 0 Application.ActivePrinter = CurPrinter If FoundIt = True Then GetPrinter = myStr Else GetPrinter = "" End If End Function On 04/09/2011 03:12, Charlotte E wrote: I have a macro, which should change the active printer. I'm doing this with... Application.ActivePrinter = "Adobe PDF" ...but it doesn't work?!? I suspect this has to do with the fact that on some computers the printer is located "on Ne03:", "on Ne09:", etc... How to change active printer, no matter "on what" the printer is located??? Thanks in advance... -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change ActivePrinter?
Thanks for your suggestion, but I came up with another solution myself...
....which only required 4 short lines of VBA :-) CE "Dave Peterson" wrote in message ... I think you should record a macro when you change printers to the Adobe PDF just to make sure that you get the exact string (minus the NE stuff). I saved this from a previous post. When I do this kind of thing, I'll let the user decide what printer to use. Maybe you can just show a dialog and let the user check/verify their printer choices: Application.Dialogs(xlDialogPrinterSetup).Show But if you wanted to automate it, you could use something like: Option Explicit Sub testme() Dim UseThisPrinter As String Dim CurPrinter As String 'save the current printer CurPrinter = Application.ActivePrinter 'make sure that the string is correct with your recorded macro UseThisPrinter = GetPrinter(myPrinterName:="\\mrafp1\MRA-ADMINCP1 on Ne") If UseThisPrinter = "" Then MsgBox "Printer not found--what should happen" Exit Sub '??? End If 'and change (temporarily to the network printer you want) Application.ActivePrinter = UseThisPrinter 'your code to print 'change their printer back to what they like Application.ActivePrinter = CurPrinter End Sub Function GetPrinter(myPrinterName As String) As String Dim iCtr As Long Dim myStr As String Dim FoundIt As Boolean Dim CurPrinter As String CurPrinter = Application.ActivePrinter FoundIt = False For iCtr = 0 To 99 On Error Resume Next myStr = myPrinterName & Format(iCtr, "00") & ":" Application.ActivePrinter = myStr If Err.Number = 0 Then FoundIt = True Exit For Else 'keep looking Err.Clear End If Next iCtr On Error GoTo 0 Application.ActivePrinter = CurPrinter If FoundIt = True Then GetPrinter = myStr Else GetPrinter = "" End If End Function On 04/09/2011 03:12, Charlotte E wrote: I have a macro, which should change the active printer. I'm doing this with... Application.ActivePrinter = "Adobe PDF" ...but it doesn't work?!? I suspect this has to do with the fact that on some computers the printer is located "on Ne03:", "on Ne09:", etc... How to change active printer, no matter "on what" the printer is located??? Thanks in advance... -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change ActivePrinter?
After serious thinking Charlotte E wrote :
Thanks for your suggestion, but I came up with another solution myself... ...which only required 4 short lines of VBA :-) CE So then.., can you share that solution with us? -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change ActivePrinter?
Care to share those 4 short lines?
Gord Dibben MS Excel MVP On Sat, 9 Apr 2011 22:04:54 +0200, "Charlotte E" wrote: Thanks for your suggestion, but I came up with another solution myself... ...which only required 4 short lines of VBA :-) CE "Dave Peterson" wrote in message ... I think you should record a macro when you change printers to the Adobe PDF just to make sure that you get the exact string (minus the NE stuff). I saved this from a previous post. When I do this kind of thing, I'll let the user decide what printer to use. Maybe you can just show a dialog and let the user check/verify their printer choices: Application.Dialogs(xlDialogPrinterSetup).Show But if you wanted to automate it, you could use something like: Option Explicit Sub testme() Dim UseThisPrinter As String Dim CurPrinter As String 'save the current printer CurPrinter = Application.ActivePrinter 'make sure that the string is correct with your recorded macro UseThisPrinter = GetPrinter(myPrinterName:="\\mrafp1\MRA-ADMINCP1 on Ne") If UseThisPrinter = "" Then MsgBox "Printer not found--what should happen" Exit Sub '??? End If 'and change (temporarily to the network printer you want) Application.ActivePrinter = UseThisPrinter 'your code to print 'change their printer back to what they like Application.ActivePrinter = CurPrinter End Sub Function GetPrinter(myPrinterName As String) As String Dim iCtr As Long Dim myStr As String Dim FoundIt As Boolean Dim CurPrinter As String CurPrinter = Application.ActivePrinter FoundIt = False For iCtr = 0 To 99 On Error Resume Next myStr = myPrinterName & Format(iCtr, "00") & ":" Application.ActivePrinter = myStr If Err.Number = 0 Then FoundIt = True Exit For Else 'keep looking Err.Clear End If Next iCtr On Error GoTo 0 Application.ActivePrinter = CurPrinter If FoundIt = True Then GetPrinter = myStr Else GetPrinter = "" End If End Function On 04/09/2011 03:12, Charlotte E wrote: I have a macro, which should change the active printer. I'm doing this with... Application.ActivePrinter = "Adobe PDF" ...but it doesn't work?!? I suspect this has to do with the fact that on some computers the printer is located "on Ne03:", "on Ne09:", etc... How to change active printer, no matter "on what" the printer is located??? Thanks in advance... -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change ActivePrinter?
Care to share those 4 short lines?
But of cause :-) On Error Resume Next For counter = 99 to 0 step -1 Application.ActivePrinter = "Adobe PDF on Ne" & Counter & ":" Next Not a fancy solution, and maybe a bit of an overkill, but it works :-) Tried it on 4 different computers, running WinXP or Win7, and XL2003 or XL2010. Just shows that a little brute force should never be underestimated :-) Best regards... CE "Gord Dibben" wrote in message ... Care to share those 4 short lines? Gord Dibben MS Excel MVP On Sat, 9 Apr 2011 22:04:54 +0200, "Charlotte E" wrote: Thanks for your suggestion, but I came up with another solution myself... ...which only required 4 short lines of VBA :-) CE "Dave Peterson" wrote in message ... I think you should record a macro when you change printers to the Adobe just to make sure that you get the exact string (minus the NE stuff). I saved this from a previous post. When I do this kind of thing, I'll let the user decide what printer to use. Maybe you can just show a dialog and let the user check/verify their printer choices: Application.Dialogs(xlDialogPrinterSetup).Show But if you wanted to automate it, you could use something like: Option Explicit Sub testme() Dim UseThisPrinter As String Dim CurPrinter As String 'save the current printer CurPrinter = Application.ActivePrinter 'make sure that the string is correct with your recorded macro UseThisPrinter = GetPrinter(myPrinterName:="\\mrafp1\MRA-ADMINCP1 on Ne") If UseThisPrinter = "" Then MsgBox "Printer not found--what should happen" Exit Sub '??? End If 'and change (temporarily to the network printer you want) Application.ActivePrinter = UseThisPrinter 'your code to print 'change their printer back to what they like Application.ActivePrinter = CurPrinter End Sub Function GetPrinter(myPrinterName As String) As String Dim iCtr As Long Dim myStr As String Dim FoundIt As Boolean Dim CurPrinter As String CurPrinter = Application.ActivePrinter FoundIt = False For iCtr = 0 To 99 On Error Resume Next myStr = myPrinterName & Format(iCtr, "00") & ":" Application.ActivePrinter = myStr If Err.Number = 0 Then FoundIt = True Exit For Else 'keep looking Err.Clear End If Next iCtr On Error GoTo 0 Application.ActivePrinter = CurPrinter If FoundIt = True Then GetPrinter = myStr Else GetPrinter = "" End If End Function On 04/09/2011 03:12, Charlotte E wrote: I have a macro, which should change the active printer. I'm doing this with... Application.ActivePrinter = "Adobe PDF" ...but it doesn't work?!? I suspect this has to do with the fact that on some computers the printer is located "on Ne03:", "on Ne09:", etc... How to change active printer, no matter "on what" the printer is located??? Thanks in advance... -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change ActivePrinter?
Ups, forgot....
Third line should of cause format the number: Application.ActivePrinter = "Adobe PDF on Ne" & Format(Counter,"00") & ":" CE "Charlotte E" wrote in message ... Care to share those 4 short lines? But of cause :-) On Error Resume Next For counter = 99 to 0 step -1 Application.ActivePrinter = "Adobe PDF on Ne" & Counter & ":" Next Not a fancy solution, and maybe a bit of an overkill, but it works :-) Tried it on 4 different computers, running WinXP or Win7, and XL2003 or XL2010. Just shows that a little brute force should never be underestimated :-) Best regards... CE "Gord Dibben" wrote in message ... Care to share those 4 short lines? Gord Dibben MS Excel MVP On Sat, 9 Apr 2011 22:04:54 +0200, "Charlotte E" wrote: Thanks for your suggestion, but I came up with another solution myself... ...which only required 4 short lines of VBA :-) CE "Dave Peterson" wrote in message ... I think you should record a macro when you change printers to the Adobe just to make sure that you get the exact string (minus the NE stuff). I saved this from a previous post. When I do this kind of thing, I'll let the user decide what printer to use. Maybe you can just show a dialog and let the user check/verify their printer choices: Application.Dialogs(xlDialogPrinterSetup).Show But if you wanted to automate it, you could use something like: Option Explicit Sub testme() Dim UseThisPrinter As String Dim CurPrinter As String 'save the current printer CurPrinter = Application.ActivePrinter 'make sure that the string is correct with your recorded macro UseThisPrinter = GetPrinter(myPrinterName:="\\mrafp1\MRA-ADMINCP1 on Ne") If UseThisPrinter = "" Then MsgBox "Printer not found--what should happen" Exit Sub '??? End If 'and change (temporarily to the network printer you want) Application.ActivePrinter = UseThisPrinter 'your code to print 'change their printer back to what they like Application.ActivePrinter = CurPrinter End Sub Function GetPrinter(myPrinterName As String) As String Dim iCtr As Long Dim myStr As String Dim FoundIt As Boolean Dim CurPrinter As String CurPrinter = Application.ActivePrinter FoundIt = False For iCtr = 0 To 99 On Error Resume Next myStr = myPrinterName & Format(iCtr, "00") & ":" Application.ActivePrinter = myStr If Err.Number = 0 Then FoundIt = True Exit For Else 'keep looking Err.Clear End If Next iCtr On Error GoTo 0 Application.ActivePrinter = CurPrinter If FoundIt = True Then GetPrinter = myStr Else GetPrinter = "" End If End Function On 04/09/2011 03:12, Charlotte E wrote: I have a macro, which should change the active printer. I'm doing this with... Application.ActivePrinter = "Adobe PDF" ...but it doesn't work?!? I suspect this has to do with the fact that on some computers the printer is located "on Ne03:", "on Ne09:", etc... How to change active printer, no matter "on what" the printer is located??? Thanks in advance... -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| ActivePrinter in Excel changes! | Excel Programming | |||
| Activeprinter | Excel Programming | |||
| application.activeprinter and network device | Excel Programming | |||
| VBA ActivePrinter | Excel Programming | |||
| Application.ActivePrinter??? | Excel Programming | |||
![Charlotte E[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
