Mine Field of Excel Development Tools – Part 5
It’s time to call it quits for now. Yup, I give up. I’ve killed an entire week on this, well over 100 hours. I have clients that need attention and this project isn’t earning its keep. The code is working great on development systems but I can’t get it to work properly on a non-development workstation. I’m am closing this for now with a definitive current tutorial which works to some extent – it doesn’t work entirely but the data is much more accurate than old blog and forum entries you’ll find out there. Maybe someone from Microsoft can correct the errors – or look at my systems with me so that we can make this work and document it for everyone!
I’ve been wrong about a few things which I’ve corrected here. I’m too lazy to go back and edit my previous notes. I’ve now done this so many times now that I can accurately summarize here the steps for creating an Excel Automation Addin concisely.
Creating an Excel Automation Addin for Office 2003
Here’s the high level view:
- Create a Shared Addin. This is the managed code that will get executed.
- Create an Addin shim. This is what gets registered with Excel. It’s a C++ project that won’t require any code changes so don’t get uptight. This creates another project called ManagedAggregator that serves as a proxy to the shared addin. No changes are required here either. The C++ code is not managed .NET. It registers with Excel and then instantiates the ManagedAggregator class which then provides access to your managed code.
- Create a Setup package. Deploy this.
- After installation you should be able to open Excel, Tools, Addins, Automation. There you should see the shim DLL. Adding this Automation Addin should allow you to use =HelloWorld() to execute managed code.
We’re off to see the Wizard
I originally thought the v2.3 COM Shim Wizard still needed the tweaks that Andrew Whitechapel published – note my list of issues in a previous post. Then I thought the Wizard did everything with no tweaks required. That was a step too far in the other direction. The v2.3 Wizard does a great job of creating a shim, even for Office 2003, but it’s not perfect.
That said, there is an Excel Automation Addin Sample that comes with the v2.3 shim and it includes code which really helps to make this effort almost a no brainer. Rather than using my own code for the primary managed interface, I later chose to use that sample code as a base and retrofit it with customizations. Rather than putting app code directly into a package like this, you may find it better to do nothing but reference a completely separate managed assembly and simply use this sample as (yet another) proxy to your code. Ultimately I think this will mean you just need to provide upgrades that include your application assembly, without asking a user to completely uninstall and reinstall this Excel/shim interface along with your code. We’ll see about this later – there might be versioning issues I’m not thinking of yet.
Big thanks to the people at Microsoft for creating the shim wizard originally as an unsupported personal initiative, and then making it an official utility!