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.