- What's the Problem?
- Creating a Reliable Reference
- Defining the Workbook Connection
Creating a Reliable Reference
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: <AssemblyName>_bin. For example, my assembly is SimpleMessage, so the folder I created for it is SimpleMessage_bin and it appears as a child of my test directory that holds the Excel file. Once you create the directory and place your DLL in it, you need to perform a security setup. The process is relatively simple, but you must have administrative privileges. The following steps show you how to perform this task.
-
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.
Figure
1: Configure your DLL using the .NET Configuration 1.1 console. -
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.
)
Figure
1: