Click here to Skip to main content
15,041,767 members
Articles / Desktop Programming / Win32
Tip/Trick
Posted 24 Feb 2016

Stats

36.8K views
19 bookmarked

Get a Collection of All Running Excel Instances

Rate me:
Please Sign up or sign in to vote.
5.00/5 (11 votes)
26 Nov 2016CPOL7 min read
Get a collection of all running Excel interop Application objects (not just the active one)

Update

I've created a new article that describes how to do the same things as this article, but with a reusable assembly instead of everything in one class, and there is a demo proejct and walkthrough as well.  Check it out here.


Introduction

If you write a lot of .NET Excel automation, particularly for use on servers where there may be multiple instances of Excel running at a time, you may find yourself needing to access a specific instance of Excel, not just the "active" one.

I have spent a lot of time on forums and Q&A sites trying to find a way to iterate over all running instances, and select specific instances by Hwnd or ProcessID, but have not yet found a satisfactory article. After a while of piecing different answers together, I believe I have a class that can provide this functionality for anyone in a similar situation.

A lot of credit goes to the anonymous article at the link below, as well as some hints from various users of StackOverflow.

http://pastebin.com/F7gkrAST

Background

You will need an intermediate grasp of C# for this project, and some familiarity with Windows processes and window handles. There is also some usage of LINQ and lambda expressions, but only in a few places. You actually don't need to know much about Excel automation, other than knowing what the Microsoft.Office.Interop.Excel.Application class is.

Several parts of the private implementation of the class involve extern calls to the Win32 API, which you don't necessarily need to understand to use this class.  I am not very familiar with the Win32 API myself, but learned a good bit about it in putting this class together. 

Please let me know if the code violates any best practices for dealing with Win32.

IMPORTANT: This code has not been tested on all versions of Excel or Windows.  (Please help me test them all out.) I believe this code may be particularly prone to issues based on different Excel and Windows versions.

Tested environments:

  • Windows 7 64-bit with Excel 2016 32-bit
  • Windows 7 64-bit with parallel instances of Excel 2010 32-bit and Excel 2013 32-bit.

Using the Code

The class below can be used alongside the Microsoft primary interop assembly for Excel, to get a Microsoft.Office.Interop.Excel.Application object for any running instance of Excel.

I've split the class into two partial class files, to breakup what would otherwise be a 200-line file. The first part is the public interface, and the second is the private implementation.

Public Interface

The publicly visible interface is pretty simple, and has the following members:

  • Constructor - This takes a nullable Int32 as a parameter, which defaults to null.  The value is used to filter Excel instances by Windows sessionID.  If null, the class's SessionID property will be set to the current sessionID.
  • SessionID - This property is used to filter Excel instances by Windows sessionID. This is very important when working with servers where multiple users may be using Excel at once.
    • If -1, the collection will give access to instances from all sessions.
    • If a valid sessionID, the collection will give access to all Excel instances running in that session.
    • If not a valid sessionID, the collection will always be empty. No exception is thrown.
  • Accessors
    • FromProcess - This method takes a reference to a Process and returns the Excel instance of that Process, or null if the Process is not an Excel instance.
    • FromProcessID - This method takes a processID and returns the Excel instance of the corresponding Process, or null if the ID is invalid or does not correspond to an Excel instance.
    • FromMainWindowHandle - This method takes the Hwnd value of the main window of an Excel instance, and returns the corresponding Excel instance, or null if the Hwnd is invalid or does not correspond to an Excel instance.
    • PrimaryInstance - This property returns the first-created Excel instance, or null if there are none.  If a user double-clicks an Excel file icon, this will be the instance the file opens in.
    • TopMostInstance - This property returns the Excel instance with the top-most visible window, or null if there are none. This will normally be the last instance selected by a user.
  • Methods
    • GetEnumerator - This method returns a collection of all Excel instances, filtering by SessionID (if SessionID is not -1).
    • GetProcesses - This method returns a collection of all Process objects of Excel instances, filtering by SessionID (if SessionID is not -1).
C#
using System;
using System.Collections;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Runtime.InteropServices;

//Don't add the entire interop namespace, it will introduce some naming conflicts.
using xlApp = Microsoft.Office.Interop.Excel.Application;

namespace ExcelExtensions {

    /// <summary>
    /// Collection of currently running Excel instances.
    /// </summary>
    public partial class ExcelAppCollection : IEnumerable<xlApp>  {

