Sendkey twice based on success?

Question

I was using sendkey to access Power Query and connect to SharePoint Folder. Everything was smooth until the Power Query Data Preview Dialog appears.

How do I allow sendkey to continue after the dialog appears? I'm using button to start macro and using Excel 2016.

Option Explicit
Sub Button1_Click()

    Dim spPath As String
    Dim strkeys As String

    spPath = "" 'SharePoint Link

    strkeys = "%APNFO" & spPath & "{Enter}{TAB 4}{Enter}" 
    'stops at first{Enter}, {TAB 4}{Enter} for EDIT
    Call SendKeys(strkeys)

End Sub

Update

Also tried to sendkey twice with True but same result, Stops at dialog.

Option Explicit
Sub Button1_Click()

    Dim spPath As String
    Dim strkeys As String
    Dim strkeys2 As String

    spPath = ""

    strkeys = "%APNFO" & spPath & "{Enter}"
    strkeys2 = "{TAB 4}{Enter}"
    Call SendKeys(Trim(strkeys), True)
    Call SendKeys(Trim(strkeys2), True)
    Debug.Print strkeys2

End Sub

Update2

I tried what @peh suggested, using sleep() and Application.wait(). I found out that once the macro is initialized, sendkey1 started and stopped by the Application.wait(). Only after the waiting time ends, then sendkey1 is being processed. And once sendkey1 started, sendkey2 also starts.

Also tried adding DoEvents, sendkey1 works perfect. However only after clicking the Cancel button, Application.wait() and sendkey2 will start.

Call SendKeys(Trim(strkeys))
Debug.Print Now & "Send Key 1"
'Do Events
Application.wait (Now + TimeValue("0:00:10"))
Call SendKeys(Trim(strkeys2), True)
Debug.Print Now & "Send Key 2"

Pannel

what you need is a command to wait some seconds between the 2 sendkeys to give the dialog time to load. See: [How to pause for specific amount of time? (Excel/VBA)](https://stackoverflow.com/questions/1544526/how-to-pause-for-specific-amount-of-time-excel-vba). But you can't guarantee that the dialog is really loaded after that amount of time, just estimate. — Pᴇʜ, Dec 15 '17 at 08:19
If you want to be sure the dialogue box has loaded instead of just using a timer, check my answer below. — Petrichor, Dec 15 '17 at 08:38
Will test out. The dialog only contains the SharePoint link with data preview. Not sure whether it takes the link as captions. — aaa, Dec 15 '17 at 08:43
Have you considered using the Microsoft UI Automation framework? See: https://learn.microsoft.com/en-us/dotnet/framework/ui-automation/ui-automation-overview. I've used this in VBA before for some stubborn buttons etc. There is a DLL that you can access with VBA. This could be a more reliable method to do what you are after. — Ryan Wildry, Dec 20 '17 at 01:23

Petrichor · Answer 1 · 2017-12-15T08:36:48.003

6

If the dialogue box is the same every time, or contains a consistent string of text in the caption, you may be able to use it's caption to detect when it appears using this function in a loop with a timer that searches for a reasonable amount of time for the dialogue box:

Private Function GetHandleFromPartialCaption(ByRef lWnd As Long, ByVal sCaption As String) As Boolean

Dim lhWndP As Long
Dim sStr As String
GetHandleFromPartialCaption = False
lhWndP = FindWindow(vbNullString, vbNullString) 'PARENT WINDOW
Do While lhWndP <> 0
    sStr = String(GetWindowTextLength(lhWndP) + 1, Chr$(0))
    GetWindowText lhWndP, sStr, Len(sStr)
    sStr = Left$(sStr, Len(sStr) - 1)
    If InStr(1, sStr, sCaption) > 0 Then
        GetHandleFromPartialCaption = True
        lWnd = lhWndP
        Exit Do
    End If
    lhWndP = GetWindow(lhWndP, GW_HWNDNEXT)
Loop
End Function

Where sCaption is the name of your dialogue box. Then in your main body of code use:

If GetHandleFromPartialCaption(lhWndP, "Your Dialogue Box Caption") = True Then
SendKeys(....

edited Dec 15 '17 at 08:36

answered Dec 15 '17 at 08:31

Petrichor

975
1
9
22

Great solution! – Mafii Dec 15 '17 at 09:41
1

I have been working with a similar issue :) If you found my answer helpful, please mark it as an answer or upvote @ppz - Thanks and Merry Christmas! – Petrichor Dec 15 '17 at 09:42
Hi, sorry just managed to test out your answer. It does not seem to work,the function and `FindWindow` is highlighted, i think something went wrong while searching the windows. I'm following your guide `If GetHandleFromPartialCaption(lhWndP, spPath) = True Then Call SendKeys(Trim(strkeys2), True) Debug.Print spPath End If` – aaa Dec 18 '17 at 00:44
If that screenshot above is your pop up then this will not work as far as I'm aware. That window does not have a caption for my function to search for, sorry about that. The image wasn't available when i posted this answer, I was assuming it was a standard windows pop up window with a caption. – Petrichor Dec 18 '17 at 08:34
@Petrichor Anyways for the function, it's still very useful. I'm aware of that and updated that's why I updated the Image for reference. :) – aaa Dec 18 '17 at 08:42

Cora Muirgen · Answer 2 · 2017-12-21T15:02:40.087

I am on my linux box right now so I can't tinker with this to test, but you might attempt to read other properties of the window with a utility like:

https://autohotkey.com/boards/viewtopic.php?t=28220

Edit: if SendKeys absolutely won't work, and you don't want to go the UI automation route, and you don't mind a dependency, you could install AutoHotkey and script that from VBA (e.g. using the Shell() command). AHK is more robust when it comes to keyboard macro automation.

If you had a unique classname, for example, you could use FindWindowEx to get the window handle:

Module-scoped ~

#If VBA7 Then
'32-bit declare
Private Declare Function FindWindowEx Lib "USER32" _
                              Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
                              ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
#Else
'64-bit declare
Private Declare PtrSafe Function FindWindowEx Lib "USER32" _
                              Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, _
                              ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr
#End If

Procedure ~

Dim appcaption as String
appcaption = "Excel"

#If VBA7 Then
Dim parenthandle as Long, childhandle as Long
#Else
Dim parenthandle as LongPtr, childhandle as LongPtr
#End If
parenthandle = FindWindow(vbNullString, appcaption)

If parenthandle Then

    childhandle = GetWindow(parenthandle, GW_CHILD)1   
    Do Until Not childhandle
        childhandle = GetWindow(childhandle, GW_HWNDNEXT)
    Loop

End If

If childhandle Then
    '
End If

This code is only proof of concept, as you could have muliple Excel Windows open, for example. It should give a good starting point, however.

Sendkey twice based on success?

2 Answers2