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.