Mine Field of Excel Development Tools – Part 4

Andrew Whitechapel’s generous bloggings on Automation Add-ins and shims are chock full of helpful information. Unfortunately the blog entries are also over two years old and I’ve just found out the code details are no longer valid.

See links to Andrew’s blog and others in my first couple postings on this topic.

I decided to take it to the next step and use a shim between COM and .NET. I started with the details provided in this MSDN article. I installed the v2.3 COM Shim Wizard. I created a project as described by Andrew. I stopped after signing my assembly and went back to the MSDN document for the walkthrough with the Wizard.

At the end of step 13 the MSDN doc says to build the projects. It seems the post-build event handler for the unmanaged shim has an instruction to copy the Debug DLL for ManagedAggregator. You may need to edit this to get the Release version, otherwise the solution build may fail. Outside of this everything went fine with the Wizard.

Back to Andrew’s blog, from step 6-forward the detailed information simply doesn’t apply to the code generated from the v2.3 COM shim wizard. That’s unfortunate – that leaves me to wonder if anyone has created a similar document to patch what v2.3 creates – or if v2.3 isn’t supposed to need the tweaks that v2.0 required. Without making tweaks suggested by Andrew I was able to build the Setup routine and load my add-in on another development system. As with all of my add-ins, it worked perfectly, but not on a user workstation.

Note that the Primary Output of ManagedAggregator, the shim, and the shared add-in must all be added to the setup project for it to work properly.

Though the results seem identical to my prior results, and it seems like I’ve made no progress, I believe the code is now running through the shim in a separate application domain, and that’s very good for security and stability. Everything I read suggests shimming is a good thing, so I’m glad I’ve made it this far. Given all that, I’m not sure yet how I can check to ensure that my code before was not running in a separate domain while my code is in a separate domain now. I think all I need to do is check the Task Manager – I’ll let you know later.

Why are Andrew’s notes no longer valid?

• There are no references in the solution to AutomationAddin. This causes many statements to fail.
• There is no IID__IOurNewInterface
• There are no references to pCLRLoader->CreateInstance
• The "using" statement is in ConnectProxy.h, not ConnectProxy.cpp.
• In step 10 the regsvr32 and copy are already in the post build task for the ManagedAggregator.
• ManagedAggregator isn’t mentioned at all and I suspect some changes aren’t required because of its presence.
• ConnectProxy.h ends with ‘private’ modifier, not ‘protected’ – should it be changed?
• A long list of other errors are returned from the build after the tweaks are made – to the best of my ability anyway given that other things were missing and there wasn’t a chance in hell of the tweaks working anymore.

Even the code at the more recent blog entry is invalid with v2.3 

It’s obvious that changes have been made in the C++ code generated from the Wizard and it just doesn’t match the patch generously provided by Andrew anymore.

Summary … for now…

I’m still thinking there are two possible issues.

First, is there a security issue on the workstation with COM invoking mscoree.dll or my managed add-in? I don’t think so. I think somewhere in my testing I got this to work once and it never worked again. I’m wondering if this has to do with the level of trust on the workstation for the signed assembly. I signed it with a pfx file – maybe it needs a snk file – or maybe I need to do something else with caspol to get the workstation to trust that signature.

Second, maybe it’s the COM/.NET interface. It seems to me that on the workstation the middle tier just isn’t getting invoked, whether mscoree or the shim or whatever does the proxy from COM to managed code. I’m really inclined toward this being the issue.

It’s time for someone at Microsoft to blog some new info. It would really help if someone there documented the next step after the COM Shim Wizard v2.3 for deployment to a workstation. There was some discussion about the new shim wizard at Misha Shneersons blog, including some comments that look similar to the issues I’m having. I’ll check there for more info. In fact, that blog entry links to this other one with some good info about add-ins – the quote "Regasm’ing is not good enough for addins" catches my eye as this might be the reason why the workstation can’t execute the addin. I’ll let you know later.

I’m going to do more research on deployment of Excel Automation Add-ins, and the details of the path from COM to managed code. Maybe I’ve missed something. You’ll find some nice pictures showing how this process works at some of the MSDN links I’ve provided.