Creating .NET Extensions for Office Applications
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.
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\11.0.0.0__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.The reason I mentioned my early experiments is that sometimes Microsoft doesn't explain things well or at all, leaving the developer to make insane experiments in search of information. It turns out that, in this case, I was chasing a red herring, and I didn't need to look any further than the Visual Studio IDE for the resources I needed. Simply because an assembly appears in the \Windows\assembly folder doesn't mean you should necessarily use it. In this case, you don't need to use it at all.
To create a reliable reference for an Office product, you don't rely on the standard .NET reference; you use a COM reference instead. Right click the References folder in Solution Explorer and choose Add Reference from the context menu. You'll see the Add Reference dialog box. Select the COM, not the .NET, tab. Locate the Microsoft Office 11.0 Object Library entry and click Select. Do the same for an Office product, such as Excel. In this case, you locate the Microsoft Excel 11.0 Object Library entry and click Select. Click OK and you'll notice that something very odd happens.
The Visual Studio IDE does add the two references, but they don't have the same names they would normally. The Excel reference actually appears as Excel in the References folder. It should have a name with the word interop in it, but the reference is simply Excel. Likewise, the Microsoft Office 11.0 Object Library appears as Microsoft.Office.Core. It seems as if you end up using those .NET assemblies anyway; you just have to follow a roundabout way to do it. Adding these two references also adds a reference to VBIDE, which allows you to interact with Office macro environment.
Creating a Simple Add-on
Creating an add-on need not be difficult. You can make an add-on as simple or as complex as needed for a given task. However, you have to make certain changes to the techniques you use to define class code in a DLL to accommodate Office 2003. The first such change is the use of a new attribute. This attribute creates a connection between the Office application and your DLL, as shown here.
// Define the starting point for the assembly.
[assembly:System.ComponentModel.DescriptionAttribute(
"OfficeStartupClass, Version=1.0, Class=SimpleMessage.DisplayMessage")]
This may not look like very significant code, but you must include it for every add-on you create or you get a strange message when you start the Office application, saying that it couldn't find or load your DLL. The OfficeStartupClass value defines the descriptive attribute—the one that the Office application looks for. The Version argument defines the version of your add-on and the Class argument defines the starting point for the code. This starting point is exceptionally important because you must include two special methods in it, as shown in Listing 1.
Listing 1: Defining the _Startup and _Shutdown Methods
// A connection to the application.
private Application MyApp;
// A connection to a loaded workbook.
private Workbook MyWorkbook;
// A connection to a worksheet within the workbook.
private Worksheet MyWorksheet;
public void _Startup(object thisApp, object thisWorkbook)
// Save the application information.
this.MyApp = thisApp as Application;
// Save the workbook information.
this.MyWorkbook = thisWorkbook as Workbook;
// Get the first worksheet from the current workbook. MyWorksheet = MyWorkbook.Worksheets[1] as Worksheet;
// Display a message on the worksheet.
MyWorksheet.Cells[1, 1] = "The add-on has started!";-}
public void _Shutdown(){
// Return any resources.
MyApp = null;
MyWorkbook = null;
MyWorksheet = null;}
The example works with Excel, so you need a connection to the application, the workbook, and a worksheet to do anything worthwhile. Excel calls the _Startup() method when it loads the DLL and expects you to perform any required initialization in this method. The example saves references to the application and workbook, and then obtains a reference to the first worksheet. You can refer to worksheets either by index number or by name. The example then displays a message in the upper left cell of the worksheet.
Any global variables you create when working with an Office application must also be deallocated when the application unloads. Excel calls the _Shutdown() method to perform this task. It's possible to create a memory leak in your application if you don't remember to do this, because you're using the interoperability layer. Remember that Excel isn't managed code.
Listing 1 is a functional application. It really does display the message in the worksheet. Although this isn't a very interesting application, it demonstrates that you don't have to do anything fancy.
Handling .NET Security Issues
Okay, so you have a nice shiny new DLL you want to run with Microsoft Office. You need to perform a security configuration before you do anything else. Microsoft recommends that you create a child directory for your add-on with a name like this:
Use the Start | Programs | Administrative Tools | Microsoft .NET Framework 1.1 Configuration command (whew!) to open the .NET Configuration 1.1 console shown in Figure 1. You can also open this console using the Microsoft .NET Framework 1.1 Configuration icon in the Administrative Tools folder of the Control Panel. Select the Runtime Security Policy folder, as shown in Figure 1. This folder contains the security settings for the assemblies, a Dynamic Link Library (DLL) in this case.
Click Increase Assembly Trust. You'll see a Trust an Assembly dialog box. Select the Make Changes to this Computer option and click Next. The wizard will ask you which assembly you want to trust. Locate the DLL file you created. Click Next. The wizard will ask what level of security you want to assign to the assembly. Set the slider for this assembly to Full Trust. Click Next. You'll see a Completing the Wizard dialog box. Verify the settings in this dialog box.
Click Finish. The assembly is now ready for use at its current location. If you move the assembly, you must also reset the security. There's one last task to accomplish before you can use even the simple DLL described in this article. You must create a connection between the workbook and the DLL or Excel will never load it. To perform this task, you must add special entries to the workbook properties. Simply use the File | Properties command to display the workbook Properties dialog box. Select the Custom tab.
You must make two custom entries to load the DLL. The first describes the location of the file. Type _AssemblyLocation0 in the Name field and the DLL location in the Value field. Use a relative location to make it easier to move the workbook to another location. For example, my assembly is located in the .\SimpleMessage_bin folder. Click Add when you complete the entry.
The second custom entry describes the name of the assembly without the file extension. Type _AssemblyName0 in the Name field and the DLL name in the Value field. Click Add again when you complete the entry.
At this point, you can close Excel and reopen the workbook. The add-on will load. In my case, I saw a message in the upper left corner saying the add-on had started.
Putting it All Together
This is a very short introduction to a much longer process for most developers, but you now have the tools to work with Office 2003 through Visual Studio without spending a lot of cash to do it.
Essentially, you need to perform four tasks. First, create your project and Office references using the COM, not the .NET technique. Yes, you'll still end up using those .NET modules listing in the \Windows\assembly folder, but not directly. Second, write code that includes the required DescriptionAttribute, a _Startup() method, and a _Shutdown() method. You can add as much additional code as you like, but every DLL must contain these three elements. Third, create a folder for your DLL and set security on it. Fourth, add the two custom properties to the document (workbook, Word file, Access database, whatever) to create a connection between the document and your DLL.
The one word of warning I can provide is start slow. Build your application with care because even with the Microsoft supplied debugging techniques, you'll find it difficult to troubleshoot an add-on that grows too quickly. I've seen a few developers put relatively complex DLLs together, but the successful add-ons were built one step at a time.
Read other web hosting Articles
- SHOWCASES -
