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!
3 thoughts on “Mine Field of Excel Development Tools – Part 5”
Leave a Reply
You must be logged in to post a comment.
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