Excel VBA application.sendkeys "^C", True not working

Question

I am using Excel VBA to copy text selection from an Access file (I'd prefer not to get into details as to why). I have it in a Do While loop that SHOULD press the tab key (works), then copies the data (fails), puts it into the clipboard (works), and sets the clipboard information to a variable (works), which then, for debugging purposes, does a debug.print of the variable (works). This is to cycle through a form to get to a "base point" where I can 100% use tabs and such to navigate to other parts of the form. See code please:

AppActivate ("Microsoft Access - Filename that is constant")

X = 0
Do While X < 14
Application.SendKeys "{TAB}", True
Application.SendKeys "^C", True

Sleep (500)

mydata.GetFromClipboard
cb = mydata.GetText

Debug.Print (cb)
If Len(cb) = 5 Then
X = 14
End If
X = X + 1
Loop
Set mydata = Nothing

I've tried getting this to work, but to no avail. What am I doing wrong or perhaps what would be a better solution?

Answer 1

Though I hate Sendkeys and was wondering whether I should ask you about it but since you said not to ask why, I will keep my trap shut. :P

Try this small fix... If this works then that means, you need to give it some time before issuing the next sendkeys command.

Sub Sample()
    '
    '~~> Rest of your code
    '

    Application.SendKeys "{TAB}", True

    Wait 2

    Application.SendKeys "^{C}", True

    '
    '~~> Rest of your code
    '
End Sub

Private Sub Wait(ByVal nSec As Long)
    nSec = nSec + Timer
    While nSec > Timer
        DoEvents
    Wend
End Sub

what would be a better solution?

Use APIs as shown Here. This doesn't directly answer your question but it explains how the concept works.

So applying that would be something like this

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Dim Ret As Long

Sub Sample()
    Ret = FindWindow(vbNullString, "Microsoft Access - Filename that is constant")

    If Ret <> 0 Then
        MsgBox "Window Found"
    Else
        MsgBox "Window Not Found"
    End If
End Sub

If you wish to become good at API’s like FindWindow, FindWindowEx and SendMessage then get a tool that gives you a graphical view of the system’s processes, threads, windows, and window messages. For Ex: uuSpy or Spy++. Another example which demonstrates how this API is used.

Answer 2

I figured it out. I copied the code from here: http://www.vbaexpress.com/forum/showthread.php?38826-SendInput()-in-Excel-64Bit I changed VkkeyMenu to VbKeyControl and the "f" key to "C". I know it could be simplified to take up less lines, but I'd rather not mess with it if it works like the saying "If it ain't broke, don't fix it." Code:

Private Declare PtrSafe Function SendInput Lib "user32" (ByVal nInputs As LongPtr, pInputs As Any, ByVal cbSize As LongPtr) As LongPtr
Private Declare PtrSafe Function VkKeyScan Lib "user32" Alias "VkKeyScanA" (ByVal cChar As Byte) As Integer
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Private Type KeyboardInput '   creating variable type
dwType As Long '   input type (keyboard or mouse)
wVk As Integer '   the key to press/release as ASCSI scan code
wScan As Integer '   not required
dwFlags As Long '   specify if key is pressed or released
dwTime As Long '   not required
dwExtraInfo As Long '   not required
dwPadding As Currency '   only required for mouse inputs
End Type



' SendInput constants
Private Const INPUT_KEYBOARD As Long = 1


Private Const KEYEVENTF_EXTENDEDKEY As Long = 1
Private Const KEYEVENTF_KEYUP As Long = 2


' Member variables


Private TheKeys() As KeyboardInput
Private NEvents As Long




Sub testage()


ReDim TheKeys(0 To 3)


With TheKeys(0)

    .dwType = INPUT_KEYBOARD 'operation type
    .wVk = vbKeyControl 'press CTRL key

End With


With TheKeys(1)


    .dwType = INPUT_KEYBOARD ' operation
    .wVk = VkKeyScan(Asc("C")) 'press chr key

End With


With TheKeys(2)

    .dwType = INPUT_KEYBOARD 'operation type
    .wVk = VkKeyScan(Asc("C"))
    .dwFlags = KEYEVENTF_KEYUP 'release chr key

End With


With TheKeys(3)


    .dwType = INPUT_KEYBOARD ' operation type
    .wVk = vbKeyControl
    .dwFlags = KEYEVENTF_KEYUP 'release CTRL Key


End With
Call SendInput(4, TheKeys(0), Len(TheKeys(0)))


Erase TheKeys


End Sub