CODEWITHSUNDEEP
vbaexcelruntime-error
Jonathan
Jonathan

Reputation: 356

Detect if an object has been disconnected from its clients

I am having an issue with automating an Excel file. The VBA script within Excel first opens a Word application and Word document:

    Dim wordApp As Object
    Set wordApp = CreateObject("Word.Application")
    
    vPath = Application.ActiveWorkbook.Path
    Set wordDoc = wordApp.Documents.Open(vPath & "\test.doc")

And then I call a subroutine within the Word document passing some data from the Excel file:

    Call wordApp.Run("StartWithData", variable1, variable2)

If Excel detects that an error occurs in that subroutine, I close the Word document and Word application from Excel in a label I call Err1:

    On Error Goto Err1
    'all the code from above
    Exit Sub
    
    Err1:
    wordDoc.Close wdCloseWithoutSaving
    wordApp.Quit SaveChanges:=wdDoNotSaveChanges
    Set wordDoc = Nothing
    Set wordApp = Nothing

This works perfectly fine under normal circumstances; however, if the Word document or application are closed before the Err1 label executes (such as the user manually closing the document), I get the following error:

Run-time error '-2147417848 (80010108)':
Automation error The object invoked has disconnected from its clients.

which makes perfect sense because the wordApp and/or wordDoc variables still reference the Application and Document objects and those objects do not exist anymore (yet are also not considered to be Nothing).

So here is my inquiry: Is there a way to check if an object has been disconnected from its client before the run-time error occurs so as to avoid having to rely on on error resume next?

Such as:
    
    If Not isDisconnected(wordDoc) Then
    wordDoc.Close wdCloseWithoutSaving
    End If
    
    If Not isDisconnected(wordApp) Then
    wordApp.Quit SaveChanges:=wdDoNotSaveChanges
    End If

Update 1:

After looking at omegastripes' answer, I realized that the error given above only occurs when the document (wordDoc) was the object that got disconnected. If the Word application (wordApp) is what got disconnected, I get the following error:

Run-time error '462':

The remote server machine does not exist or is unavailable

Upvotes: 4

Views: 1458

Answers (1)

omegastripes
omegastripes

Reputation: 12602

Consider the below example:

Sub Test()
    Dim wordApp As Object
    Dim wordWnd As Object
    Dim wordDoc As Object

    Set wordApp = CreateObject("Word.Application")
    Set wordWnd = wordApp.Windows ' choose any object property as indicator
    wordApp.Visible = True ' debug
    Set wordDoc = wordApp.Documents.Open(Application.ActiveWorkbook.Path & "\test.doc")
    MsgBox IsObjectDisconnected(wordWnd) ' False with opened document
    wordDoc.Close
    MsgBox IsObjectDisconnected(wordWnd) ' False with closed document
    wordApp.Quit ' disconnection
    MsgBox IsObjectDisconnected(wordWnd) ' True with quited application
End Sub

Function IsObjectDisconnected(objSample As Object) As Boolean
    On Error Resume Next
    Do
        IsObjectDisconnected = TypeName(objSample) = "Object"
        If Err = 0 Then Exit Function
        DoEvents
        Err.Clear
    Loop
End Function

Seems any type detection of the variable, which references to the intrinsic Word objects, like .Documents, .Windows, .RecentFiles, etc., made immediately after document close or application quit commands have been invoked, may throw the error 14: Out of string space, while Word application processing the command. The same detection on the Applicationobject , may also hang Excel application.

In the example TypeName() call is wrapped into OERN loop, that should skip irrelevant results to get explicit disconnection feedback, relying on the type name, but not on the error number. To avoid hanging, .Windows property is being checked instead of Application.

Upvotes: 3

Related Questions