Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Sendkey twice based on success?

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

enter image description here

like image 768
aaa Avatar asked Dec 15 '17 07:12

aaa


1 Answers

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(....
like image 141
Petrichor Avatar answered Nov 14 '22 11:11

Petrichor