Results 1 to 10 of 10

Thread: Resume macro to next line when get Prompt
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Resume macro to next line when get Prompt

    cross post :

    https://stackoverflow.com/questions/...her-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.

    applicationApplication.IgnoreRemoteRequests = true

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

    applicationApplication.IgnoreRemoteRequests = false

  2. #2
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    2,806
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Resume macro to next line when get Prompt

    .
    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.

  3. #3
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,603
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Resume macro to next line when get Prompt

    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?

  4. #4
    New Member
    Join Date
    Sep 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Resume macro to next line when get Prompt

    Quote Originally Posted by Logit View Post
    .
    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 .

  5. #5
    New Member
    Join Date
    Sep 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Resume macro to next line when get Prompt

    Quote Originally Posted by mikerickson View Post
    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

  6. #6
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    2,806
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Resume macro to next line when get Prompt

    .
    Based on your comment :

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

    Hopefully another volunteer can assist.

  7. #7
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,351
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Resume macro to next line when get Prompt

    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
    
    #If  VBA7 Then
        Declare PtrSafe Function CoRegisterMessageFilter Lib "OLE32.DLL" (ByVal lFilterIn As LongPtr, ByRef lPreviousFilter As LongPtr) As Long
    #Else 
        Declare Function CoRegisterMessageFilter Lib "OLE32.DLL" (ByVal lFilterIn As Long, ByRef lPreviousFilter As Long) As Long
    #End  If
    
    
    
    Sub Test()
        
        On Error Resume Next
        
        EnableThreadMessageFilter = True
        
            'YOUR CODE GOES HERE  !!!!! .
        
        EnableThreadMessageFilter = False
        
        
        
        '=====================================================================================================================
        'Debug code section executed Only if ole server busy.
        
        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
        '======================================================================================================================
    
    End Sub
    
    
    'Helper Routine.
    Property Let EnableThreadMessageFilter(ByVal Enable As Boolean)
    
        #If  VBA7 Then
            Static lMsgFilter As LongPtr
        #Else 
            Static lMsgFilter As Long
        #End  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 by Jaafar Tribak; Sep 8th, 2019 at 10:12 AM.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  8. #8
    New Member
    Join Date
    Sep 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Resume macro to next line when get Prompt

    Quote Originally Posted by Jaafar Tribak View Post
    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
    
    #If  VBA7 Then
        Declare PtrSafe Function CoRegisterMessageFilter Lib "OLE32.DLL" (ByVal lFilterIn As LongPtr, ByRef lPreviousFilter As LongPtr) As Long
    #Else 
        Declare Function CoRegisterMessageFilter Lib "OLE32.DLL" (ByVal lFilterIn As Long, ByRef lPreviousFilter As Long) As Long
    #End  If
    
    
    
    Sub Test()
        
        On Error Resume Next
        
        EnableThreadMessageFilter = True
        
            'YOUR CODE GOES HERE  !!!!! .
        
        EnableThreadMessageFilter = False
        
        
        
        '=====================================================================================================================
        'Debug code section executed Only if ole server busy.
        
        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
        '======================================================================================================================
    
    End Sub
    
    
    'Helper Routine.
    Property Let EnableThreadMessageFilter(ByVal Enable As Boolean)
    
        #If  VBA7 Then
            Static lMsgFilter As LongPtr
        #Else 
            Static lMsgFilter As Long
        #End  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

  9. #9
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,351
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Resume macro to next line when get Prompt

    Quote Originally Posted by SmarterLife View Post
    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 by Jaafar Tribak; Sep 9th, 2019 at 10:38 AM.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  10. #10
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,351
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Resume macro to next line when get Prompt

    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
        
            'YOUR CODE GOES HERE  !!!!! .
        
         Application.DisplayAlerts = True    
    
    
    End Sub
    Last edited by Jaafar Tribak; Sep 9th, 2019 at 02:12 PM.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •