Writing an Excel COM AddIn - Public Content

Writing a plugin in C# for Excel

There are not a lot of references that actually tell you how to do this. There are many that tell you partially how to do it, but they don't give you the full info.

We will be writing an "In-Process" COM add-in

Start a new project
Right-click on the solution, Properties, then "Configuration Properties",Build,Register for COM Interop,TRUE

Write An Interface for the functions you want to implement.

public interface IMyFunctions {
    string myGetVersion();
  }

Write an implementation for the interface

namespace Junk {
 public class MyFunctions : IMyFunctions {
   public string myGetVersion() {
    return "0.1";
   }
 }
}

Add in COM interop stuff.

Note that quite a few places will tell you to use ClassInterfaceType.AutoDual. Don't. All that does is 'magically' write an interface for this function to export, as it is the interface thats exposed, not the class. By writing the interface, you save that bother.

Note also that only the first named interface is exposed, so if you need multiple interfaces, you will need to create a superinterface for them all.

using System;
using System.Runtime.InteropServices;

namespace Junk {
 [ClassInterfaceAttribute(ClassInterfaceType.None), ComVisible(true)]
 public class MyFunctions : IMyFunctions {
   public string myGetVersion() {
    return "0.1";
   }

   #region windowsStuffThatsNotUnderstandable
   [ComRegisterFunctionAttribute]
   public static void RegisterFunction(Type type) {
     Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type));
   }

   [ComUnregisterFunctionAttribute]
   public static void UnregisterFunction(Type type) {
     Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type),false);
   }

   private static string GetSubKeyName(Type type) {
     string s = @"CLSID\{" + type.GUID.ToString().ToUpper() + @"}\Programmable";
     return s;
   }
   #endregion
 }
}

Write an implementation for IDTExtensibility2 interface.

This allows you to listen in to when your add-in gets loaded and unloaded. It will allow you to add and remove menus too.

Add reference to Extensibility.dll, and Office Objects v11.

Note that only the first interface is exposed to COM, so the IDTExtensibility2 thing shouldn't show up.

using Extensibility;
using System;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

namespace Junk {
 [ClassInterfaceAttribute(ClassInterfaceType.None), ComVisible(true)]
 public class MyFunctions : IMyFunctions, IDTExtensibility2 {

   Excel.Application application;

   public string myGetVersion() {
    return "0.1";
   }
   #region windowsStuffThatsNotUnderstandable
   [ComRegisterFunctionAttribute]
   public static void RegisterFunction(Type type) {
     Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type));
   }

   [ComUnregisterFunctionAttribute]
   public static void UnregisterFunction(Type type) {
     Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type),false);
   }

   private static string GetSubKeyName(Type type) {
     string s = @"CLSID\{" + type.GUID.ToString().ToUpper() + @"}\Programmable";
     return s;
   }
   #endregion

   #region IDTExtensibility2_Implementation
   public void OnAddInsUpdate(ref Array custom) {}
   public void OnBeginShutdown(ref Array custom) {}

   public void OnConnection(object application, ext_ConnectMode connectMode, object addInInst, ref Array custom) {
     this.application = (Excel.Application) application;
   }

   public void OnDisconnection(ext_DisconnectMode disconnectMode, ref Array custom) {
     this.application = null;
   }

   public void OnStartupComplete(ref Array custom) {}
   #endregion
 }
}

Add in to Excel

It will be under Tools, Add Ins, Automation

Disting it out

When building an MSI, for some reason I couldn't get it ro register the assembly automatically.

run:

c:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\RegAsm /codebase yourdll.dll

from the deployment directory.

References

http://blogs.msdn.com/eric_carter/archive/2004/12/01/273127.aspx