        #region Constructors

        /// <summary>Initializes a new instance of the 
        /// <see cref="ExcelAppCollection"/> class.</summary>
        /// <param name="sessionID">Windows sessionID to filter instances by.
        /// If not assigned, uses current session.</param>
        public ExcelAppCollection (Int32? sessionID = null) {
            if (sessionID.HasValue && sessionID.Value < -1)
                throw new ArgumentOutOfRangeException("sessionID");

            this.SessionID = sessionID
                ?? Process.GetCurrentProcess().SessionId;
        }

        #endregion

        #region Properties

        /// <summary>Gets the Windows sessionID used to filter instances.
        /// If -1, uses instances from all sessions.</summary>
        /// <value>The sessionID.</value>
        public Int32 SessionID { get; private set; }

        #endregion

        #region Accessors

        /// <summary>Gets the Application associated with a given process.</summary>
        /// <param name="process">The process.</param>
        /// <returns>Application associated with process.</returns>
        /// <exception cref="System.ArgumentNullException">process</exception>
        public xlApp FromProcess(Process process) {
            if (process == null)
                throw new ArgumentNullException("process");
            return InnerFromProcess(process);
        }

        /// <summary>Gets the Application associated with a given processID.</summary>
        /// <param name="processID">The process identifier.</param>
        /// <returns>Application associated with processID.</returns>
        public xlApp FromProcessID(Int32 processID) {
            try {
                return FromProcess(Process.GetProcessById(processID));
            }
            catch (ArgumentException) {
                return null;
            }
        }

        /// <summary>Get the Application associated with a given window handle.</summary>
        /// <param name="mainHandle">The window handle.</param>
        /// <returns>Application associated with window handle.</returns>
        public xlApp FromMainWindowHandle(Int32 mainHandle) {
            return InnerFromHandle(ChildHandleFromMainHandle(mainHandle));
        }

        /// <summary>Gets the main instance. </summary>
        /// <remarks>This is the oldest running instance.
        /// It will be used if an Excel file is double-clicked in Explorer, etc.</remarks>
        public xlApp PrimaryInstance {
            get {
                try {
                    return Marshal.GetActiveObject(MarshalName) as xlApp;
                }
                catch (COMException) {
                    return null;
                }
            }
        }

        /// <summary>Gets the top most instance.</summary>
        /// <value>The top most instance.</value>
        public xlApp TopMostInstance {
            get {
                var topMost = GetProcesses() //All Excel processes
                    .Select(p => p.MainWindowHandle) //All Excel main window handles
                    .Select(h => new { h = h, z = GetWindowZ(h) }) //Get (handle, z) pair per instance
                    .Where(x => x.z > 0) //Filter hidden instances
                    .OrderBy(x => x.z) //Sort by z value
                    .First(); //Lowest z value

                return FromMainWindowHandle(topMost.h.ToInt32());
            }
        }

        #endregion

        #region Methods

        /// <summary>Returns an enumerator that iterates through the collection.</summary>
        /// <returns>
        /// A <see cref="T:System.Collections.Generic.IEnumerator`1" /> 
        /// that can be used to iterate through the collection.
        /// </returns>
        public IEnumerator<xlApp> GetEnumerator() {
            foreach (var p in GetProcesses())
                yield return FromProcess(p);
        }
        IEnumerator IEnumerable.GetEnumerator() { return GetEnumerator(); }

        /// <summary>Gets all Excel processes in the current session.</summary>
        /// <returns>Collection of all Excel processing in the current session.</returns>
        public IEnumerable<Process> GetProcesses() {

            IEnumerable<Process> result = Process.GetProcessesByName(ProcessName);

            if (this.SessionID >= 0)
                result = result.Where(p => p.SessionId == SessionID);

            return result;
        }

        #endregion
   }
}

Private Implementation

