find all open Excel workbooks

Question

I'm trying to get a list of all currently open Excel workbooks, so that the user can select which one of them to get some data from.

I tried this:

List<string> excelList = new List<string>();
Process[] processList = Process.GetProcessesByName("excel");
foreach (Process p in processList)
{
 excelList.Add(p.MainWindowTitle);
 Console.WriteLine(p.MainWindowTitle);
}

But that only gets the first open instance of Excel and the most recently opened instance, so any workbooks that were opened between those two aren't in the list.

I also started exploring the solution described in the blog link in the answer to this SO question, and tried to get access to the Running Object Table with the code suggested in the blog entry:

IBindCtx bc;
IRunningObjectTable rot;
CreateBindCtx(0, out bc);
bc.GetRunningObjectTable(out rot);

Problem here is that CreateBindCtx actually accepts a UCOMIBindCTX instead of IBindCTX, but UCOMIBindCTX is obsolete per MSDN.

Is there a simpler way to do what I'm trying to do: get a list of Workbook objects corresponding to all the open Excel books?

Answer 1

Ok, I found a way to do this. The blog that describes the solution appears to no longer be available, but there is a Google cached version.

I slightly adapted the code so that the constructor accepts a MainWindowHandle, since I'm iterating through the handles for all processes.

The class is as follows. I've left some of Andrew Whitechapel's comments in to explain what's happening, since this code is beyond my present knowledge of Windows OS management:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

namespace DTEExcel
{
    class ExcelApplicationRetriever
    {
        [DllImport("Oleacc.dll")]
        public static extern int AccessibleObjectFromWindow(
              int hwnd, uint dwObjectID, byte[] riid,
              ref Microsoft.Office.Interop.Excel.Window ptr);

        [DllImport("User32.dll")]
        public static extern int GetClassName(
              int hWnd, StringBuilder lpClassName, int nMaxCount);

        [DllImport("User32.dll")]
        public static extern bool EnumChildWindows(
              int hWndParent, EnumChildCallback lpEnumFunc,
              ref int lParam);

        public delegate bool EnumChildCallback(int hwnd, ref int lParam);
        private EnumChildCallback cb;
        public Excel.Application xl;

        public ExcelApplicationRetriever(int winHandle)
        {
            // We need to enumerate the child windows to find one that
            // supports accessibility. To do this, instantiate the
            // delegate and wrap the callback method in it, then call
            // EnumChildWindows, passing the delegate as the 2nd arg.
            if (winHandle != 0)
            {
                int hwndChild = 0;
                cb = new EnumChildCallback(EnumChildProc);
                EnumChildWindows(winHandle, cb, ref hwndChild);

                // If we found an accessible child window, call
                // AccessibleObjectFromWindow, passing the constant
                // OBJID_NATIVEOM (defined in winuser.h) and
                // IID_IDispatch - we want an IDispatch pointer
                // into the native object model.
                if (hwndChild != 0)
                {
                    const uint OBJID_NATIVEOM = 0xFFFFFFF0;
                    Guid IID_IDispatch = new Guid(
                         "{00020400-0000-0000-C000-000000000046}");
                    Excel.Window ptr = null;

                    int hr = AccessibleObjectFromWindow(
                          hwndChild, OBJID_NATIVEOM,
                         IID_IDispatch.ToByteArray(), ref ptr);
                    if (hr >= 0)
                    {
                        // If we successfully got a native OM
                        // IDispatch pointer, we can QI this for
                        // an Excel Application (using the implicit
                        // cast operator supplied in the PIA).
                        xl = ptr.Application;
                    }
                }
            }
        }

        public bool EnumChildProc(int hwndChild, ref int lParam)
        {
            StringBuilder buf = new StringBuilder(128);
            GetClassName(hwndChild, buf, 128);
            if (buf.ToString() == "EXCEL7")
            {
                lParam = hwndChild;
                return false;
            }
            return true;
        }

    }
}

