Mine Field of Excel Development Tools – Part 5

Starting from scratch

Anyway… Here are the steps to shim your code (all examples are for C# but easily translated for VB.NET or other languages):

  1. If the COM Shim Wizard v2.3 isn’t installed yet, be sure VS2005 is closed and install it. Then open VS.
  2. Create a new solution.
  3. Add a new project, Other Project Types, Extensibility, Shared Add-in. This creates a good base class that implements IDTExtensibility2.
  4. If References does not include Microsoft.Office.Core, add the Microsoft Office 11 Object Library now.
  5. Add a Using Microsoft.Win32.
  6. Create a new Interface above the class. The class should inherit from this interface. Change the name of the class to something more meaningful than Connect. Here is a sample of the top of some code. It would be good to use the sample from the COM Shim v2.3 as a base.:

[GuidAttribute("CCCE1234-CC11-CCCC-BC5E-CCCC123FCCCC")]
public interface IMyInterface
{
  string HelloWorld();
}
[Guid("BBBB6789-BBBB-4b3f-BBBB-A6788A3CBBBB")]
public interface IAppDomainReport
{
  string GetAppDomain();
}
[Guid(MyClass.TypeGuid)]
[ProgId(MyClass.TypeProgId)]
[ClassInterface(ClassInterfaceType.AutoDual)]
public class MyClass : Object ,
        Extensibility.IDTExtensibility2 ,
        IMyInterface,
        IAppDomainReport
{
  internal const string TypeGuid =
        "AAAA7438-AAAA-4FD0-9373-AAAA088CAAAA";
// Note: there seems to be an undocumented limit of
// 39 chars for the ProgId for automation add-ins.
// Also, you cannot use underscores in the ProgId.
// (Helpful comment from COM Shim Wizard sample)
  public const string TypeProgId = "MajorName.MinorName";
  const string SubKeyName =
        @"CLSID\{" + TypeGuid + @"}\Programmable";
  public string HelloWorld()
  { return "Hello World!"; }
  public string GetAppDomain()
  {
    return AppDomain.CurrentDomain.FriendlyName;
  }

  1. Notice the HelloWorld and GetAppDomain methods at the bottom of that code which implement the functions defined in the interfaces. If the following methods are not in the code, add them now.
  2. Much has been said about the ClassInterfaceType.AutoDual. I don’t think this has anything to with my deployment issues but the topic is open for commentary.

[ComRegisterFunction]
public static void RegisterFunction(Type type)
{
  Registry.ClassesRoot.CreateSubKey(MyClass.SubKeyName);
}

[ComUnregisterFunction]
public static void UnRegisterFunction(Type type)
{
  Registry.ClassesRoot.DeleteSubKey(MyClass.SubKeyName);
}

  1. Obviously you’re going to want the code to do something else. Define your methods in the interface and implement them in MyClass.
  2. Go to Properties, Application, Assembly Information. Make assembly COM-Visible. No other info there is required for a test.
  3. Still in Properties, go to to the Build tab and check "Register for COM interop".
  4. Still in Properties, go to Signing, check "Sign the assembly". Create a new snk file, or a pfx file with a password. Save and exit Properties.
  5. Build this project.
  6. Add a new project: Other Languages, C++, COMShims, Addin Shim.
  7. Point the assembly selection to your shared addin project with MyClass.
  8. Only select Excel as a target and be sure to check the box that this is an Excel Automation Addin.
  9. The project is created along with the other ManagedAggregator project. There’s a little bug here that needs to be fixed. Go to the shim Properties, Build Events, Post-Build-Event. Double click the "…" button to open a text box to edit the commands that get executed when this project is built. Note that the assembly for ManagedAggregator gets copied from \bin\debug. If the project is in Release mode, which I think is the default, this build step will fail. The copy needs to agree with the active build mode. Change to \bin\Release as required. You will also note that the copy doesn’t use $(SolutionDir) and you might want to tweak that just for elegance.

Let’s break for a moment. Notice in the Build-Event block that regsvr32 is called to register the COM shim on your development system. This is where registry entries are created that are later used for deployment (at least I think so – and this might be part of what causes my deployments to fail). In the shim code there is a Resource File called ConnectProxy.rgs. This file has registry key definitions for HKCR and HKCU. The GUID here is the one defined as TypeGuid in the MyClass. The ProgID here is the TypeProgId in MyClass. When you open the list of Automation Servers in Excel, your going to see the ProgID in the list. (This is good to know since there are a couple hundred items in that list to sift through.) If you want to change that description or you need to change the GUID in MyClass, you need to change this .rgs file!

In many blogs and articles you’ll see notes that the ComRegisterFunction from MyClass needs to add a "Programmable" registry key. This is no longer necessary because you’ll see that the key is in the .rgs file.

Note the reference to ‘Connect Class’ in the .rgs file. I’ll come back to this later as this may be related to my deployment problem.

The name of the COM DLL is embedded in many places in the shim project. If you have a need to change that name, delete the shim project and the ManagedAggregator project, make changes to MyClass, and re-run the COM Shim Wizard. (Don’t forget to tweak the build events if required, you may want to just copy that text somewhere so you can copy/paste it later.) You may find yourself doing this wholesale delete and regeneration process a few times.

3 thoughts on “Mine Field of Excel Development Tools – Part 5

    • Looks like I didn’t post this anywhere, not sure in all of this mess.

      Andrew Whitechapel has a recent blog entry with a bug fix for the COM Shim Wizard v2.3.  I haven’t had time to try it but it could be the solution to all of these problems.

      http://blogs.msdn.com/
      andreww/archive/2007/11/25/com-shim-clr-loader-bug.aspx

      I’d really appreciate it if someone could tell me if this actually fixes deployment issues.  I’ll check it myself in the next couple days.

    • hey tony — great posts, I only wish I had found them a bit earlier, as I’ve recently gone through the exact same miserable experience trying to develop automation add-ins for excel. although I haven’t bothered to shim my add-ins (yet) I was able to solve my non-dev box deployment issues by adding the exensibility dll in my setup project. Have you had any luck since this post on successfully deploying?
      And on the topic of deployment, have you come across anything on using click once to automate updates for add-ins? Since there’s no Publish tab in the project properties, I’m guessing VS isn’t going to be a pal and do it for me. Any suggestions rather than making my users manually go through a wizard each time I update?
      thanks for all the useful stuff.

    • I have found that there are specific registry keys that are not set on the target box, though they are set on the source/development box. My first guess was that something in the Setup project wasn’t creating the keys, but then the keys do get created in another system with VS and the .NET SDK vs the redistributable runtimes (.NET and Office PIAs). I’ll post article #6 in a few days with details, and I’ll check to see if manually loading the keys to the target gets it to work.

      It’s also interesting that the missing keys aren’t for the shim but for the function code itself – and that’s confusing since this was added to the Setup as part of the Primary Output. I know the shim is executed because I can add the shim to Excel addins, and the Programmable registry key gets created from the shim.

      About Extensibility.DLL – yeah, I mentioned that in step 20 of Part 5 here, but I haven’t tried adding it manually yet. There are so many "maybe I should try this" permutations and very little time to experiment with change, build, deploy, uninstall old version, install new, test, fail, go back.

      About click-once: I think with a shim a new assembly for functions can simply substitute the original, as long as there are no versioning issues. If the code is binary compatible and the GUIDs are the same, there’s nothing to differentiate an old DLL from a new one. In this case the shim is managed code calling to our managed code, so all of those rules apply, including click-once. If you’re not using a shim then there may be an issue with COM calling a different managed assembly (though I don’t think so) and that could mandate full re-installs for users.

      HTH

Leave a Reply