Resume macro to next line when get Prompt

[SmarterLife]

cross post :

https://stackoverflow.com/questions...macro-code-while-waiting-for-other-applicatio

Error prompt :
Microsoft Excel is waiting for another application to complete an OLE action

I not looking for way to prevent that prompt coming .
but i need a way to tell Excel to stop the waiting and move to next line of code so that it able to dismiss the prompt

1) Ignoreremoterequests not working . maybe i did'nt code it correctly
2) Intranet and confidential web , cant share the URL . belive we can discuss with it
3) cannot have add in / vb script.

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">applicationApplication.IgnoreRemoteRequests = true

With ie.document.querySelector("#query4")
.Click
'potentially add wait here
DoEvents
.FireEvent "ondblclick"
End With

applicationApplication.IgnoreRemoteRequests = false</code>

 

[Logit]

.
You can include the statement :

On Error Resume Next

placed at the beginning of your macro.

That should cause Excel to ignore any errors and simply move on with the code but
it doesn't solve any errors that may arise.

 

[mikerickson]

If your VBA routine doesn't need the remote action to be completed in order for the VBA's task to be completed, why not initiate the remote action after the VBA has completed its task?

 

[SmarterLife]

.
You can include the statement :

On Error Resume Next

placed at the beginning of your macro.

That should cause Excel to ignore any errors and simply move on with the code but
it doesn't solve any errors that may arise.

thanks for suggestion.
adding On Error Resume Next will not solve the issue
because it will still trigger the prompt from Excel saying it's still waiting for another application
so basically it's not an error .

 

[SmarterLife]

If your VBA routine doesn't need the remote action to be completed in order for the VBA's task to be completed, why not initiate the remote action after the VBA has completed its task?

thanks for looking up the problem. i am not sure if i understand you correctly
how should i code it to handle it ?
may i explain the code abit and would appreciate if you may suggest the code to fix it

applicationApplication.IgnoreRemoteRequests = true <<- adding this do nothing , so will remove it

With ie.document.querySelector("#query4")
.Click
'potentially add wait here
DoEvents
.FireEvent "ondblclick" <<- this will trigger a message prompt from server side. the prompt need to be dismissed in order to go forward
End With
Application.sendkeys "{Enter}", true <<- this is handler for message from from server side. however it's with excel and this command does not send until the server side complete the OLE action. so we are in deadloop. Server wait for excel , excel wait for server.

applicationApplication.IgnoreRemoteRequests = false <<- adding this do nothing , so will remove it

 

[Logit]

.
Based on your comment :

Error prompt :
Microsoft Excel is waiting for another application to complete an OLE action

Hopefully another volunteer can assist.

 

[Jaafar Tribak]

I have successfully used the CoRegisterMessageFilter API in the past to prevent excel from getting locked when waiting for a response from another ole server such as from a second instance of excel which may happen to be in edit mode when the com call is performed.

Not sure if this will work for your specific requirement but give it a try and see what happens :

Option Explicit

[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If"]#If[/URL]  VBA7 Then
    Declare PtrSafe Function CoRegisterMessageFilter Lib "OLE32.DLL" (ByVal lFilterIn As LongPtr, ByRef lPreviousFilter As LongPtr) As Long
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else"]#Else[/URL] 
    Declare Function CoRegisterMessageFilter Lib "OLE32.DLL" (ByVal lFilterIn As Long, ByRef lPreviousFilter As Long) As Long
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End"]#End[/URL]  If



Sub Test()
    
    On Error Resume Next
    
    EnableThreadMessageFilter = True
    
       [COLOR=#ff0000][B] 'YOUR CODE GOES HERE  !!!!! .[/B][/COLOR]
    
    EnableThreadMessageFilter = False
    
    
    
   [COLOR=#008000] '=====================================================================================================================[/COLOR]
    [COLOR=#008000]'Debug code section executed Only if ole server busy.[/COLOR]
    
    Const DBG_EXCEPTION_NOT_HANDLED = &H80010001
    
    If Err = DBG_EXCEPTION_NOT_HANDLED Then
        Debug.Print
        Debug.Print Err.Description & vbLf & "OLE Server Busy."; " Error: " & "(&H" & Hex$(DBG_EXCEPTION_NOT_HANDLED) & ")"
    End If
   [COLOR=#008000] '======================================================================================================================[/COLOR]

End Sub


[COLOR=#008000]'Helper Routine.[/COLOR]
Property Let EnableThreadMessageFilter(ByVal Enable As Boolean)

    [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If"]#If[/URL]  VBA7 Then
        Static lMsgFilter As LongPtr
    [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else"]#Else[/URL] 
        Static lMsgFilter As Long
    [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End"]#End[/URL]  If
    
    Const S_OK = &H0
    
    If Enable Then
        If lMsgFilter = 0 Then
            If CoRegisterMessageFilter(0, lMsgFilter) = S_OK Then
                Debug.Print "Msg Filter Registered"; "   Msg Filter Ptr:"; lMsgFilter
            End If
        End If
    Else
        If lMsgFilter Then
            If CoRegisterMessageFilter(lMsgFilter, lMsgFilter) = S_OK Then
                lMsgFilter = 0
                Debug.Print "Msg Filter Revoked"
            End If
        End If
    End If

End Property

 

[SmarterLife]

I have successfully used the CoRegisterMessageFilter API in the past to prevent excel from getting locked when waiting for a response from another ole server such as from a second instance of excel which may happen to be in edit mode when the com call is performed.

Not sure if this will work for your specific requirement but give it a try and see what happens :

Option Explicit

[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If"]#If[/URL]  VBA7 Then
    Declare PtrSafe Function CoRegisterMessageFilter Lib "OLE32.DLL" (ByVal lFilterIn As LongPtr, ByRef lPreviousFilter As LongPtr) As Long
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else"]#Else[/URL] 
    Declare Function CoRegisterMessageFilter Lib "OLE32.DLL" (ByVal lFilterIn As Long, ByRef lPreviousFilter As Long) As Long
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End"]#End[/URL]  If



Sub Test()
    
    On Error Resume Next
    
    EnableThreadMessageFilter = True
    
       [COLOR=#ff0000][B] 'YOUR CODE GOES HERE  !!!!! .[/B][/COLOR]
    
    EnableThreadMessageFilter = False
    
    
    
   [COLOR=#008000] '=====================================================================================================================[/COLOR]
    [COLOR=#008000]'Debug code section executed Only if ole server busy.[/COLOR]
    
    Const DBG_EXCEPTION_NOT_HANDLED = &H80010001
    
    If Err = DBG_EXCEPTION_NOT_HANDLED Then
        Debug.Print
        Debug.Print Err.Description & vbLf & "OLE Server Busy."; " Error: " & "(&H" & Hex$(DBG_EXCEPTION_NOT_HANDLED) & ")"
    End If
   [COLOR=#008000] '======================================================================================================================[/COLOR]

End Sub


[COLOR=#008000]'Helper Routine.[/COLOR]
Property Let EnableThreadMessageFilter(ByVal Enable As Boolean)

    [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If"]#If[/URL]  VBA7 Then
        Static lMsgFilter As LongPtr
    [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else"]#Else[/URL] 
        Static lMsgFilter As Long
    [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End"]#End[/URL]  If
    
    Const S_OK = &H0
    
    If Enable Then
        If lMsgFilter = 0 Then
            If CoRegisterMessageFilter(0, lMsgFilter) = S_OK Then
                Debug.Print "Msg Filter Registered"; "   Msg Filter Ptr:"; lMsgFilter
            End If
        End If
    Else
        If lMsgFilter Then
            If CoRegisterMessageFilter(lMsgFilter, lMsgFilter) = S_OK Then
                lMsgFilter = 0
                Debug.Print "Msg Filter Revoked"
            End If
        End If
    End If

End Property

thank you for suggestion. you codes work well where the notification does'nt turn up . unfortunately it also not executing the next line of code even it suppressed the message. any other suggestion

 

[Jaafar Tribak]

thank you for suggestion. you codes work well where the notification does'nt turn up . unfortunately it also not executing the next line of code even it suppressed the message. any other suggestion

Hi SmarterLife,

The only other thing that I can think of is to install a WH_CBT hook or have a windows timer routine to detect the moent the prompt comes up and send a message to it in order to close it.

This is brute manner but may work. However not knowing the URL or other details, there is no way I can test the code.

BTW, What happens when you dismiss the prompt manually ?

 

[Jaafar Tribak]

I have given the windows cbt hook approach a shot but before we take this any further, did you try setting the Excel DisplayAletrs Property to False before running the code ?

Like This :

Sub Test()    
    
    Application.DisplayAlerts = False
    
        '[COLOR=#ff0000][B]YOUR CODE GOES HERE  !!!!! .[/B][/COLOR]
    
     Application.DisplayAlerts = True    


End Sub