Introduction
This article shows how to create automation for Microsoft Excel that can work with multiple Excel instances, or a single Excel instance when other instances may be running.
Why?
The standard Excel API exposed though the COM interop library (Microsoft.Office.Interop.Excel) does not have any support for multiple Excel instances. (I believe the situation is similar for Visual Studio Tools for Office, which is largely a wrapper for the COM interop library, but I have not used VSTO very much.) In that object model, the Application is the top-level object, which does not belong to any kind of collection. Application instances can only be acquired easily in a few restrictive ways:
- If you have a reference to any object in the
Microsoft.Office.Interop.Excel namespace, you can use its Application property to get a reference to the Application containing it. The catch is that you need to start with a reference to an object inside the Application, which is difficult to get without already having the Application. - You can use
Application's constructor to launches a new Excel instance. However, this cannot be used to access existing instances, and the new instance will be in automation mode, which prevents add-ins and other features from loading and is not always desirable. - Add-ins can access the
Application instance they are loaded in, but this does not work for standalone applications. - The
System.Runtime.InteropServices.Marshal class can be used to get the "active" instance, but this does not give any control as to which instance is acquired.
What?
The focus of this article is on a class library called ExcelExtensions which provides classes to extend the Excel object model to incorporate multiple Excel instances. There is also a demo application called ExcelAppManager to show an example use case and how to consume ExcelExtensions from other code. At the top of the page there are download links for just the compliled ExcelExtensions assembly, and for the full ExcelAppManager solution. Both the library and demo app are far from perfect, so please notify me of any bugs you encounter, and do not use this library in a production enviroment without thorough testing.
This is a second iteration on an article I wrote on this topic in April 2016, A lot of credit goes to this anonymous article, as well as some hints from various users of StackOverflow, and the CodeProject members that left comments on my previous article. I'll also include some relevant links throughout the article, and will repost links at the end in the Links section.
The rest of this article is broken down into the following major sections:
- Requirements
- Conventions
- ExcelExtensions Library
- Demo Walkthrough
- Demo Implementation
- Closing Remarks
- Links
- History
Requirements
The source code is written in C#6, so it will require some familiarity with those syntax features, and Visual Studio 2015 will be required to open the solution. The assembly targets .NET 4.0, so it can be consumed by older applications. Some of the implementation uses calls to the Win32 API, but understanding that API is not necessary for consumers. A general understanding of Windows processes and window handles will be helpful. There isn't any complex use of the Excel automation, but you should know what the Microsoft.Office.Interop.Excel.Application class is.
I am not an expert on the Win32 API myself, but learned a good bit about it from this project. Please let me know if the code violates any best practices for dealing with Win32.
NOTE, 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
Conventions
NOTE, paragraphs starting with "NOTE," are usually important or unexpected details, like the environmental conditions mentioned above.
To keep the code examples brief, I have removed many of the comments which can be found in the solution in the ZIP file. The solution in the ZIP file has pretty good XML comment coverage.
I've omitted using directives for the most part, but the you can assume the following are always in effect:
using System;
using System.Collections;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using XL = Microsoft.Office.Interop.Excel;
I've also omitted namespace declarations, but assume all the classes in the code samples are in the namespace ExcelExtensions.
NOTE, the namespace alias XL. I will also refer to types in this namespace as "XL types" and instances of these types as "XL objects".
ExcelExtensions Library
The library implementation consist of four classes:
NativeMethods - This internal class encapsulates calls to the unmanaged Windows API. Generally, you don't need to know how everything in this class works, but a basic understanding will explain some of this library's quirks.ApplicationExtensionMethods - This public class has extension methods for XL.Application.ProcessExtensionMethods - This public class has extension methods for System.Diagnostics.Process.Session - This public class represents a collection of all Excel instances running in a specific Windows session. This may be the only class required by consuming code. It extends the hierarchy of XL types up a level, so that Sessions contain Applications contain Workbooks contain Worksheets.
NativeMethods
This class encapsulates calls to the unmanaged Windows API. It has five public methods, each handling a separate responsibility of the class and using separate Windows API calls.
NOTE, the implementation of this class is not bullet-proof; passing invalid parameters can result in COMExceptions or unexpected null values being returned.
Here is a high-level view of the class, with regions for each responsibility collapsed.
internal static class NativeMethods {
private const string USER32 = "User32.dll";
private const string OLEACC = "Oleacc.dll";
#region Process ID from handle
#region Excel window from handle
#region Excel App from handle
#region Window Z
#region Bring Process to front
}
Here are the each of the regions:
#region Process ID from handle
public static int ProcessIdFromWindowHandle(int windowHandle) {
if (windowHandle == 0) throw new ArgumentOutOfRangeException(
"Window handle cannot be 0.", nameof(windowHandle));
int processId;
GetWindowThreadProcessId(windowHandle, out processId);
return processId;
}
[DllImport(USER32)]
private static extern int GetWindowThreadProcessId(int hWnd, out int lpdwProcessId);
#endregion
#region Excel window from handle
public static XL.Window ExcelWindowFromHandle(int handle) {
XL.Window result;
AccessibleObjectFromWindow(handle, windowObjectId, windowInterfaceId, out result);
return result;
}
[DllImport(OLEACC)]
private static extern int AccessibleObjectFromWindow(int hwnd, uint dwObjectID,
byte[] riid, out XL.Window ppvObject);
private const uint windowObjectId = 0xFFFFFFF0;
private static byte[] windowInterfaceId = \
new Guid("{00020400-0000-0000-C000-000000000046}").ToByteArray();
#endregion
#region Excel App from handle
public static XL.Application AppFromMainWindowHandle(int mainWindowHandle) {
if (mainWindowHandle == 0) throw new ArgumentOutOfRangeException("Window handle cannot be 0.", nameof(mainWindowHandle));
int childHandle = 0;
EnumChildWindows(mainWindowHandle, NextChildWindowHandle, ref childHandle);
var win = ExcelWindowFromHandle(childHandle);
return win?.Application;
}
[DllImport(USER32)]
private static extern bool EnumChildWindows(int hWndParent, EnumChildCallback lpEnumFunc,
ref int lParam);
private delegate bool EnumChildCallback(int hwnd, ref int lParam);
private static bool NextChildWindowHandle(int currentChildHandle, ref int nextChildHandle) {
const string excelClassName = "EXCEL7";
var result = true;
var className = GetClassName(currentChildHandle);
if (className == excelClassName) {
nextChildHandle = currentChildHandle;
result = false;
}
return result;
}
#region Get class name
private static string GetClassName(int windowHandle) {
var buffer = new StringBuilder(128);
GetClassName(windowHandle, buffer, 128);
return buffer.ToString();
}
[DllImport(USER32, CharSet = CharSet.Unicode)]
private static extern int GetClassName(int hWnd, StringBuilder lpClassName, int nMaxCount);
#endregion
#endregion
#region Window Z
public static int GetWindowZ(int windowHandle) {
var z = 0;
for (var h = new IntPtr(windowHandle);
h != IntPtr.Zero;
h = GetWindow(h, GW_HWNDPREV)) {
z++;
}
return z;
}
[DllImport(USER32)]
private static extern IntPtr GetWindow(IntPtr hWnd, uint uCmd);
private const int GW_HWNDPREV = 3;
#endregion
#region Bring Process to front
public static bool BringToFront(Process process) {
if (process == null) throw new ArgumentNullException(nameof(process));
var handle = process.MainWindowHandle;
if (handle == IntPtr.Zero) return false;
try {
SetForegroundWindow(handle);
return true;
}
catch { return false; }
}
[DllImport(USER32)]
private static extern bool SetForegroundWindow(IntPtr hWnd);
#endregion
ProcessExtensionMethods
This is a pretty simple static class with extension methods for System.Diagnostics.Process, and some for IEnumerable<Process> as well. It relies heavily on NativeMethods to do its dirty work.
NOTE, the AsExcelApp method can throw a COMException or return null if an invalid process is used, or the Excel instance is currently busy, such as when a dialog window is opened.
public static class ProcessExtensionMethods {
public static int MainWindowZ(this Process process) {
if (process == null) throw new ArgumentNullException(nameof(process));
return NativeMethods.GetWindowZ(process.MainWindowHandle.ToInt32());
}
public static IEnumerable<Process> OrderByZ(this IEnumerable<Process> processes) {
if (processes == null) throw new ArgumentNullException(nameof(processes));
return processes
.Select(p => new {
Process = p,
Z = MainWindowZ(p)
})
.Where(x => x.Z > 0)
.OrderBy(x => x.Z)
.Select(x => x.Process);
}
public static Process TopMost(this IEnumerable<Process> processes) {
if (processes == null) throw new ArgumentNullException(nameof(processes));
return OrderByZ(processes).FirstOrDefault();
}
public static XL.Application AsExcelApp(this Process process) {
if (process == null) throw new ArgumentNullException(nameof(process));
var handle = process.MainWindowHandle.ToInt32();
return NativeMethods.AppFromMainWindowHandle(handle);
}
public static bool IsVisible(this Process process) {
if (process == null) throw new ArgumentNullException(nameof(process));
return process.MainWindowHandle.ToInt32() != 0;
}
}
ApplicationExtensionMethods
This is another simple extension method class, extending XL.Application.
public static class ApplicationExtensionMethods {
public static Session Session(this XL.Application app) {
if (app == null) throw new ArgumentNullException(nameof(app));
using (var process = app.AsProcess()) {
return new Session(process.SessionId);
}
}
public static bool IsActive(this XL.Application app) {
if (app == null) throw new ArgumentNullException(nameof(app));
return Equals(app, app.Session().TopMost);
}
public static void Activate(this XL.Application app) {
if (app == null) throw new ArgumentNullException(nameof(app));
using (var process = app.AsProcess()) {
NativeMethods.BringToFront(process);
}
}
public static bool IsVisible(this XL.Application app) {
if (app == null) throw new ArgumentNullException(nameof(app));
try {
using (var process = app.AsProcess()) {
return app.Visible && process.IsVisible();
}
}
catch (COMException x)
when (x.Message.StartsWith("The message filter indicated that the application is busy.")
|| x.Message.StartsWith("Call was rejected by callee.")) {
return false;
}
}
public static Process AsProcess(this XL.Application app) {
if (app == null) throw new ArgumentNullException(nameof(app));
var mainWindowHandle = app.Hwnd;
var processId = NativeMethods.ProcessIdFromWindowHandle(mainWindowHandle);
return Process.GetProcessById(processId);
}
}
Session
This is an instance class that represents the set of all running Excel instances in a specific Windows session. It extends the XL type hierarchy up one level, so that Sessions contain Applications contain Workbooks contain Worksheets.
public class Session {
public static Session Current => new Session(Process.GetCurrentProcess().SessionId);
public Session(int sessionId) {
SessionId = sessionId;
}
public int SessionId { get; }
private IEnumerable<Process> Processes =>
Process.GetProcessesByName("EXCEL")
.Where(p => p.SessionId == this.SessionId);
private static XL.Application TryGetApp(Process process) {
try {
return process.AsExcelApp();
}
catch {
return null;
}
}
public IEnumerable<int> ProcessIds =>
Processes
.Select(p => p.Id)
.ToArray();
public IEnumerable<int> ReachableProcessIds =>
AppsImpl.Select(a => a.AsProcess().Id).ToArray();
public IEnumerable<int> UnreachableProcessIds =>
ProcessIds
.Except(ReachableProcessIds)
.ToArray();
public IEnumerable Apps => AppsImpl;
private IEnumerable<XL.Application> AppsImpl =>
Processes
.Select(TryGetApp)
.Where(a => a != null && a.AsProcess().IsVisible())
.ToArray();
public XL.Application TopMost {
get {
var dict = AppsImpl.ToDictionary(
keySelector: a => a.AsProcess(),
elementSelector: a => a);
var topProcess = dict.Keys.TopMost();
if (topProcess == null) {
return null;
}
else {
try {
return dict[topProcess];
}
catch {
return null;
}
}
}
}
public XL.Application PrimaryInstance {
get {
try {
return (XL.Application)Marshal.GetActiveObject("Excel.Application");
}
catch (COMException x)
when (x.Message.StartsWith("Operation unavailable")) {
Debug.WriteLine("Session: Primary Excel instance unavailable.");
return null;
}
}
}
}
NOTE, the public Apps property returns an IEnumerable, not an IEnumerable<XL.Application>. This is because XL.Application (like all XL types) is an embedded COM interop type, and as such can not be used as a generic type parameter for a public member. It is however, completely safe to cast elements of this collection to IEnumerable<XL.Application> in consuming code, like so:
XL.Application[] myApps = mySession.Apps.OfType<XL.Application>().ToArray();
For more on embedding interop types, see this MSDN article.
NOTE, the distinction between TopMost and PrimaryInstance. TopMost is the instance with the foremost window, which is likely the last one a user clicked on; this may change frequently if dealing with multiple instances. PrimaryInstance is the default instance that double-clicked files will open in; this generally does not change until the existing PrimaryInstance is closed.
Demo Walkthrough
ExcelAppManager is a WinForms application that allows you to monitor Excel instances and also update the value in cell A1 for any worksheet in any Excel instance. The top-left of the form contains a few buttons for launching or cleaning up processes, the bottom left has controls for updating cell A1, and the right side displays a JSON-like tree of currently running Excel instances. First we'll go over using the display on the right side, then we'll update cell values.
Monitoring Excel Instances
Here is what the application looks like when no Excel instances are running.
Click "Launch Excel instance" to launch an Excel instance, and watch the right side of the form update accordingly.
A new Excel 2016 instance:
Create a new workbook, and the form will update accordingly:
Opening multiple workbooks will add their details to the form. Note that multiple workbooks open in the same Excel instance normally:
Click "Launch Excel instance" again to launch another process:
NOTE, if a dialog window, context menu, or mouseover tooltip is open in Excel, the application is not accessible to automation and will temporarily dissappear from the display on the right of the form.
Updating Cell Values
To update cell values, first check the "Pause display updates" checkbox. This prevents the selected values in the comboboxes from constantly resetting themselves, but also freezes the JSON display on the right of the form. If you open or close a new Excel instance, workbook, or worksheet while updates are paused, you will have to unpause updates to refresh the items in the comboboxes.
The "ProcessID" combobox will allow you to select a running Excel instance by processID. This combobox will always have items.
The "Book Name" combobox will allow you to select a workbook by name, from the selected Excel instance above. 
The "Sheet Name" combobox will allow you to select a worksheet by name, from the selected book above. When a sheet is selected, the value of its A1 cell will be displayed in the readonly textbox below.
Clicking "Change cell value" will update the value of the selected sheet's A1 cell to be the value of the "New A1 Value" textbox.
Demo Implementation
I won't post all of the code for the application here, but I'll describe its basic structure, with some code samples for the most interesting parts. The application has four classes:
MainForm - This is the UI and contains the core of the application logic. This is the only class I will examine further here.Program - This is the standard WinForms application entry point; it just creates a MainForm instance.AppSerializer - This class has methods for serializing XL objects in a JSON-like format, which MainForm uses for displaying the state of the current session.ProcessLauncher - This class has methods for launching and disposing of processes, which are called by MainForm's button-click events, so that multiple Excel instances can be easily observed.
MainForm
This class is the UI and contains some of the core application logic. Since there is a lot going on in this class, I've grouped the source code into regions. This first code sample shows the class constructor and its fields, but has three regions at the end collapsed.
using System.Drawing;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;
public partial class MainForm : Form {
public MainForm() {
InitializeComponent();
launcher = new ProcessLauncher();
session = Session.Current;
var task = new Task((Action)UpdateDisplayLoop);
task.Start();
}
#region Fields
private readonly ProcessLauncher launcher;
private readonly Session session;
private bool IsPaused {
get { return isPaused; }
set {
isPaused = value;
cmb_ProcessID.Enabled = value;
cmb_BookName.Enabled = value;
cmb_SheetName.Enabled = value;
txt_Session.BackColor = value ? Color.LightGray : Color.White;
}
}
private bool isPaused = false;
#endregion
#region Update loop
#region Control event handlers
#region Update selection options
}
As you can see, the constructor acquires a ProcessLauncher and a Session instance and stores them in its fields. The ProcessLauncher will be used to launch new Excel instances, or clean up any that have been launched by this application. The Session can be queried to get XL.Application objects for currently running Excel instances. The constructor also starts a background Task that continuously updates the UI, which we'll examine shortly. The last field, isPaused, determines whether UI updates are paused (true) or enabled (false); the property IsPaused also updates the display of several controls.
The update loop is pretty simple. In an endless loop, it checks to see if updates are paused, and if not paused, updates the textbox to contain a string representation of the Session, them waits 100ms.
#region Update loop
private void UpdateDisplayLoop() {
while (true) {
if (!isPaused) {
Invoke((MethodInvoker)UpdateDisplay);
}
Thread.Sleep(100);
}
}
private void UpdateDisplay() {
var sessionText = ExcelSerializer.SerializeSession(this.session);
txt_Session.Text = sessionText;
ResetSelections();
}
#endregion
The control event handlers section is pretty straight-forward. The "Pause Updates" checkbox is linked to the IsPaused property. The process management buttons on the top-left of the form use ProcessLauncher. The controls on the bottom-left of the form call functions which are defined in the last region.
NOTE, the following abbreviations are used for control names:
- chk = CheckBox
- btn = Button
- cmb = ComboBox
#region Control event handlers
private void chk_PauseUpdates_CheckedChanged(object sender, EventArgs e) {
IsPaused = chk_PauseUpdates.Checked;
}
private void btn_LaunchTaskMgr_Click(object sender, EventArgs e) =>
this.launcher.LaunchTaskManager();
private void btn_LaunchExcel_Click(object sender, EventArgs e) =>
this.launcher.LaunchExcelInstance();
private void btn_CleanUpExcel_Click(object sender, EventArgs e) =>
this.launcher.Dispose();
private void cmb_ProcessID_SelectedIndexChanged(object sender, EventArgs e) =>
TryUpdateSelectableBookNames();
private void cmb_BookName_SelectedIndexChanged(object sender, EventArgs e) =>
TryUpdateSelectableSheetNames();
private void cmb_SheetName_SelectedIndexChanged(object sender, EventArgs e) =>
TryUpdateOldCellValue();
private void btn_ChangeCell_Click(object sender, EventArgs e) =>
TryChangeCellValue();
#endregion
The last region is the biggest, but none of the methods are too complicated.
ResetSelections clears the contents of the controls on the bottom-left of the form, and is called as part of the update loop.
The TryXxx methods are called by control event handlers, and each attempt to update the contents of a specific control or perform a task, but refresh the display if they cannot. For example, if the user selects "Book1" after Book1 has been closed, the display will refresh since the selection is invalid.
The GetSelectedXxx methods each attempt to return an XL object that corresponds to the selection in a specific control.
The UpdateXxx methods each replace the contents of a specific control to contain data about a given XL object.
#region Update selection options
private void ResetSelections() {
UpdateSelectableProcessIds(this.session);
cmb_BookName.Items.Clear();
cmb_SheetName.Items.Clear();
txt_OldValue.Text = "";
}
private void TryUpdateSelectableBookNames() {
var app = GetSelectedApp();
if (app == null) { UpdateDisplay(); }
else { UpdateSelectableBookNames(app); }
}
private void TryUpdateSelectableSheetNames() {
var book = GetSelectedBook();
if (book == null) { UpdateDisplay(); }
else { UpdateSelectableSheetNames(book); }
}
private void TryUpdateOldCellValue() {
var sheet = GetSelectedSheet();
if (sheet == null) { UpdateDisplay(); }
else { UpdateOldCellValue(sheet); }
}
private void TryChangeCellValue() {
var sheet = GetSelectedSheet();
if (sheet == null) {
UpdateDisplay();
}
else {
xlRange cell = sheet.Cells[1, 1];
var text = txt_NewValue.Text;
cell.Value = text;
}
}
#region Get current selections
private XL.Application GetSelectedApp() {
XL.Application result = null;
var text = cmb_ProcessID.Text;
if (text.Length > 0) {
var processId = int.Parse(text);
result = session.Apps.OfType<XL.Application>()
.FirstOrDefault(a => a.AsProcess().Id == processId);
}
return result;
}
private XL.Workbook GetSelectedBook() {
XL.Workbook result = null;
var app = GetSelectedApp();
if (app != null) {
var text = cmb_BookName.Text;
if (text.Length > 0) {
try {
result = app.Workbooks[text];
}
catch {
result = null;
}
}
}
return result;
}
private XL.Worksheet GetSelectedSheet() {
XL.Worksheet result = null;
var book = GetSelectedBook();
if (book != null) {
var text = cmb_SheetName.Text;
if (text.Length > 0) {
try {
result = book.Sheets[text];
}
catch {
result = null;
}
}
}
return result;
}
#endregion
#region Update displayed data
private void UpdateSelectableProcessIds(Session session) {
cmb_ProcessID.Items.Clear();
foreach (var id in session.ReachableProcessIds) {
cmb_ProcessID.Items.Add(id);
}
}
private void UpdateSelectableBookNames(XL.Application app) {
cmb_BookName.Items.Clear();
foreach (XL.Workbook book in app.Workbooks) {
cmb_BookName.Items.Add(book.Name);
}
}
private void UpdateSelectableSheetNames(XL.Workbook book) {
cmb_SheetName.Items.Clear();
foreach (XL.Worksheet sheet in book.Sheets) {
cmb_SheetName.Items.Add(sheet.Name);
}
}
private void UpdateOldCellValue(XL.Worksheet sheet) {
XL.Range cell = sheet.Cells[1, 1];
var text = cell.Text;
txt_OldValue.Text = text;
}
#endregion
#endregion
Closing Remarks
I hope this article helps. Keep in mind, there are still bugs in this library, and not all Windows and Excel versions have been tested. Please let me know if you find any bugs, or if you have any questions about usage or design choices.
I am also currently working on a WPF application called ExcelBrowser that uses the techniques outlined in this article. It will serve as an "object browser" window for all Excel instances running on the current users Windows session. The ExcelExtensions library seen here was actually derived from part of its codebase, so the implementation is a little different.
Links
History
- Posted 11/23/2016
- Previous article posted 2/23/16
