SendKeys
SendKeys allows you to send keystrokes to the currently active window. It is used to control applications that do not support any other form of communication, such as DDE (Dynamic Data Exchange) or OLE. It is generally considered a last-resort technique.
The following example opens the Notepad application, which does not support DDE or OLE, and writes a line of data to the Notepad document:
|
Sub SKeys() | |
|
Dim dReturnValue As Double | |
|
dReturnValue = Shell( |
"NOTEPAD.EXE", vbNormalFocus) |
|
AppActivate dReturnValue | |
|
Application.SendKeys |
"Copy Data.xlsx c:\", True |
|
Application.SendKeys |
True |
|
Application.SendKeys |
"%FABATCH%S", True |
|
End Sub | |
This example might not execute correctly from the VBE. Run it from the Excel window.
SKeys uses Alt+F+A to perform a File O SaveAs and enters the filename as BATCH and then enters Alt+S to save the text file. The percent symbol (%) is used to represent Alt and the tilde (~) represents Enter. The caret symbol (A) is used to represent Ctrl, and other special keys are specified by putting their names in curly braces. For example, the Delete key is represented by {Del}, as shown in the following example.
You can also send keystrokes directly to Excel. The following procedure clears the VBE's Immediate window. If you have been experimenting in the Immediate window or using Debug.Print to write to the Immediate window, it can get cluttered with old information. This procedure switches focus to the Immediate window and sends Ctrl+a to select all the text in the window. The text is then deleted by sending Del:
Sub ImmediateWindowClear()
Application.VBE.Windows.Item("Immediate").SetFocus Application.SendKeys "Aa" Application.SendKeys "{Del}" End Sub
It is necessary for you to have programmatic access to your Visual Basic project for this macro to work. This can be set from the Excel Ribbon. Select the Developer tab, select Macro Security, and check the box against Trust access to the VBA project object model.
Average user rating: 5 stars out of 1 votes
Post a comment