Automate multiple Excel instances
.NET library to access to any running instance of Excel.
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 itsApplication
property to get a reference to theApplication
containing it. The catch is that you need to start with a reference to an object inside theApplication
, which is difficult to get without already having theApplication
. - 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.ApplicationExtensionMethods
- This public class has extension methods forXL.Application
.ProcessExtensionMethods
- This public class has extension methods forSystem.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 thatSession
s containApplication
s containWorkbook
s containWorksheet
s.
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 COMException
s 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
//Gets the process ID of the process that owns the window with the given 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
//Gets the XL.Window object with the given 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
//Gets an XL.Application object for the instances with the given main window 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
//Gets the name of the COM class to which the specified window belongs.
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
//Gets the depth of the window with the given handle.
//This will return a negative number for invalid handles or invisible windows.
public static int GetWindowZ(int windowHandle) {
var z = 0;
//Count all windows above the starting window
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
//Brings the main window of the given process to the front of all windows.
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 {
//Gets the depth of the main window of the process.
public static int MainWindowZ(this Process process) {
if (process == null) throw new ArgumentNullException(nameof(process));
return NativeMethods.GetWindowZ(process.MainWindowHandle.ToInt32());
}
//Gets the input sequence, with processes ordered by the depth of their main window.
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) //Filter hidden instances
.OrderBy(x => x.Z) //Sort by z value
.Select(x => x.Process);
}
//Gets the process from the input sequence with the main window having the lowest Z value.
public static Process TopMost(this IEnumerable<Process> processes) {
if (processes == null) throw new ArgumentNullException(nameof(processes));
return OrderByZ(processes).FirstOrDefault();
}
//Converts a Process to an Xl.Application.
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);
}
//Determines if the process is currently visible.
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 {
//Gets the Session containing the given Application.
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);
}
}
//Determines if the given Application is the most recently active one.
public static bool IsActive(this XL.Application app) {
if (app == null) throw new ArgumentNullException(nameof(app));
return Equals(app, app.Session().TopMost);
}
//Activates the given Application.
public static void Activate(this XL.Application app) {
if (app == null) throw new ArgumentNullException(nameof(app));
using (var process = app.AsProcess()) {
NativeMethods.BringToFront(process);
}
}
//Determines if the given Application is visible and accesible to automation.
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.")) {
//This means the application is in a state that does not permit COM automation.
//Often, this is due to a dialog window or right-click context menu being open.
return false;
}
}
//Converts an Application to a Process.
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 Session
s contain Application
s contain Workbook
s contain Worksheet
s.
public class Session {
//Gets an instance representing the current Windows session.
public static Session Current => new Session(Process.GetCurrentProcess().SessionId);
//Creates a new instance for the given session.
public Session(int sessionId) {
SessionId = sessionId;
}
public int SessionId { get; }
//Gets the sequence of currently running processes in this session named "Excel".
private IEnumerable<Process> Processes =>
Process.GetProcessesByName("EXCEL")
.Where(p => p.SessionId == this.SessionId);
//Gets the Application corresponding to a Process, or null if that fails.
private static XL.Application TryGetApp(Process process) {
try {
return process.AsExcelApp();
}
catch {
return null;
}
}
//Gets the IDs for current Excel processes.
public IEnumerable<int> ProcessIds =>
Processes
.Select(p => p.Id)
.ToArray();
//Gets the IDs for current Excel processes that are reachable by automation.
public IEnumerable<int> ReachableProcessIds =>
AppsImpl.Select(a => a.AsProcess().Id).ToArray();
//Gets the IDs for the current Excel processes that are not reachable by automation.
public IEnumerable<int> UnreachableProcessIds =>
ProcessIds
.Except(ReachableProcessIds)
.ToArray();
//Gets an untyped sequence of running Applications.
public IEnumerable Apps => AppsImpl;
//Gets a strongly-typed sequence of running Applications.
private IEnumerable<XL.Application> AppsImpl =>
Processes
.Select(TryGetApp)
.Where(a => a != null && a.AsProcess().IsVisible())
.ToArray();
//Gets the Application with the topmost window, or null if there are none.
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;
}
}
}
}
//Gets the default Application that double-clicked files will open in,
//or null if there are none.
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 aMainForm
instance.AppSerializer
- This class has methods for serializing XL objects in a JSON-like format, whichMainForm
uses for displaying the state of the current session.ProcessLauncher
- This class has methods for launching and disposing of processes, which are called byMainForm
'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;
//Start a background task to update the "Session" textbox.
var task = new Task((Action)UpdateDisplayLoop);
task.Start();
}
#region Fields
//Used to launch and cleanup processes
private readonly ProcessLauncher launcher;
//Used to get access to currently running Excel instances.
private readonly Session session;
//Determines whether the UI data will refresh periodically or not.
//This must be set to false to allow the user to make drop-down selections.
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
//Updates the display, then waits. Repeats forever.
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
- My original article (CodeProject)
- Anonymous source article
- ExcelBrowser (GitHub)
- Windows Functions (Windows Dev Center)
- Type Equivalence and Embedded Interop Types (MSDN)
- Consuming Unmanaged DLL Functions (MSDN)
History
- Posted 11/23/2016
- Previous article posted 2/23/16