GetObject xxx Application not working recognizing open files

Question

Here's a code snipped exemplifying the issue I'm having:

Private Sub document_open()

    Dim excelObj As Excel.Application

    Err.Clear 'just pro forma, in case there's any previous junk left

    On Error Resume Next

    Set excelObj = GetObject("Book1").Application
    excelObj.Visible = True 'not necessary, I guess but I left it there anyway, tried removing it, tried changing it to false - no difference
    MsgBox excelObj Is Nothing 'for testing purposes

End Sub

I entered and executed that code on an un-saved, newly generated file (Book1). It gave a FALSE response, indicating that it has found the operating Excel instance with the specified file. I pressed Ctrl+N to create Book2, ran the code for it, and once more received a FALSE.

When I ran the code again after saving Book2 but leaving it open, it returned a TRUE, indicating that the script had failed to identify it as an instance of Excel. I ran it with Book1 and it returned FALSE. I then ran the script with "Book1" and "Book1.xlsm," saved Book1 (of course as an.xlsm), and received a TRUE.

I closed everything, checked the Task Manager to be sure there were no active Excel instances, opened both files, ran the script, and TRUE.

The consequence is that a saved open file cannot be used by script to identify an Excel instance; only an unsaved open file can.

Answer 1

You can reference open Excel files in a different way to get around this problem. One choice is to loop over all open workbooks in the Application.Workbooks collection and see whether the name of any workbooks matches the filename you're looking for. Here's an example:

Sub Example()
    Dim wb As Workbook
    Dim filename As String
    filename = "Book1.xlsx" ' replace with the filename you are looking for
    For Each wb In Application.Workbooks
        If wb.Name = filename Then
            MsgBox "Found " & filename & " in Excel instance " & wb.Application.Caption
            Exit For
        End If
    Next wb
End Sub