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

About the primary objective, accessing the opened WorkBooks of an existing Excel instance (created running EXCEL.EXE. Which of course includes a request to the Shell to open an Excel-associated file extension).

The following method uses Console.WriteLine() just to evaluate (eventually setting a BreakPoint), the current values of some objects. It's clearly redundant (has to be deleted/commented out before release).

It creates a local List(Of Workbook), which is the returned to the caller:
Note that each time an Interop object is created is then marshalled and set to nothing.
Why both? Inspect the objects when debugging and you'll see.

The Process.GetProcessesByName("EXCEL") is also redundant. Again, used only to evaluate the returned Process objects and inspect their values.

The Excel active Instance (if any) is accessed using Marshal.GetActiveObject()
Note that this will not create a new Process. We are accessing the existing instance.

Visual Studio Version: 15.7.6 - 15.8.3
.Net FrameWork version: 4.7.1
Option Strict: On, Option Explicit: On, Option Infer: On

Public Function FindOpenedWorkBooks() As List(Of Workbook)
    Dim OpenedWorkBooks As New List(Of Workbook)()

    Dim ExcelInstances As Process() = Process.GetProcessesByName("EXCEL")
    If ExcelInstances.Count() = 0 Then
        Return Nothing
    End If

    Dim ExcelInstance As Excel.Application = TryCast(Marshal.GetActiveObject("Excel.Application"), Excel.Application)
    If ExcelInstance Is Nothing Then Return Nothing
    Dim worksheets As Sheets = Nothing
    For Each WB As Workbook In ExcelInstance.Workbooks
        OpenedWorkBooks.Add(WB)
        worksheets = WB.Worksheets
        Console.WriteLine(WB.FullName)
        For Each ws As Worksheet In worksheets
            Console.WriteLine(ws.Name)
            Marshal.ReleaseComObject(ws)
        Next
    Next

    Marshal.ReleaseComObject(worksheets)
    worksheets = Nothing
    Marshal.FinalReleaseComObject(ExcelInstance)
    Marshal.CleanupUnusedObjectsInCurrentContext()
    ExcelInstance = Nothing
    Return OpenedWorkBooks
End Function

The returned List(Of Workbook) contains active objects. Those objects have not been marshalled and are accessible.

You can call the FindOpenedWorkBooks() method like this:
(Some values, as WorkSheet.Columns.Count, are worthless. Those are used to show that you access each WorkSheet values in each of the Sheets returned, for all the WorkBooks found)

The Excel.Range object created to access the value of a Cell (the first Column Header, here):
Dim CellRange As Excel.Range = CType(ws.Cells(1, 1), Excel.Range) is a new Interop object, so it is released after its value has been evaluated.

Private ExcelWorkBooks As List(Of Workbook) = New List(Of Workbook)()

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    ExcelWorkBooks = FindOpenedWorkBooks()

    If ExcelWorkBooks IsNot Nothing Then
        Dim WBNames As New StringBuilder()
        For Each wb As Workbook In ExcelWorkBooks
            WBNames.AppendLine(wb.Name)
            Dim sheets As Sheets = wb.Worksheets
            Console.WriteLine($"Sheets No.: { sheets.Count}")
            For Each ws As Worksheet In sheets
                Console.WriteLine($"WorkSheet Name: {ws.Name}  Columns: {ws.Columns.Count}  Rows: {ws.Rows.Count}")
                Dim CellRange As Excel.Range = CType(ws.Cells(1, 1), Excel.Range)
                Console.WriteLine(CellRange.Value2.ToString)
                Marshal.ReleaseComObject(CellRange)
                Marshal.ReleaseComObject(ws)
            Next

            Marshal.ReleaseComObject(sheets)
        Next
        MessageBox.Show(WBNames.ToString())
    End If
End Sub

What objects must be Released? All the objects you create.

Suppose you have to open a new Excel file and you want to access a WorkBook inside it.
(This will create a new Process)

Dim WorkBook1Path As String = "[Some .xlsx Path]"
Dim ExcelApplication As New Excel.Application()
Dim ExcelWorkbooks As Workbooks = ExcelApplication.Workbooks
Dim MyWorkbook As Workbook = ExcelWorkbooks.Open(WorkBook1Path, False)
Dim worksheets As Sheets = MyWorkbook.Worksheets
Dim MyWorksheet As Worksheet = CType(worksheets("Sheet1"), Worksheet)

'(...)
'Do your processing here
'(...)

Marshal.ReleaseComObject(MyWorksheet)
Marshal.ReleaseComObject(worksheets)
MyWorkbook.Close(False) 'Don't save
Marshal.ReleaseComObject(MyWorkbook)
ExcelWorkbooks.Close()
Marshal.ReleaseComObject(ExcelWorkbooks)
ExcelApplication.Quit()
Marshal.FinalReleaseComObject(ExcelApplication)
Marshal.CleanupUnusedObjectsInCurrentContext()

Again, all objects must be released. WorkBooks must be .Close()d and their content saved if required. The WorkBooks collection must be .Close()d.
Use the Excel.Application main object .Quit() method to notify the end of the operations.
.Quit() will not terminate the Process you created.
Marshal.FinalReleaseComObject(ExcelApplication) is used to finalize it's release.
At this point the EXCEL process will end.

The last instruction, Marshal.CleanupUnusedObjectsInCurrentContext()`, is a clean-up precaution.
May not be even necessary, but it doesn't hurt: we're quitting here.

Of course you can instantiate all those objects once, in the initialization proc of you application, then Marshal them when the application closes.
When using a Form class, it creates a Dispose() method that can be used for this task.
If you are implementing these procedure in your own class, implement the IDisposable interface and implement the required Dispose() method.

But, what if you don't want or can't take care of all those objects instantiation/destruction?
Possibly, you prefer to use Type Inference when instantiating new objects. So you set Option Explicit and Option Strict ON, while keeping Option Infer On. Many do so.

So you write something like:
Dim MyWorkbook = ExcelWorkbooks.Open([FilePath], False)

instead of:
Dim MyWorkbook As Workbook = ExcelWorkbooks.Open([FilePath], False)

Sometimes it's clear what object(s) has(have) been created to satisfy your request.
Sometimes absolutely not.

Thus, many prefer to implement a different pattern to Release/Dispose Interop objects.

You can see many ways here (c#, mainly, but its the same):
How do I properly clean up Excel interop objects?

This thoughtful implementation:
Application not quitting after calling quit

Also, a peculiar way described by TnTinMn here:
Excel COM Object not getting released

Test, find your way :).
Never use Process.Kill(). Among other things, you don't know what you're terminating.

Also, some interesting readings about COM marshalling in managed/unmanaged code:

The Visual Studio Engineering Team:
Marshal.ReleaseComObject Considered Dangerous

Hans Passant on COM marshalling and Garbage Collection:
Understanding garbage collection in .NET

MSDN docs about Runtime Callable Wrapper (RCW) and COM Callable Wrapper (CCW)
Runtime Callable Wrapper
COM Callable Wrapper

Answer 2

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