如何检测Excel Instance运行状态并动态获取所有已打开Excel Workbook的名称

Got it, let's break this down into practical, actionable solutions—whether you're working directly in Excel with VBA or building an external app to monitor Excel instances. Here's how to get it done:

If you're working inside Excel itself, these macros will handle both detecting instances and listing open workbooks.

1. Check if Any Excel Instance is Running #

This uses the GetObject function to test for active Excel processes. It catches errors if no instance exists:

Sub CheckForExcelInstances()
    Dim xlApp As Object
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    On Error GoTo 0
    
    If Not xlApp Is Nothing Then
        MsgBox "An Excel instance is currently running!", vbInformation
        Set xlApp = Nothing
    Else
        MsgBox "No Excel instances are running.", vbInformation
    End If
End Sub

2. List All Open Workbooks (Current Instance) #

To get every workbook open in the Excel instance running your macro, just loop through the Workbooks collection:

Sub ListAllOpenWorkbooks()
    Dim wb As Workbook
    Dim wbNames As String
    
    If Workbooks.Count = 0 Then
        MsgBox "No workbooks are open in this Excel instance.", vbInformation
        Exit Sub
    End If
    
    For Each wb In Workbooks
        wbNames = wbNames & "- " & wb.Name & vbNewLine
    Next wb
    
    MsgBox "Open Workbooks:" & vbNewLine & vbNewLine & wbNames, vbInformation
End Sub

3. List Workbooks Across All Running Excel Instances #

If you need to capture workbooks from every active Excel window (not just your current one), use Windows API calls to enumerate all Excel instances:

Option Explicit

Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
    (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, _
    ByVal lpsz2 As String) As LongPtr

Private Declare PtrSafe Function GetWindowText Lib "user32" Alias "GetWindowTextA" _
    (ByVal hWnd As LongPtr, ByVal lpString As String, ByVal cch As Long) As Long

Private Declare PtrSafe Function AccessibleObjectFromWindow Lib "oleacc" _
    (ByVal hWnd As LongPtr, ByVal dwId As Long, riid As GUID, ppvObject As Object) As Long

Private Type GUID
    Data1 As Long
    Data2 As Integer
    Data3 As Integer
    Data4(0 To 7) As Byte
End Type

Sub ListAllWorkbooksAcrossInstances()
    Dim hWnd As LongPtr
    Dim xlApp As Object
    Dim wb As Workbook
    Dim wbNames As String
    Dim IID_IDispatch As GUID
    
    ' Initialize GUID for IDispatch
    With IID_IDispatch
        .Data1 = &H20400
        .Data2 = &H0
        .Data3 = &H0
        .Data4(0) = &HC0
        .Data4(1) = &H0
        .Data4(2) = &H0
        .Data4(3) = &H0
        .Data4(4) = &H0
        .Data4(5) = &H0
        .Data4(6) = &H0
        .Data4(7) = &H46
    End With
    
    hWnd = FindWindowEx(0&, 0&, "XLMAIN", vbNullString)
    
    Do While hWnd <> 0
        On Error Resume Next
        AccessibleObjectFromWindow hWnd, &HFFFFFFF0, IID_IDispatch, xlApp
        On Error GoTo 0
        
        If Not xlApp Is Nothing Then
            For Each wb In xlApp.Workbooks
                wbNames = wbNames & "- Instance ID: " & ObjPtr(xlApp) & " | Workbook: " & wb.Name & vbNewLine
            Next wb
            Set xlApp = Nothing
        End If
        
        hWnd = FindWindowEx(0&, hWnd, "XLMAIN", vbNullString)
    Loop
    
    If wbNames = "" Then
        MsgBox "No open workbooks found across any Excel instances.", vbInformation
    Else
        MsgBox "All Open Workbooks Across Instances:" & vbNewLine & vbNewLine & wbNames, vbInformation
    End If
End Sub

Quick heads-up: This uses Windows API, so it's Windows-only. You may also need to enable "Trust access to the VBA project object model" in Excel's Trust Center settings to avoid permission issues.

If you're building a desktop app to monitor Excel from outside, use the Office Interop library alongside Windows API calls.

1. Set Up the Project #

First, install the NuGet package: Microsoft.Office.Interop.Excel

2. Detect Instances and List All Open Workbooks #

using System;
using System.Collections.Generic;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;

class ExcelMonitor
{
    [DllImport("user32.dll", SetLastError = true)]
    static extern IntPtr FindWindowEx(IntPtr hwndParent, IntPtr hwndChildAfter, string lpszClass, string lpszWindow);

    [DllImport("oleacc.dll")]
    static extern int AccessibleObjectFromWindow(IntPtr hwnd, uint dwId, ref Guid riid, [Out, MarshalAs(UnmanagedType.IUnknown)] out object ppvObject);

    static void Main(string[] args)
    {
        var allWorkbooks = new List<string>();
        Guid iidIDispatch = new Guid("{00020400-0000-0000-C000-000000000046}");
        
        IntPtr hwnd = IntPtr.Zero;
        // Find all XLMAIN windows (Excel's main window class)
        while ((hwnd = FindWindowEx(IntPtr.Zero, hwnd, "XLMAIN", null)) != IntPtr.Zero)
        {
            if (AccessibleObjectFromWindow(hwnd, 0xFFFFFFF0, ref iidIDispatch, out object obj) == 0)
            {
                if (obj is Application xlApp)
                {
                    foreach (Workbook wb in xlApp.Workbooks)
                    {
                        allWorkbooks.Add($"Instance Window ID: {xlApp.Hwnd} | Workbook: {wb.Name}");
                    }
                    Marshal.ReleaseComObject(xlApp);
                }
            }
        }

        if (allWorkbooks.Count == 0)
        {
            Console.WriteLine("No Excel instances or open workbooks found.");
        }
        else
        {
            Console.WriteLine("All Open Excel Workbooks:");
            foreach (var wb in allWorkbooks)
            {
                Console.WriteLine($"- {wb}");
            }
        }
    }
}

Critical note: Always release COM objects properly (like Marshal.ReleaseComObject(xlApp))—if you skip this, Excel might linger as a background process even after closing the app. This only works on Windows, as it relies on Windows API and Office Interop.

内容的提问来源于stack exchange，提问作者Jatin Sharma