As mentioned in the introduction, I am not an expert on the Win32 API.  Parts of the private implementation are still a bit mysterious to me and may violate best practices for using it.  That being said, it has been reliable as far as I've used it.

  • Methods
    • InnerFromProcess - This method takes a reference to a Process and returns the corresponding Microsoft.Office.Interop.Excel.Application object.
    • ChildHandleFromMainHandle - This method takes the Hwnd of a Process or Application object and returns a child window's Hwnd.
    • InnerFromHandle - This method takes the Hwnd of a child window of an Application object and returns the Application.
    • GetWindowZ - This method takes the Hwnd of a window and returns its z value.
    • EnumChildFunc - This method is used by the EnumChildWindows method to get child window Hwnds.
  • External Methods
    • AccessibleObjectFromWindow - This method takes the Hwnd of an Excel window, as well as some of the constants below, and returns (through its ref parameter) a reference to a Window object, which can then be used to get its parent Application object. 
      • It does not work if you pass it the value of a Application's Hwnd property; it must be a specific workbook's window's Hwnd.  This may only be the case on Excel 2013 or newer, where there is no main Excel window.
    • EnumChildWindows - This method takes the Hwnd of the main window of an Excel instance and an EnumChildCallback delegate as parameters, and returns (through its ref parameter) the Hwnd of a child window, which can be used by AccessibleObjectFromWindow.
    • GetClassName - This method is used by the EnumChildCallback delegate that is passed to EnumChildWindows.  I believe it gets the details of the Window class internally so that an Hwnd can be returned.
    • GetWindow - This method takes an Hwnd and a constant as parameters.  The constant used determines how to get other Hwnds based on the provided Hwnd.  Using GW_HWNDPREV returns the Hwnd of the window directly above (z position) the given Hwnd.  This is used to get the TopMostInstance.
  • Constants and Delegates
    • MarshalName - This constant is required to get the "active" instance (PrimaryInstance) from the System.Runtime.InteropServices.Marshal class.
    • ProcessName - This constant is required to get Excel processes by name from System.Diagnostics.Process.
    • ComClassName - This constant is required for EnumChildFunc method, which is used by the EnumChildWindow method from the Win32 API.
    • DW_OBJECTID - This constant is required for the AccessibleObjectFromWindow method from the Win32 API.
    • GW_HWNDPREV - This constant is required for getting window z (depth) values from the GetWindow method from the Win32 API.  I copied a bit of the Microsoft documentation into the code comments.
    • rrid - This pseudo-constant is required for the AccesibleObjectFromWindow from the Win32 API.
    • EnumChildCallback - This delegate is implemented by the EnumChildFunc method and is requied for the EnumChildWindow method from the Win32 API.
C#
using System;
using System.Diagnostics;
using System.Runtime.InteropServices;
using System.Text;

//Don't import the entire namespace, this will cause name conflicts.
using xlApp = Microsoft.Office.Interop.Excel.Application;
using xlWin = Microsoft.Office.Interop.Excel.Window;

namespace ExcelExtensions {

   public partial class ExcelAppCollection {

        #region Methods

        private static xlApp InnerFromProcess(Process p) {
            return InnerFromHandle(ChildHandleFromMainHandle(p.MainWindowHandle.ToInt32()));
        }

        private static Int32 ChildHandleFromMainHandle(Int32 mainHandle) {
            Int32 handle = 0;
            EnumChildWindows(mainHandle, EnumChildFunc, ref handle);
            return handle;
        }

        private static xlApp InnerFromHandle(Int32 handle) {
            xlWin win = null;
            Int32 hr = AccessibleObjectFromWindow(handle, DW_OBJECTID, rrid.ToByteArray(), ref win);
            return win.Application;
        }

        private static Int32 GetWindowZ(IntPtr handle) {
            var z = 0;
            for (IntPtr h = handle; h != IntPtr.Zero; h = GetWindow(h, GW_HWNDPREV))
                z++;
            return z;
        }
       
        private static Boolean EnumChildFunc(Int32 hwndChild, ref Int32 lParam) { 
            var buf = new StringBuilder(128); 
            GetClassName(hwndChild, buf, 128); 
            if (buf.ToString() == ComClassName) { 
                lParam = hwndChild; 
                return false; 
            } 
            return true; 
        }
        
        #endregion

        #region Extern Methods

        [DllImport("Oleacc.dll")] 
        private static extern Int32 AccessibleObjectFromWindow(
            Int32 hwnd, UInt32 dwObjectID, Byte[] riid, ref xlWin ptr); 

        [DllImport("User32.dll")] 
        private static extern Boolean EnumChildWindows(
            Int32 hWndParent, EnumChildCallback lpEnumFunc, ref Int32 lParam); 

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

        [DllImport("User32.dll")] 
        private static extern IntPtr GetWindow(IntPtr hWnd, UInt32 uCmd);

