The SendKeys Command
The SendKeys command is not really a function but rather a command statement. A command statement like this can be used in a function or a subroutine. It allows you to command another application by sending keypresses to it, exactly the same as if you were typing at the keyboard into that application. It effectively simulates keypresses on the keyboard and can be used for low-level automation of programs that do not support OLE automation.
All the applications in the Microsoft Office Suite support OLE automation in that you can load in a reference to a particular object model and then manipulate that application from inside your code.
Other applications do not support this; a good example is the calculator application that is supplied with Microsoft Windows. There is no access to an object model, but you can use SendKeys to manipulate it.
SendKeys sends one or more keystrokes to the active window as if they had been entered at the keyboard:
SendKeys keytext [,wait]
In this example, keytext is the string of keys to be sent to the active window; wait is a Boolean value (True or False). If wait is True, then the keys must be processed first before control is returned to the procedure. If wait is False, then control is returned to the procedure immediately after the keystrokes are sent. If wait is omitted, then False is assumed as the default.
The value of wait can be extremely important when sending keys to another application. If the application is running quickly, it may have moved on in execution by the time the key statement comes across, so the SendKeys statement gets ignored. You set wait to True so that the keystrokes are processed first, preventing the application from moving on ahead. Generally, the keyboard keys themselves are used in the SendKeys statement most of the time. For example,
SendKeys "A123"
sends A123 to the current window.
Of course, this is assuming you have the relevant application running and that it is the active window!
Two other commands will help with this. The first is the Shell function. This allows your code to launch another application and transfer control to it:
Shell (commandstring [,windowstyle])
The commandstring parameter is the command line to call the application. If you look at the shortcuts on your desktop, you will see there is a text box called Target that holds the pathname and filename of the application plus any necessary parameters. This is used as the commandstring parameter.
The windowstyle parameter dictates how the application will be opened, whether it will be opened as a normal window and icon or hidden.
Before you can send your keypresses to the application, you need to open it. The following example opens the Windows Calculator:
x = Shell("calc.exe",1)
This opens the Windows Calculator application in a standard window with focus. Since it is now the active window, you can use SendKeys to send keypresses to it.
The other command to use before you send keys is AppActivate. If the application is already loaded, you do not need to use the Shell function to load it in again, but you do need to switch the focus over to that application so it can send the keys. This allows your code to activate another application already loaded by use of the title in the header bar:
AppActivate "Microsoft Word"
In this way, you can perform simple automation of other applications:
Sub test_sendkeys()
x = Shell("calc.exe")
For n = 1 To 10
SendKeys n & "{+}", True
Next n
MsgBox "Press OK to close calculator"
AppActivate "calculator"
SendKeys "%{F4}", True
End Sub
In this example, the Windows Calculator is loaded, and then a For..Next loop makes it add up the numbers from 1 to 10.
The message box will appear on the Access application, because that is where the code is running from. The Access icon will flash on the Windows toolbar. Select Access and click OK, and the calculator will close.
The plus sign (+), caret (^), percent sign (%), tilde (~), and parentheses (( )) have special meanings to SendKeys. To specify one of these characters, enclose it in braces ({}). For example, to specify the plus sign, type {+}. Brackets ([ ]) have no special meaning to SendKeys, but you must enclose them in braces.
To specify special characters that aren't displayed when you press a key, such as ENTER or
TAB, and keys that represent actions rather than characters, use the codes shown in Table-7.
To specify keys combined with any combination of the SHIFT, CTRL, and ALT keys, precede
the key code with one or more of the following codes:
Key Code
SHIFT +
CTRL ^
ALT %
BACKSPACE {BACKSPACE}, {BS}, or {BKSP}
BREAK {BREAK}
CAPS LOCK {CAPSLOCK}
DEL OR DELETE {DELETE} or {DEL}
DOWN ARROW {DOWN}
END {END}
ENTER {ENTER}or ~
ESC {ESC}
HELP {HELP}
HOME {HOME}
INS OR INSERT {INSERT} or {INS}
LEFT ARROW {LEFT}
NUM LOCK {NUMLOCK}
PAGE DOWN {PGDN}
PAGE UP {PGUP}
PRINT SCREEN {PRTSC}
RIGHT ARROW {RIGHT}
SCROLL LOCK {SCROLLLOCK}
TAB {TAB}
UP ARROW {UP}
F1 {F1}
F2 {F2}
F3 {F3}
F4 {F4}
F5 {F5}
F6 {F6}
F7 {F7}
F8 {F8}
F9 {F9}
F10 {F10}
F11 {F11}
F12 {F12}
To specify that any combination of SHIFT, CTRL, and ALT should be held down while several other keys are pressed, enclose the code for those keys in parentheses. For example, to specify to hold down SHIFT while E and C are pressed, use +(EC). To specify to hold down SHIFT while E is pressed, followed by C without SHIFT, use +EC.
If you use SendKeys to drive another application, be aware that the keyboard is still active and can have disastrous results if it is touched while SendKeys is running.
SendKeys program for a major bank in the UK to work with a time-recording application. The program ran overnight and generated timesheets for use on Friday morning. One Friday morning, there were no timesheets because the program had gone haywire. The reason for this was that during the evening, a cleaner had been dusting and managed to press the RIGHT ARROW key on the keyboard, throwing off my careful sequence of keystrokes. This meant that instead of going down one particular column, it went down the next one, and the keystrokes had no effect. After that incident, when the program was run on a Thursday evening, the keyboard was always placed behind the monitor out of harm's way.
SendKeys is not the most elegant way of doing things, but in this particular case it was the only option available given the application involved. Because of thousands of users on the application, it put immense pressure on the server on a Friday morning with people logging on and generating timesheets, and it certainly led to the server going down and a lot of unhappy project managers.
The SendKeys option saved the day in this particular case and allowed the timesheets to be generated overnight. Managers were pleased and the servers stayed up!