Problematic Call to *GetObject* hanging the application

[Jaafar Tribak]

Hi all

If you call GetObject on a workbook opened in another excel instance ( workbook opened on the same pc or on a remote one) you get a pointer to that workbook ... This works fine . However, if the second workbook happens to be in edit mode or has a modal dialogbox on display (such as the Data Validation dialog) at the time the call to GetObject is made, the calling application hangs and all user interaction becomes impossible until the other workbook comes out of edit mode or the dialogbox is closed.

So, in order to prevent this problem from happening, it would be useful to check beforehand if the called workbook is in an edit mode or is displaying a dialog .. Anybody has an idea how to do that ? or maybe another workaround ?

Regards.

 

[Ruddles]

Off the top of my head... open a third workbook in another instance of Excel which just issues a GetObject to the second workbook and then closes, then wait for a while and see if it managed to close successfully. If that third workbook managed to close, your second workbook wasn't in edit mode and didn't have a modal dialogbox on display. If the third workbook didn't manage to close, it must be hanging, so I guess you'd have to force it to close (if that's possible... you might have to kill that instance of Excel) and then... what? Alert the user that the action on the second workbook needs to be completed? Or wait for whoever was using it to finish what they're doing and try again?

As I said, just a suggestion...

 

[Jaafar Tribak]

Thanks Ruddles for the answer.

Yes, I had thought of issuing a GetObject to the second workbook from an outide process which I guess would solve the application hanging problem (Although I haven't tested that yet) but I thought I would ask in case someone could think of a more direct method .

 

[Jaafar Tribak]

[FONT=&quot]Classic VB has the handy Property OLEServerBusyTimeout that permits setting a timeout for an automation request .. I guess this would have been ideal in this scenario if only there was a similar property in VBA

Has anybody come up against the problem described in this thread before when trying to send an automation request from excel or from another office application ?

I have done a search web but not much has come up on the subject... Maybe there is some under-the hood api solution somewhere.


[/FONT]

 

[Jaafar Tribak]

Ok, after some Win api digging, I think, I have this sorted out - hopefully .
The needed API was CoRegisterMessageFilter

I have wrapped the api in a custom GetObject Sub which I have named Safe_GetObject for easy use as follows :

Option Explicit

#If VBA7 Then
    Declare PtrSafe Function CoRegisterMessageFilter Lib "OLE32.DLL" (ByVal lFilterIn As Long, ByRef lPreviousFilter As Long) As Long
#Else
    Declare Function CoRegisterMessageFilter Lib "OLE32.DLL" (ByVal lFilterIn As Long, ByRef lPreviousFilter As Long) As Long
#End If


Sub Safe_GetObject(ByRef Obj As Object, Optional ByVal PathName As String, Optional ByVal Class As String)

    Const DBG_EXCEPTION_NOT_HANDLED = &H80010001
    Dim lMsgFilter As Long
    Dim sName As String
    Dim sErrMsg As String
    
    
    On Error GoTo ErrHandler
    Call CoRegisterMessageFilter(0, lMsgFilter)
    Select Case True
        Case Len(PathName) = 0
            Set Obj = GetObject(, Class)
        Case Len(Class) = 0
            Set Obj = GetObject(PathName)
    End Select
    sName = Obj.Name
    Call CoRegisterMessageFilter(lMsgFilter, lMsgFilter)
    Exit Sub
    
ErrHandler:
    sErrMsg = Err.Description
    If Err = DBG_EXCEPTION_NOT_HANDLED Then
        sErrMsg = Err.Description & vbLf & vbLf & "The Server Application may be Busy or in an Disabled state."
        sErrMsg = sErrMsg & vbLf & "Try again later."
        Set Obj = Nothing
    End If
    MsgBox sErrMsg, vbCritical, "Error " & Err & " (&H" & Hex$(Err) & ")"
End Sub

The Server Object is returned in the first ByRef argument of the Safe_GetObject Sub .

Below is a test macro :

Sub Test()

    Dim oRemoteWorkbook As Workbook
    
    Call Safe_GetObject(oRemoteWorkbook, "C:\Test\Callee.xls")
    If Not oRemoteWorkbook Is Nothing Then
        MsgBox "Connected to :  '" & oRemoteWorkbook.FullName & "  '", vbInformation, "Successful connection"
[B][COLOR=#006400]        'Continue code here ..........[/COLOR][/B]
    End If
End Sub

Now, if the Callee workbook (opened in another instance of excel) is in edit mode or has a modal dialogbox displayed at the time of the automation request when you run the Test Macro , the application no longer hangs... Instead it raises a user freindly error message and continues.

 

[dbooksta]

NB: This solution doesn't appear to work with VBA7. Solution here does.