Extending Excel with .NET
4.50/5 (5 votes)
This article demonstrates how to automate Excel using VS2008 rather than VSTO or VBA
Introduction
Using 'Excel' as search keyword results in almost 700 CodeProject articles. Admittedly quite a few of these articles address Excel at best tangentially, but none of the ones actually focussing on Excel 'seem' to have addressed a very simple question: how to create menu items in the Excel application that allow the user to interact with code written in .NET without resorting to VSTO or the development of add-ins.
This article is intended to fill this void. I intend to provide an easy way to create and interact with custom Excel menu items without having to resort to Microsoft's Visual Studio Tools for Office (VSTO) or Visual Basic for Applications (VBA). This approach takes advantage of the .NET platform and the VB and C# object models for MS Excel which have been illustrated in a number of previous CodeProject articles. In the interest of space I am referencing only two that I found particular helpful. Modifying MS Excel's menu is rather straightforward due to the relative simplicity of its commandbar structure. It is considerably more involved for Outlook for which I have published a separate article. The code presented here is applicable to both Excel2003 and Excel2007, however the location of the added menu items slightly differs between these two versions: in Excel2003 the menu items will appear in the main Menu Bar while they will show in the Add-In section of the Menu Bar in Excel2007.
Background
Automating MS Excel is a very rich topic offering ample opportunity to improve on the built-in functionality. There are three ways to approach this task:
- Developing add-ins using VSTO
- Writing VBA code
- Developing automation executeables using Visual Studio
The relative merits of these three strategies are nicely discussed by E.Carter & E.Lippert in "Visual Studio Tools for Office 2007: VSTO for Excel, Word and Outlook," Addison-Wesley, March 2009, however with emphasis on VSTO add-in development. The development of add-ins seems notoriously difficult: debugging is non-trivial and proper registration on the target machine tends to be fickle. Using VBA is rather limiting as it does not currently offer full access to the .NET platform. Developing automation executeables with VS2008 seems to be the best of all worlds: it is a development environment we are comfortable with and provides access to the full range of debugging techniques offered by the VS2008 Integrated Development Environment. In addition, and most importantly for the purists amongst us, the resulting code could be relatively easily implemented in an add-in once it is fully debugged.
Using the Code
The code I am presenting here is intended to provide you with the framework needed to develop your own automation products. You need to add the following references to your VS2008 project:
- Microsoft Office 11.0 (or 12.0) Object Library
- Microsoft Excel 11.0 (or 12.0) Object Library
The code is organized into three separate classes. The ExcelMenuExtensions class (file name: ExcelSidekickCSharp.cs) accomplishes the following essential steps:
- It creates a new instance of Excel.
- It adds the user-specified number of single menu buttons and a pull-down menu item with the user-specified number of menu items to the "Worksheet Menu Bar" commandbar of the Excel instance.
The Program class (file name: Program.cs) contains a simple example demonstrating how to implement the ExcelMenuExtensions class. It creates an instance of the ExcelMenuExtensions class and implements a simple set of menu items and a pull-down menu along and wires the click events for the custom menu items to simple event handlers. When using this framework to work with Excel the developers would simply have to include their custom code in these event handlers.
Since I have included the code I decided not to reproduce the code listing in this article, but to rather focus on demonstrating the relative simplicity of implementing the ExcelMenuExtensions class. Here is the core of the implementation (see Program.cs for the complete code; I will explain the reason for the reference to the API method SetForegroundWindow and the purpose of the commented lines referencing LicenseGenie later on):
class Program
{
public class InteractiveExcel
{
[DllImport("user32.dll")]
private static extern bool SetForegroundWindow(int hWnd);
ExcelMenuExtensions xL;
private Excel.Application c_ExcelApp;
private const string CAPTION1 = "New Button 1";
private const string CAPTION2 = "New Button 2";
private const string CAPTION3 = "New Popup";
private const string CAPTION4 = "Item 1";
private const string CAPTION5 = "Item 2";
private const string CAPTION6 = "Item 3";
private object c_Missing = System.Type.Missing;
static void Main()
{
InteractiveExcel t = new InteractiveExcel();
}
public InteractiveExcel()
{
//LicenseGenie myLicenseGenie = new LicenseGenie();
//if (!myLicenseGenie.LicenseIsValid()) return;
int nMenuItems = 2;
int nPullDownMenuItems = 3;
xL = new ExcelMenuExtensions(nMenuItems, nPullDownMenuItems);
xL.c_CustomMenuItem_Captions = new string[2] { CAPTION1, CAPTION2 };
xL.c_CustomMenuItem_Tags = new string[2] { CAPTION1, CAPTION2 };
xL.c_CustomMenuPullDownItem_Caption = CAPTION3;
xL.c_CustomMenuPullDownMenuItem_Captions = new string[3] { CAPTION4,
CAPTION5, CAPTION6 };
xL.SetUpMenu();
EnableMenuItems(true);
c_ExcelApp = xL.c_ExcelApp;
c_ExcelApp.WorkbookNewSheet +=
new Excel.AppEvents_WorkbookNewSheetEventHandler(
c_ExcelApp_WorkbookNewSheet);
c_ExcelApp.WorkbookActivate +=
new Excel.AppEvents_WorkbookActivateEventHandler(
c_ExcelApp_WorkbookActivate);
c_ExcelApp.SheetSelectionChange +=
new Excel.AppEvents_SheetSelectionChangeEventHandler(
c_ExcelApp_SheetSelectionChange);
for (int i = 0; i < xL.c_CustomMenuItem_Captions.Length; i++)
xL.c_CustomMenuItem[i].Click +=
new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(
CustomMenuItem_Click);
for (int i = 0; i < xL.c_CustomMenuPullDownMenuItem_Captions.Length; i++)
xL.c_CustomMenuPullDownMenuItem[i].Click +=
new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(
CustomMenuItem_Click);
WaitForEvents();
}
...
}
The code for the WaitForEvents() method is straightforward. It simply is intended to ensure that your application listens to the events fired by the MS Excel application. The code terminates when the instance of Excel becomes invisible (this might not be the most optimal way of determining whether the Excel instance has terminated, but it is working sufficiently well).
private void WaitForEvents()
{
do
{
Application.DoEvents();
if (!c_ExcelApp.Visible)
{
break;
}
Thread.Sleep(500);
} while (true);
}
In the sample implementation the custom menu items remain disabled until a workbook has been opened. I have used only one event handler for all custom menu items which switches to the method intended for each menu item based on the tag of the clicked item. Here is the abbreviated code for this event handler:
private void CustomMenuItem_Click(Microsoft.Office.Core.CommandBarButton Ctrl,
ref bool CancelDefault)
{
switch (Ctrl.Tag)
{
case CAPTION1:
CustomMethod1();
break;
case CAPTION2:
CustomMethod2();
break;
...
default:
break;
}
}
In the sample implementation the custom methods assigned to each of the custom menu items don't have much functionality and are included simply for demonstration purposes:
- "New Button 1" simply adds a series of numbers to column "A" of the active worksheet and then changes their font to italic.
- "New Button 2" creates a border for each cell and shades every other row in the user-selected range in the active worksheet
- Clicking on the menu items in the pull-down menu simply displays a form indicating with menu item was clicked 'You clicked ...' in the status bar for one second.
For reasons I was unable to determine, it turned out that using the MessageBox.Show() method in the event handlers didn't reliably bubble the resultant MessageBox to the top of the windows stack, at least the first time the event handler was called (it seemed to work fine on subsequent calls). I improved on this behavior by using the ShowDialog() method of a custom form whose Avtivated event I wired to the following event handler which ensured that the custom form always bubbled up to the top by making use of the API function SetForegroundWindow:
private void alert_Activated(object sender, EventArgs e)
{
SetForegroundWindow(((Form)sender).Handle.ToInt32());
}
I recommend the articles and books in the reference Section for further study on how to program the Excel object model. Regrettably the Excel object models for VB and C# are significantly different from each other. Rather than recreating the ExcelMenuExtensions in VB.NET I have included a dll version (GenericExcelSidekick.dll) which can be used in a VB.NET project by first explicitly adding a reference to GenericExcelSidekick.dll to your project and then perusing the VB version of my sample implmentation program (Program.vb).
Finally, as promised, a few words about the commented lines referring to a class I am calling LicenseGenie. Implementing this class as shown would incorporate a complete user license management framework into your appliction without having to write any additional code! If you find this intriguing I invite you to find out more about LicenseGenie on SoarentComputing's web site.
Points of Interest
The main thing I learned in the course of this project was how different the VB and the C# versions of the Excel model are from each other. Even the VB model is not identical to the old workhorse VBA, but still sufficiently close that it should be relatively easy for anyone with VBA experience to get up to speed. I also learned that either version is not quite as complete as the VBA version. For example I have yet to find the equivalent of the Selection property or the ActiveSheet.Range()L method. Here is a VBA code example that doesn't seem to have an exact equivalent in either the VB or the C# version of the Excel object model:
Dim r As Range
Set r = ActiveSheet.Range("A1:B10")
r.Select
Selection.Value = 1
In VB the following code produces the same result (where c_ExcelApp denotes the Excel object instantiated in Program.vb):
Dim r As Excel.Range = c_ExcelApp.Range("A1:B10")
r.Value = 1
In C# this is would look like this (again c_ExcelApp denoting the Excel object instantiated in Program.cs):
Excel.Range r = c_ExcelApp.get_Range("A1", "B10");
r.Value2 = 1;
References
- [2] "Excel 2003 Programming: A Developer's Notebook", J.Webb, O'Reilly, 2004
- [3] "Programming Excel with VBA and .NET", J.Webb & S.Saunders, O'Reilly, 2006
- [5] "Visual Studio Tools for Office 2007: VSTO for Excel, Word and Outlook", E.Carter & E.Lippert, Addison-Wesley, 2009
History
First revision published in July 2009.