Creating .NET Extensions for Office Applications
- What's the Problem?
- Creating a Reliable Reference
- Defining the Workbook Connection
You might have heard rumblings about the new .NET connectivity in Office 2003, but you may not have had the opportunity to see it in action. After playing around with a few add-ons, I'm honestly impressed with what you can do with this technology. In this article, I'll share a few programming tips I've learned.
This technique only works with Office 2003. Older Office versions don't have the required functionality. When you install Office 2003, make sure you also include the .NET interoperability feature, because it's key in creating add-ons.
According to Microsoft, you also need a relatively expensive toolkit for Visual Studio .NET named Microsoft Visual Studio Tools for the Microsoft Office System, and they provide some lovely examples and other documentation. However, with a little careful programming, you can create add-ons without investing your life savings in this toolkit. (I'll dispense with the toolkit's long name for this article and simply call it the toolkit.)
What's the Problem?
Before you go any further, you need to understand that Microsoft doesn't approve of, support, or even recognize the technique I demonstrate in this article. I actually discovered the technique quite by accident by playing with some Microsoft examples. In addition, my technique is completely manual; you don't get any of the goodies in the Microsoft toolkit. That said, I've discovered a lot about the .NET Framework by going through this experience and you will, too. In addition, you'll discover that sometimes automation isn't required to get the job done.
Whenever you start a new project in Visual Studio .NET, you select a project from the list that Microsoft provides. Installing the toolkit provides a number of new project types that revolve around Microsoft Office. You can create any of these projects as part of a simple DLL, so the toolkit buys you automation, not a truly new project type.
Projects rely on assembly references. Consequently, I was interested to see that the Microsoft projects all included a reference to
Microsoft.Office.Core plus an interoperability assembly. For example, to work with Excel you use Excel assembly, which actually appears as
Microsoft.Office.Interop.Excel in the \Windows\assembly folder. The only problem is that you can't access these assemblies. For example, right click on References and choose "Add Reference" from the context menu. The
Microsoft.Office.Interop.Excel assembly appears in the GAC (check it out in the \Windows\assembly folder), but it doesn't appear in the Add Reference dialog box. Clicking Browse won't help; you can't access the DLL.
All these assemblies are located in the bowels of the Windows folder hierarchy, and any attempt to reference them from your project is going to fail. For example, the
Microsoft.Office.Core assembly appears in \WINDOWS\assembly\GAC\Office\126.96.36.199__71e9bce111e9429c\Office.dll. Try to access this folder with either Visual Studio .NET or Windows Explorer and you'll fail.
However, a curious thing happens when you open a DOS prompt. Suddenly, you can access the required folder.
My early experiments with Microsoft Office interoperability revolved around copying the required DLLs from the hidden folders to a folder I could access, and then creating a reference to them from Visual Studio .NET. The technique works, but it's a bit cumbersome and you'll find it has problems because the assembly reference doesn't appear in the Global Assembly Cache (GAC) unless you register it. This little kludge also means you have to register it on every user machine that relies on your custom add-on. Therefore, while this technique is wonderful for experimentation, I needed another method.