Extending Excel with .NET

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):


See full detail: http://www.codeproject.com/KB/office/extending_excel.aspx

Comments

Post a Comment

Popular posts from this blog

Asynchronous Socket Programming in C#

Image
Objective The objective of this article is to demonstrate a socket-based client/server application that will allow two-way asynchronous communication between a server and multiple client applications. Because this example uses asynchronous methods, the server application does not use threads to communicate to multiple clients (although internally the asynchronous communication mechanism uses threads at the OS level). The Difference Between Synchronous and Asynchronous Communication in Network Programming The key difference between synchronous and asynchronous communication can be explained with an example. Consider a server application that is listening on a specific port to get data from clients. In synchronous receiving, while the server is waiting to receive data from a client, if the stream is empty the main thread will block until the request for data is satisfied. Hence, the server cannot do anything else until it receives data from the client. If another clien
Read more

Url Routing MVC TUTORIAL

Image
This tutorial covers an introduction to the MVC pattern using Visual Basic. It also includes a brief overview of the URL routing assumptions that are a foundation to the implementation of the pattern. A sample application is created to demonstrate the fundamentals of this new web-application model. Implementing business rules and testing is also covered. Finally, the use of two alternative view engines, Silverlight and XML Literals, is coverd.
Read more

WCF Chat Sample

Image
The Chat sample demonstrates how to implement a multiparty chat application by using PeerChannel. Messages sent by any instance of a chat application are received by all other instances. To get samples and instructions for installing them Do one or more of the following: On the Help menu, click Samples . The Readme displays information about samples. Visit the Visual Studio 2008 Samples Web site. The most recent versions of samples are available there. Locate samples on the computer on which Visual Studio is installed. By default, samples and a Readme file are installed in drive :\Program Files\Microsoft Visual Studio 9.0\Samples\ lcid . For Express editions of Visual Studio, all samples are located online. For more information, see Locating Sample Files . Security Note: This sample code is intended to illustrate a concept, and it shows only the code that is relevant to that concept. It may not meet the security requirements for a specific environment, a
Read more