Excel-Printing to specific tray using macro

[shabutt]

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.

 

[Barb Reinhardt]

I found this with a google search.

http://www.exceltip.com/st/Select_a...rinting_using_VBA_in_Microsoft_Excel/458.html

I've not tested it.

 

[Ron de Bruin]

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

 

[shabutt]

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]

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

 

[shabutt]

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