Printing query with Sendkeys

[Laura]

Good afternoon. I wonder if someone could offer some advice with a
small problem I am currently having. I use Excel in two environments,
Excel 2002 and Windows XP and also Excel 2002 with Windows Server 2003
(using Citrix Terminal Services). We have a scenario where a report is
exported from a third party product into Excel. The resulting
spreadsheet then needs to be printed to a printer which has two paper
trays taking one copy from tray 1 and four copies from tray 2. I want
to do this using a macro. After searching through the newsgroups I
decided the best way to do this would be to use SendKeys rather than
set up a second printer with the default tray 2 as we use Auto Created
printers in Citrix and I'm not sure how easy this would be (however
given my problems I may have to!). When I'm using Excel with Windows
XP my SendKeys code works beautifully, but when I test it out in the
Windows Server 2003 environment it falls apart and just won't work. I
can't understand why and wondered if there was maybe something I'm
missing. I'm a "simple" VBA user so my code may appear ridiculous but
it did work!

'Select correct printer
Application.ActivePrinter = "IT1 on Ne15:"
'Print one copy of current worksheet on headed paper
SendKeys "%R^{TAB}%S%{DOWN}TRAY 2{TAB}~~"
Application.Dialogs(xlDialogPrint).Show

My Sendkeys statement is supposed to go to the printer driver and
change the default bin.

If anyone can offer any suggestions I would appreciate it. However, I
understand I may need to go to the second printer route.

Thanks

Laura

 

[BrianB]

My guess is that the "server environment" runs a lot slower. Macros ru
very fast and the system can not keep up.

You will need to break down your Sendkeys into several parts and pu
delays in the code to give time for things to happen. Takes a bit o
tweaking. This line delays code for 1 second :-

Application.Wait Now + TimeValue("00:00:01"

 

[BrianB]

oops ... PS ...........
don't forget to use Sendkeys .... , True.

Try putting some DoEvents lines in too - although I have never notice
a difference using this

 

[Laura]

Brian, thanks for replying. I will go and try this and let you know how I get on.

Laura