vba - Sendkey 两次就成功了？

Question

我使用 sendkey 访问 Power Query 并连接到 SharePoint 文件夹。一切都很顺利，直到出现 Power Query 数据预览对话框。

如何允许 sendkey 在对话框出现后继续？我正在使用按钮启动宏并使用 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

更新

还尝试使用 True 两次 sendkey 但结果相同，在对话框中停止。

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

更新2

我尝试了@peh的建议，使用sleep()和Application.wait()。我发现一旦宏初始化，sendkey1 就会由 Application.wait() 启动和停止。只有等待时间结束后，sendkey1才会被处理。一旦 sendkey1 启动，sendkey2 也会启动。

还尝试添加 DoEvents，sendkey1 效果完美。但是，只有单击取消按钮后，Application.wait() 和 sendkey2 才会启动。

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"

面板

Answer 1

如果对话框每次都相同，或者在标题中包含一致的文本字符串，您可以使用它的标题来检测它何时出现，在循环中使用此函数，并使用一个定时器来搜索对话框的合理时间:

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

其中 sCaption 是对话框的名称。然后在代码主体中使用:

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