Get running instances of Excel with VB.NET

Question

I have the following working code taken from this answer:

Option Compare Binary
Option Explicit On
Option Infer On
Option Strict Off

Imports Microsoft.Office.Interop
Imports System.Collections.Generic
Imports System.Runtime.InteropServices

Friend Module Module1
    Private Declare Function GetDesktopWindow Lib "user32" () As IntPtr
    Private Declare Function EnumChildWindows Lib "user32.dll" (ByVal WindowHandle As IntPtr, ByVal Callback As EnumWindowsProc, ByVal lParam As IntPtr) As Boolean
    Private Declare Function GetClassName Lib "user32.dll" Alias "GetClassNameA" (ByVal hWnd As IntPtr, ByVal lpClassName As String, ByVal nMaxCount As Integer) As Integer
    Private Delegate Function EnumWindowsProc(ByVal hwnd As IntPtr, ByVal lParam As Int32) As Boolean
    Private Declare Function AccessibleObjectFromWindow Lib "oleacc" (ByVal Hwnd As IntPtr, ByVal dwId As Int32, ByRef riid As Guid, <MarshalAs(UnmanagedType.IUnknown)> ByRef ppvObject As Object) As Int32
    Private lstWorkBooks As New List(Of String)
    Public Sub Main()
        GetExcelOpenWorkBooks()
    End Sub
    Private Sub GetExcelOpenWorkBooks()
        EnumChildWindows(GetDesktopWindow(), AddressOf GetExcelWindows, CType(0, IntPtr))
        If lstWorkBooks.Count > 0 Then MsgBox(String.Join(Environment.NewLine, lstWorkBooks))
    End Sub
    Public Function GetExcelWindows(ByVal hwnd As IntPtr, ByVal lParam As Int32) As Boolean
        Dim Ret As Integer = 0
        Dim className As String = Space(255)
        Ret = GetClassName(hwnd, className, 255)
        className = className.Substring(0, Ret)
        If className = "EXCEL7" Then
            Dim ExcelApplication As Excel.Application
            Dim ExcelObject As Object = Nothing
            Dim IDispatch As Guid
            AccessibleObjectFromWindow(hwnd, &HFFFFFFF0, IDispatch, ExcelObject)
            If ExcelObject IsNot Nothing Then
                ExcelApplication = ExcelObject.Application
                If ExcelApplication IsNot Nothing Then
                    For Each wrk As Excel.Workbook In ExcelApplication.Workbooks
                        If Not lstWorkBooks.Contains(wrk.Name) Then
                            lstWorkBooks.Add(wrk.Name)
                        End If
                    Next
                End If
            End If
        End If
        Return True
    End Function
End Module

It will be used to get references of all the opened/running Excel instances/applications.

Without looking it up online I would never guess how to do it as I don't understand much of it, so it's probably not the best way to do it and is bug/error prone. I'm trying to turn option strict on (1, 2) so I changed the line ExcelApplication = ExcelObject.Application to ExcelApplication = CType(ExcelObject, Excel.Application).Application but doing so throws the exception:

System.InvalidCastException Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Interop.Excel.Application'. This operation failed because the Query Interface call on the COM component for the interface with IID '{000208D5-0000-0000-C000-000000000046}' failed due to the following error: No such interface supported. (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

I can find multiple look alike references to this in different sites but haven't got the luck to fix it with the trial and error method.

My question is how to turn on option strict and bonus if someone helps me get a better code or fix/explain any other issues with it.

Answer 1

The other answer which I had previously marked as accepted is great, but there is a catch (*), which is that it only gets the active object, the first Excel process.

That is enough in most cases, but not in a specific one where there is more than one instance of Excel opened. From what I know, that is only possible by either holding the Alt key when starting Excel which prompts to start Excel in a new instance, or with code in some program.

On the other hand the code in the question does work and solve the issue of getting all running instances of Excel. The only problem that I was having was converting it from late binding (Option Strict Off) to early binding (Option Strict On) which was causing an error that I couldn't find the answer to, until now.

With the help of an answer in another question that approaches the issue in C# I found out that I had to replace the parameter ppvObject of the function AccessibleObjectFromWindow from:

<MarshalAs(UnmanagedType.IUnknown)> ByRef ppvObject As Object

To:

ByRef ppvObject As Excel.Window

And change the type of the variable ExcelObject in the declaration from Object to Excel.Window (also good practice to rename it to ExcelWindow in the code).