Answer 2

This code was put together using the resource (Link) in the last comment and the code you supplied. This should pull in all open workbook names.

using Excel = Microsoft.Office.Interop.Excel;        

[DllImport("User32")]
public static extern int GetClassName(
    int hWnd, StringBuilder lpClassName, int nMaxCount);


// Callback passed to EnumChildWindows to find any window with the
// registered classname "paneClassDC" - this is the class name of
// PowerPoint's accessible document window.
public bool EnumChildProc(int hwnd, ref int lParam)
{
    StringBuilder windowClass = new StringBuilder(128);
    GetClassName(hwnd, windowClass, 128);
    s += windowClass.ToString() + "\n";
    if (windowClass.ToString() == "EXCEL7")
    {
        lParam = hwnd;
    }
    return true;
}

public delegate bool EnumChildCallback(int hwnd, ref int lParam);


[DllImport("User32")]
public static extern bool EnumChildWindows(
    int hWndParent, EnumChildCallback lpEnumFunc, ref int lParam);


[DllImport("User32")]
public static extern int FindWindowEx(
    int hwndParent, int hwndChildAfter, string lpszClass,
    int missing);


// AccessibleObjectFromWindow gets the IDispatch pointer of an object
// that supports IAccessible, which allows us to get to the native OM.
[DllImport("Oleacc.dll")]
private static extern int AccessibleObjectFromWindow(
    int hwnd, uint dwObjectID,
    byte[] riid,
    ref Excel.Window ptr);


// Get the window handle for a running instance of PowerPoint.
internal List<String> GetAccessibleObject()
{

    List<String> workbookNames = new List<String>();
    try
    {
        // Walk the children of the desktop to find PowerPoint’s main
        // window.
        int hwnd = FindWindowEx(0, 0, "XLMAIN", 0);
        while(hwnd != 0)
        if (hwnd != 0)
        {
            // Walk the children of this window to see if any are
            // IAccessible.
            int hWndChild = 0;
            EnumChildCallback cb =
                new EnumChildCallback(EnumChildProc);
            EnumChildWindows(hwnd, cb, ref hWndChild);


            if (hWndChild != 0)
            {
                // OBJID_NATIVEOM gets us a pointer to the native 
                // object model.
                uint OBJID_NATIVEOM = 0xFFFFFFF0;
                Guid IID_IDispatch =
                    new Guid("{00020400-0000-0000-C000-000000000046}");
                Excel.Window ptr = null;
                int hr = AccessibleObjectFromWindow(
                    hWndChild, OBJID_NATIVEOM,
                    IID_IDispatch.ToByteArray(), ref ptr);
                if (hr >= 0)
                {
                    Excel.Application eApp = ptr.Application;
                    if (eApp != null)
                    {
                        foreach (Excel.Workbook wb in eApp.Workbooks)
                        {
                            workbookNames.Add(wb.FullName);
                        }
                        Marshal.ReleaseComObject(eApp);
                        GC.WaitForPendingFinalizers();
                        GC.Collect();
                    }
                }

                hwnd = FindWindowEx(0, hwnd, "XLMAIN", 0);
            }
        }
    }
    catch (Exception ex)
    {
        Debug.WriteLine(ex.ToString());
    }
    return workbookNames;
}

Answer 3

public void closeOpenedFile(string file_name)
{
    //Excel Application Object
    Microsoft.Office.Interop.Excel.Application oExcelApp;
    //Get reference to Excel.Application from the ROT.
    if (Process.GetProcessesByName("EXCEL").Count() > 0)
    {
        oExcelApp = (Microsoft.Office.Interop.Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");

        foreach (Microsoft.Office.Interop.Excel.Workbook WB in oExcelApp.Workbooks)
        {
            //MessageBox.Show(WB.FullName);
            if (WB.Name == file_name)
            {
                WB.Save();
                WB.Close();
                //oExcelApp.Quit();
            }
        }
    }
}