7

I am using .SendKeys() in Excel VBA to send key strokes to an external window that I am making active using shell .AppActive method. The problem is that SendKeys is simply not behaving consistently and is sometimes sending the keys and sometimes not. I think it has something to do with storing the keys in the buffer according to MSDN documentation.

How does one get around this?

Community
  • 1
  • 1
rex
  • 3,133
  • 6
  • 35
  • 62

2 Answers2

4

You may consider using Autoit which is more reliable than SendKeys.

Download Autoit from below link http://www.autoitscript.com/site/autoit/downloads/

Add in reference autoit addin AutoItX3 1.0 Type Library

Below is sample code which will open the calculator and type 123456789

Sub sendkeys()

'Open a calc
    StartCalculator

    Dim au As New AutoItX3Lib.AutoItX3
    au.AutoItSetOption "WinTitleMatchMode", 2

    au.WinActivate "Calculator"

    'send key strokes
    au.ControlClick "Calculator", "", "Button5"
    au.ControlClick "Calculator", "", "Button11"
    au.ControlClick "Calculator", "", "Button16"
    au.ControlClick "Calculator", "", "Button4"
    au.ControlClick "Calculator", "", "Button10"
    au.ControlClick "Calculator", "", "Button15"
    au.ControlClick "Calculator", "", "Button3"
    au.ControlClick "Calculator", "", "Button9"
    au.ControlClick "Calculator", "", "Button14"

End Sub

Sub StartCalculator()
    Dim Program As String
    Dim TaskID As Double
    On Error Resume Next
    Program = "calc.exe"
    TaskID = Shell(Program, 1)
    If Err <> 0 Then
        MsgBox "Can't start " & Program
    End If
End Sub

enter image description here

Santosh
  • 12,175
  • 4
  • 41
  • 72
3

I found that the solution to this problem was to just make the application pause for a short while. This seems to allow the buffer to clear (correct me if I'm wrong please).

In order to make Excel VBA sleep, I exposed the windows API function Sleep by writing at the top of a module:

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

And then after every SendKeys command, I just used:

Sleep 1

And literally, 1 millisecond made all the difference and the keys were always sent correctly.

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
rex
  • 3,133
  • 6
  • 35
  • 62
  • The issue you will run into is that it will work in your DEV environment, and not PROD. Or... it will work in PROD environment... sometimes.... and then it will stop working. Hardcoded delays are never good. But this might be okay for your purposes. – robnick Jan 10 '14 at 08:42