Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel-Printing to specific tray using macro
Hello,
I have been using Mr. Mayor's following macro for word (I use word 2007) which lets me print to any tray I specify in the macro and I have two macros set up for two different trays. Sub LaserTray1() Dim sCurrentPrinter As String Dim sTray As Integer sCurrentPrinter = ActivePrinter sTray = Options.DefaultTrayID ActivePrinter = HP LaserJet P2015 Series PCL 5e With Options .DefaultTrayID = 259 End With Application.PrintOut Filename:="" With Options .DefaultTrayID = sTray End With ActivePrinter = sCurrentPrinter End Sub I am unable to use the same code to run in excel 2007 which is obvious as the above code is for word. Is it possible to modify the above code so it works in excel too. Guide me please. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel-Printing to specific tray using macro
I found this with a google search.
http://www.exceltip.com/st/Select_a_...Excel/458.html I've not tested it. -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "shabutt" wrote: Hello, I have been using Mr. Mayor's following macro for word (I use word 2007) which lets me print to any tray I specify in the macro and I have two macros set up for two different trays. Sub LaserTray1() Dim sCurrentPrinter As String Dim sTray As Integer sCurrentPrinter = ActivePrinter sTray = Options.DefaultTrayID ActivePrinter = HP LaserJet P2015 Series PCL 5e With Options .DefaultTrayID = 259 End With Application.PrintOut Filename:="" With Options .DefaultTrayID = sTray End With ActivePrinter = sCurrentPrinter End Sub I am unable to use the same code to run in excel 2007 which is obvious as the above code is for word. Is it possible to modify the above code so it works in excel too. Guide me please. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel-Printing to specific tray using macro
For the OP
The best way I think is to create different printers for each tray. Then change the defaultprinter with the code to it and print -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Barb Reinhardt" wrote in message ... I found this with a google search. http://www.exceltip.com/st/Select_a_...Excel/458.html I've not tested it. -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "shabutt" wrote: Hello, I have been using Mr. Mayor's following macro for word (I use word 2007) which lets me print to any tray I specify in the macro and I have two macros set up for two different trays. Sub LaserTray1() Dim sCurrentPrinter As String Dim sTray As Integer sCurrentPrinter = ActivePrinter sTray = Options.DefaultTrayID ActivePrinter = HP LaserJet P2015 Series PCL 5e With Options .DefaultTrayID = 259 End With Application.PrintOut Filename:="" With Options .DefaultTrayID = sTray End With ActivePrinter = sCurrentPrinter End Sub I am unable to use the same code to run in excel 2007 which is obvious as the above code is for word. Is it possible to modify the above code so it works in excel too. Guide me please. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel-Printing to specific tray using macro
Thanks Mr. Barb Reinhardt & Mr. Ron de Bruin for the reply.
Mr. Barb Reinhardt, your link contained a code which dates back to '97 version of excel and doesn't seem to work. Mr. Ron de Bruin, what's the code to change the default printer. Note: I have used the code of Mr. Allen Wyatt but that too is not working. Please help me out. "Ron de Bruin" wrote: For the OP The best way I think is to create different printers for each tray. Then change the defaultprinter with the code to it and print -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Barb Reinhardt" wrote in message ... I found this with a google search. http://www.exceltip.com/st/Select_a_...Excel/458.html I've not tested it. -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "shabutt" wrote: Hello, I have been using Mr. Mayor's following macro for word (I use word 2007) which lets me print to any tray I specify in the macro and I have two macros set up for two different trays. Sub LaserTray1() Dim sCurrentPrinter As String Dim sTray As Integer sCurrentPrinter = ActivePrinter sTray = Options.DefaultTrayID ActivePrinter = HP LaserJet P2015 Series PCL 5e With Options .DefaultTrayID = 259 End With Application.PrintOut Filename:="" With Options .DefaultTrayID = sTray End With ActivePrinter = sCurrentPrinter End Sub I am unable to use the same code to run in excel 2007 which is obvious as the above code is for word. Is it possible to modify the above code so it works in excel too. Guide me please. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel-Printing to specific tray using macro
Try this
Note: "Adobe PDF" is the name that you see in the Ctrl p dialog Sub Test() Dim str As String Dim strNetworkPrinter As String str = Application.ActivePrinter strNetworkPrinter = GetFullNetworkPrinterName("Adobe PDF") If Len(strNetworkPrinter) 0 Then Application.ActivePrinter = strNetworkPrinter ActiveSheet.PrintOut End If Application.ActivePrinter = str End Sub Function GetFullNetworkPrinterName(strNetworkPrinterName As String) As String Dim strCurrentPrinterName As String, strTempPrinterName As String, i As Long strCurrentPrinterName = Application.ActivePrinter i = 0 Do While i < 100 strTempPrinterName = strNetworkPrinterName & " on Ne" & Format(i, "00") & ":" On Error Resume Next ' try to change to the network printer Application.ActivePrinter = strTempPrinterName On Error GoTo 0 If Application.ActivePrinter = strTempPrinterName Then GetFullNetworkPrinterName = strTempPrinterName i = 100 ' makes the loop end End If i = i + 1 Loop Application.ActivePrinter = strCurrentPrinterName ' change back to the original printer End Function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "shabutt" wrote in message ... Thanks Mr. Barb Reinhardt & Mr. Ron de Bruin for the reply. Mr. Barb Reinhardt, your link contained a code which dates back to '97 version of excel and doesn't seem to work. Mr. Ron de Bruin, what's the code to change the default printer. Note: I have used the code of Mr. Allen Wyatt but that too is not working. Please help me out. "Ron de Bruin" wrote: For the OP The best way I think is to create different printers for each tray. Then change the defaultprinter with the code to it and print -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Barb Reinhardt" wrote in message ... I found this with a google search. http://www.exceltip.com/st/Select_a_...Excel/458.html I've not tested it. -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "shabutt" wrote: Hello, I have been using Mr. Mayor's following macro for word (I use word 2007) which lets me print to any tray I specify in the macro and I have two macros set up for two different trays. Sub LaserTray1() Dim sCurrentPrinter As String Dim sTray As Integer sCurrentPrinter = ActivePrinter sTray = Options.DefaultTrayID ActivePrinter = HP LaserJet P2015 Series PCL 5e With Options .DefaultTrayID = 259 End With Application.PrintOut Filename:="" With Options .DefaultTrayID = sTray End With ActivePrinter = sCurrentPrinter End Sub I am unable to use the same code to run in excel 2007 which is obvious as the above code is for word. Is it possible to modify the above code so it works in excel too. Guide me please. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel-Printing to specific tray using macro
Dear Mr. Ron de Bruin,
Thanks for your help. The code does work most of the time but when I try to run it the first time Excel is opened, the printout goes to the default printer and not to the intended printer (which is just the copy of default printer but having tray 1 setting instead of the automatic or tray 2 setting). The printer is on network. I don't know what's causing this but thanks again for your help. The below code does the trick but it also present same problem as yours Sub Tray1() Application.ActivePrinter = "Network Printer Name Goes Here" ActiveSheet.PrintOut End Sub Mr. Graham Mayor's code for MS Word is best and I hope it could be implemented in Excel too because to create copy of printer causes problems in prefect execution of code. Regards, Shahbaz "Ron de Bruin" wrote: Try this Note: "Adobe PDF" is the name that you see in the Ctrl p dialog Sub Test() Dim str As String Dim strNetworkPrinter As String str = Application.ActivePrinter strNetworkPrinter = GetFullNetworkPrinterName("Adobe PDF") If Len(strNetworkPrinter) 0 Then Application.ActivePrinter = strNetworkPrinter ActiveSheet.PrintOut End If Application.ActivePrinter = str End Sub Function GetFullNetworkPrinterName(strNetworkPrinterName As String) As String Dim strCurrentPrinterName As String, strTempPrinterName As String, i As Long strCurrentPrinterName = Application.ActivePrinter i = 0 Do While i < 100 strTempPrinterName = strNetworkPrinterName & " on Ne" & Format(i, "00") & ":" On Error Resume Next ' try to change to the network printer Application.ActivePrinter = strTempPrinterName On Error GoTo 0 If Application.ActivePrinter = strTempPrinterName Then GetFullNetworkPrinterName = strTempPrinterName i = 100 ' makes the loop end End If i = i + 1 Loop Application.ActivePrinter = strCurrentPrinterName ' change back to the original printer End Function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "shabutt" wrote in message ... Thanks Mr. Barb Reinhardt & Mr. Ron de Bruin for the reply. Mr. Barb Reinhardt, your link contained a code which dates back to '97 version of excel and doesn't seem to work. Mr. Ron de Bruin, what's the code to change the default printer. Note: I have used the code of Mr. Allen Wyatt but that too is not working. Please help me out. "Ron de Bruin" wrote: For the OP The best way I think is to create different printers for each tray. Then change the defaultprinter with the code to it and print -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Barb Reinhardt" wrote in message ... I found this with a google search. http://www.exceltip.com/st/Select_a_...Excel/458.html I've not tested it. -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "shabutt" wrote: Hello, I have been using Mr. Mayor's following macro for word (I use word 2007) which lets me print to any tray I specify in the macro and I have two macros set up for two different trays. Sub LaserTray1() Dim sCurrentPrinter As String Dim sTray As Integer sCurrentPrinter = ActivePrinter sTray = Options.DefaultTrayID ActivePrinter = HP LaserJet P2015 Series PCL 5e With Options .DefaultTrayID = 259 End With Application.PrintOut Filename:="" With Options .DefaultTrayID = sTray End With ActivePrinter = sCurrentPrinter End Sub I am unable to use the same code to run in excel 2007 which is obvious as the above code is for word. Is it possible to modify the above code so it works in excel too. Guide me please. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for printing specific worksheet from a workbook | Excel Programming | |||
Print with paper from a specific printer-tray | Excel Programming | |||
How do I change the paper tray from an Excel macro / vba module? | Excel Programming | |||
Printing worksheet with printer tray # 3 | Excel Programming | |||
I can't quite get the syntax for printing to the manual feed tray | Excel Programming |