        #endregion

        #region Constants & delegates

        private const String MarshalName = "Excel.Application";

        private const String ProcessName = "EXCEL";

        private const String ComClassName = "EXCEL7";

        private const UInt32 DW_OBJECTID = 0xFFFFFFF0;

        private const UInt32 GW_HWNDPREV = 3;
        //3 = GW_HWNDPREV
        //The retrieved handle identifies the window above the specified window in the Z order.
        //If the specified window is a topmost window, the handle identifies a topmost window.
        //If the specified window is a top-level window, the handle identifies a top-level window.
        //If the specified window is a child window, the handle identifies a sibling window.

        private static Guid rrid = new Guid("{00020400-0000-0000-C000-000000000046}");

        private delegate Boolean EnumChildCallback(Int32 hwnd, ref Int32 lParam);
        #endregion
    }
} 

Points of Interest

Please let me know if you find this class helpful (or terrible).  I'm especially interested in issues with older version of Excel (pre-2013), multiple versions of Excel on one machine, or multiple users on one server.  If you have any further insight into how the Win32 API is working behind the scenes, I would also like to know more about that.  Any feedback is highly appreciated.

Further Developments

I have recently started working on a WPF application called ExcelBrowser that allows users to easily browse through multiple Excel instances, their workbooks, and sheets.  Part of the implementation of this application is directly decended from the class described in this article.  Check it out at github.com/JamesFaix/ExcelBrowser.  Also, note that the solution uses C#6/.NET 4.6.1.  As of writing this, I also need to catch up on some code comments, so bear with me.

The parts related to this article are in the ExcelBrowser.Interop project of the solution.  All extern methods are encapsulated in the NativeMethods class, the Session class represents a collection of all running Applications and all running Processes with the name "Excel". AppFactory provides methods for getting specific Application instances.  Some other parts of this class are also in the ApplicationExtensionMethods and ProcessExtensionMethods classes.

History

  • Added "Further Developments" section 11/20/16
  • Posted 2/23/2016

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

JamesFaix
Software Developer
United States United States
I am a professional developer, but I really create software because it's fun. I've always been interested in deconstructing complex systems, and software engineering has proven to be an inexhaustable supersystem of such systems. In the past I've also spent time focusing on music composition, audio engineering, electronics, game design, history, and philosophy.

My strongest languages are English and C#, in fact I'm a Microsoft Certified Professional for "Programming in C#". I do not have any certifications for English, so please trust me there. I've spent a lot of time working on Windows desktop applications, particularly for interacting with SQL Server or automating Microsoft Office programs, using technologies such as C#, VB.NET, VBA, T-SQL, WinForms, WPF, ADO.NET, the MS Office PIA's, ExcelDNA, EPPlus, and Crystal Reports. I've also done some web development using JavaScript, HTML, CSS, TypeScript, ASP.NET, WCF, jQuery, and requirejs. I am very interested in functional programming (F#, Haskell, Clojure), and try to use C# and JavaScript in a "functional" way at times, but I haven't had the opportunity to use a functional language for a serious project yet.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Farhad Reza2-Dec-16 6:14
MemberFarhad Reza2-Dec-16 6:14 
QuestionAny Example Usage Pin
jrdnoland19-Nov-16 13:24
Memberjrdnoland19-Nov-16 13:24 
AnswerRe: Any Example Usage Pin
JamesFaix20-Nov-16 4:17
MemberJamesFaix20-Nov-16 4:17 
GeneralRe: Any Example Usage Pin
jrdnoland21-Nov-16 8:50
Memberjrdnoland21-Nov-16 8:50 
GeneralRe: Any Example Usage Pin
JamesFaix21-Nov-16 9:30
MemberJamesFaix21-Nov-16 9:30 
GeneralRe: Any Example Usage Pin
jrdnoland21-Nov-16 10:57
Memberjrdnoland21-Nov-16 10:57 
GeneralRe: Any Example Usage Pin
JamesFaix26-Nov-16 6:24
MemberJamesFaix26-Nov-16 6:24 
QuestionComClassName: should I change the value? Pin
Jing Wu14-Mar-16 5:05
MemberJing Wu14-Mar-16 5:05 
AnswerRe: ComClassName: should I change the value? Pin
JamesFaix15-Mar-16 0:40
MemberJamesFaix15-Mar-16 0:40 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.