in Chris Velazquez's words... - Running .NET Code from Microsoft Office 2003

Wednesday, February 07, 2007

Running .NET Code from Microsoft Office 2003

I can't believe that I've been racking my brains for hours trying to figure out how to execute my .NET 2.0 code in Microsoft Excel 2003. Hopefully this tidbit of information will save somebody else on the web some pain, frustration and time.

Punchline: You need a file called EXCEL.EXE.CONFIG in the same directory as EXCEL.EXE.

I created a class, written in C# and compiled with Visual Studio .NET 2005, with the .NET 2.0 Framework. This class has a COM Interop wrapper so that it can consumed by ActiveX clients, most notably Excel 2003. This class was compiled with the "Register for COM interop" setting in the properties turned on. This arrangement autogenerates the GUIDs and COM interface for you, though I prefer to define my own COM interfaces manually in production code.

Here is a sample class in C#:

// COMClass.cs
using System.Windows.Forms;

namespace AnyCOMLib {
   public class COMClass {
        public void DoSomething() {
            MessageBox.Show("I did something.");
        }
    }
}

When I compiled this C# class, Visual Studio generated the DLL and TLB files I expected. You can invoke it from a COM client, and it seems to work. Here is the VB code:

Dim thing As Object
Set thing = CreateObject("AnyCOMLib.COMClass")
thing.DoSomething

I typed out a simple VBScript file with this code, and it worked. A message box popped up saying "I did something.".

I created a simple VB 6.0 program with this code, and this worked, too. The same message box displayed.

I created a VBA macro in Excel 2003. This did not work. I had a message that read:

Run-time error '-2147024894 (80070002)':
File or assembly name AnyCOMLib, or one of its dependencies, was not found.

So I thought - If I can create a VB6 DLL that can be called from Excel (true) and VB6 code can call my COM-wrapped C# code (also true) then why not create a VB6 DLL that calls my C# code and call that from Excel? Unfortunately, Excel was not easily fooled by this subversion, and I continued to get the -2147024894 error. That was a waste of time.

After spinning my wheels for too long, I finally found something helpful. It turns out that I need a file named Excel.exe.config to be placed in the same directory as Excel.exe (typically at C:\Program Files\Microsoft Office\Office11). That file has the following format:

You have to make sure that you have the correct supportedRuntime elements for the .NET runtime to work.

Here are links to the pages that helped me find out about this:

newsgroup microsoft.public.dotnet.framework.sdk

topic in msnewgroups.net

These were specifically targetted for MS Word, but the same method applies to running .NET assemblies in Excel 2003 as well.

I hope this helps somebody!