Resume macro to next line when get Prompt

SmarterLife

New Member
Joined
Sep 7, 2019
Messages
4
cross post :

https://stackoverflow.com/questions/57824008/excel-automation-with-ie-execute-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

Well-known Member
Joined
Aug 31, 2016
Messages
2,924
.
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,801
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

New Member
Joined
Sep 7, 2019
Messages
4
.
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

New Member
Joined
Sep 7, 2019
Messages
4
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

Well-known Member
Joined
Aug 31, 2016
Messages
2,924
.
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

Well-known Member
Joined
Dec 5, 2002
Messages
7,493
Office Version
2016
Platform
Windows
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 :

Code:
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
 
Last edited:

SmarterLife

New Member
Joined
Sep 7, 2019
Messages
4
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 :

Code:
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

Well-known Member
Joined
Dec 5, 2002
Messages
7,493
Office Version
2016
Platform
Windows
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 ?
 
Last edited:

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,493
Office Version
2016
Platform
Windows
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 :
Code:
Sub Test()    
    
    Application.DisplayAlerts = False
    
        '[COLOR=#ff0000][B]YOUR CODE GOES HERE  !!!!! .[/B][/COLOR]
    
     Application.DisplayAlerts = True    


End Sub
 
Last edited:

Forum statistics

Threads
1,082,297
Messages
5,364,373
Members
400,793
Latest member
hamzayagiz

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top