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
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(....
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With