Good morning,

I have been given a spreadsheet that when done entering all of the data needs to be printed out. However, it will have to use something other than my default printer settings. It will have to do this:
Document size will be default 8.5" x 11"
Change the Input tray to Tray 2
Change the Job Type from Locked Print to NORMAL
Set it up to print in DUPLEX and Open to Top
Then Close and Print.

How can this all be done in possibly VBA? I have no clue as I don't really work in Excel that much, but since I have been given this to use and the settings will change each time I was hoping to automate the process. Reason: It's a Shared Workbook and others will be updating and printing it as well and the printer is stocked with different colored paper for this project also. In case it matters, the printer is a Lanier LD255 networked laser B/W printer. If you could lend a hand on this one I'd appreciate it a lot.

have a nice one,
BUD

Hi Bud,

I will ask the obvious question first, Have you tried recording the macro.

All i have everdone is set the pageoptions and used printout to use the printer.
But as for tray etc. this is just something i have never done.

All the Best
David

Hi David and thanks,

Geez, I forgot all about the Macro thingy. Kinda been using VBA in Access and long since gotten away from those things. I'll look into that and again,
thanks very much and have a wonderful day.

Bud
I'll let you know how it worked out.

You too
and remember to tidy up your code cause the recorder will spew out a lot of nonsense as well.

Well I just tried it twice and it didn't work like I thought it would. I started the Macro Recorder, went through the motions, saved it and it didn't do it. When I went to review the Macro, this is all that was captured by the recorder:

What am I doing wrong? OR will Excel NOT record mouse movements???

BUD

Nothing, it looks as though excel doesn't interact with the printers print options.
We might be stumped with regards to excel unless we go down the route of attempting to find windows commands to set these options for the printer.

I will have a think.

My guess is that it will require APIs to call the printer specifics, since each printer has its own. I don't have access to it right now, but I think John Walkenbach in Power Programming has a chapter on Windows APIs and something on printers.

Ive done some reaserch and printers seem to be a bit of a pest.

See For selecting a printer tray
See for duplex
see For Paper Size

Good Luck and let us know how you get on.

Thanks to you both David and Rich for your assistance. I really wish they would let me re-create the entire thing in Access. I'll see what I can rig up possibly.

have a nice one,
BUD

it's all done