Sometimes I find myself copy-pasting between Excel and another application.
In this example, I have a table of three columns: First Name, Last Name, Birth Date.
My external application has 3 text boxes, one for each of those values.
I can’t just copy the 3 cells from Excel and paste them to my App, because they would all end up in the first text box!
But, by running VBA SendKeys with a specially crafted string, I can send keystrokes for tabbing to the 2nd and 3rd text boxes.
I also need to activate the SendKeys procedure only when my cursor is positioned correctly, or things could get messy.
My approach is to run a macro that sits there listening for F6 before activating SendKeys.
I’ve also included listening for the Esc key, just in case I change my mind.
‘ Virtual-Key Codes http://msdn2.microsoft.com/en-us/library/ms645540(VS.85).aspx
Const VK_F6 = &H75
Const VK_ESC = &H1B
Dim lngRow As Long, str As String
lngRow = Selection.row
str = Cells(lngRow, 1) & vbTab & Cells(lngRow, 2) & vbTab & _
Format(Cells(lngRow, 3), “dd-mmm-yyyy”)
MsgBox “Click OK, then click the First Name box on the external application, then press F6 on the keyboard”
WaitAndSend str, VK_F6, VK_ESC
Sub WaitAndSend(SendString As String, ExecuteKey As Long, CancelKey As Long)
If GetAsyncKeyState(CancelKey) <> 0 Then Exit Do
If GetAsyncKeyState(ExecuteKey) <> 0 Then
SendKeys SendString, True