SendKey Functionality

Hello Folks, I am new to this and am hopeful that someone can provide some guidance. I apologize upfront if this is the wrong forum.

I have an Excel spreadsheet that has many tabs for which many of them are printed each day. I would like to have printed the various tabs to print in the duplex mode. In fact, I would like any of the 12 employees in this area to be able to print the various reports.

We all are connected to a Xerox Workcentre Pro 275 printer, via the lan, that can print in duplex.

I tried recording a macro in Excel, but I have learned that the macro is not recorded while changing the printer details.

I therefore moved onto a less useful method by sending the keystrokes (SendKey). That seems to work, but up to a point (the ENTER Function). Here is the VERY SIMPLE code:

Sub xxx()
' xxx Macro
' Macro recorded 6/3/2010 by XXXX

'Select Printer
Application.ActivePrinter = "\\EEKIRK1PRN01\KGO2P56 on Ne03:"

'Open Printer Dialog Box
SendKeys "%FP"
Application.Wait Now + TimeValue("00:00:01")

'Open Printer Properties
SendKeys "%R"
Application.Wait Now + TimeValue("00:00:02")

'Open Printing Mode
SendKeys "{TAB 2}"

'Select Printing Mode (2 Sided Print, Flip on Short Edge)
SendKeys "{DOWN 2}"
Application.Wait Now + TimeValue("00:00:01")

'Change focus of dialog box to OK)
SendKeys "{TAB 9}"
Application.Wait Now + TimeValue("00:00:03")

'Enter to close this dialog box
Application.SendKeys "{~}", True


End Sub

I have tried many different approaches for the "ENTER" function (i.e., VbCrLf, {ENTER}, etc.) but in all cases, once the focus is on the OK and ENTER is sent, the system locks up. In all cases, the excel spreadsheet quits responding. I can manually do it and everything is fine. I am just not sure if the sendkey function is having an issue with the Xerox printer.

Any suggestions?

Once again, sorry if this is not the correct forum.

Thank you,

Pat



Welcome to the forum, Pat. Your question is about Excel VBA, so I have moved it from the VB .Net General board to the Excel VBA board.
If you have questions about Excel VBA then this is the board to post them in.

I have also added code /code around your code to make it easier to read.

Instead of using the clunky SendKeys method, I think you're probably better off to set duplex as the default. If this isn't the default you want for that printer, setup another printer driver and have that one setup as default. Then when you want to print whatever it is you're print in Excel, just use that particular printer driver instead to print duplex. Here is an example of changing the default printer in Excel...

http://erlandsendata.no/?p=2100
http://www.exceltip.com/st/Change_the_default_printer_using_VBA_in_Microsoft_Excel/453.html
http://www.vbaexpress.com/kb/getarticle.php?kb_id=528

HTH

As firefytr says one solution is 2 install the printer one more time in Windows. This way you can also select specefic trays, paper type and more.

I have the same printer installed 4 times in my system (tray 1, tray 2, MP tray and MP tray with envolopes)

But if your program is going to be used on other PC's where you dont have the rights to setup new printers AutoIT may be your solution.

AutoIT is my preffered program when it comes to sendKeys, and its 100% freeware.

Create a simple script in AutoIT like the one you allready did.
But instead op sleeping in 2 seconds and so on use the function called

winWait("Title of window")

This way the script wait until the "print" window is active, this way yoor script runs as fast as possible.

When you are finished to edit the properties of the printer you have to use winActivate("print dialog title") to send the last "enter" key stroke.

NOTICE:
When sending keystroke like CTRL+P Excel (at least in danish) thinks you send CTRL+SHIFT+p. Instead you need to send CTRL+p: send("^p")

In Excel

1)
Set currentActive printer to the desired printer
2)
Call the exe file which you can compile with AutoIT
3)
Let the script do its stuff and the print i complete.


- Stisen