Mine Field of Excel Development Tools – Part 6
In Part 5 of this unintended series I provided complete details on how to create an Excel Automation Add-in using the COM Shim Wizard from Microsoft. It works for development, not for deployment. I’ve found that in the deployment system some registry keys are missing.
It seems to me that this is the fault of the COM Shim Wizard since that creates some of the registry entries. I did put in the fix documented by Andrew Whitechapel and I’m sure it will help, but without the right registry keys that specific fix won’t make this code work. It seems there is something I’m not adding to the Setup project. You’d think adding the Project Output from my code, and both shim projects would have done the trick, but something manual may still need to be added. In a comment to Part 5 a visitor says he needed to add Extensibility.DLL to his Setup project. Sure, I can try that, but I don’t believe that’s what’s causing the registry entries to get lost.
I’ve provided below a detailed list of the registry keys that are present, and I’ve highlighted the missing ones. I’m really hoping someone from Microsoft can take a look, maybe follow my instructions from Part 5, and tell us exactly what’s missing. I’ve masked the name of my executables and GUIDs, and some of the lines wrap in this page, but that shouldn’t make any difference for someone who knows how to read the registry.
; ok
[HKEY_CLASSES_ROOT\CLSID\{GUID-01}]
@="MyCode.UDF01"
[HKEY_CLASSES_ROOT\CLSID\{GUID-01}\ProgId]
@="MyCode.UDF01"
; ok – shows the shim code executed during Excel addin process
[HKEY_CLASSES_ROOT\CLSID\{GUID-01}\Programmable]
; missing
[HKEY_CLASSES_ROOT\CLSID\{GUID-01}\Implemented Categories]
; GUID-02 is all over source system – what is it?
[HKEY_CLASSES_ROOT\CLSID\{GUID-01}\Implemented Categories\{GUID-02}]
; Regarding this key:
[HKEY_CLASSES_ROOT\CLSID\{GUID-01}\InprocServer32]
; Following values are present, but they should not be:
; ThreadingModel=Apartment
; default=C:\Program Files\MyPath\ShimCode.dll
; following values should be present, not those above.
[HKEY_CLASSES_ROOT\CLSID\{GUID-01}\InprocServer32]
@="mscoree.dll"
"RuntimeVersion"="v2.0.50727"
"Class"="MyCode.UDF01"
"Assembly"="MyCode, Version=1.0.2905.24564, Culture=neutral, PublicKeyToken=mykey123abc"
"CodeBase"="C:\\Program Files\\MyPath\\MyCode.dll"
"ThreadingModel"="Both"
; missing
[HKEY_CLASSES_ROOT\CLSID\{GUID-01}\InprocServer32\1.0.2905.24564]
"Class"="MyCode.UDF01"
"Assembly"="MyCode, Version=1.0.2905.24564, Culture=neutral, PublicKeyToken=mykey123abc"
"CodeBase"="C:\\Program Files\\MyPath\\MyCode.dll"
"RuntimeVersion"="v2.0.50727"
; ok
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{GUID-01}]
@="MyCode.UDF01"
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{GUID-01}\ProgId]
@="MyCode.UDF01"
; ok – shows the shim code executed during Excel addin process
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{GUID-01}\Programmable]
; missing
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{GUID-01}\Implemented Categories]
; missing
; GUID-02 is all over source system – what is it?
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{GUID-01}\Implemented Categories\{GUID-02}]
; missing
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{GUID-01}\InprocServer32\1.0.2905.24564]
"Class"="MyCode.UDF01"
"Assembly"="MyCode, Version=1.0.2905.24564, Culture=neutral, PublicKeyToken=mykey123abc"
"RuntimeVersion"="v2.0.50727"
"CodeBase"="file:///C:/MyDevelopmentPath/MyCode/bin/Release/MyCode.dll"
; Need to change Codebase for target platform?
; Regarding this key:
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{GUID-01}\InprocServer32]
; Following values are present, but they should not be:
; ThreadingModel=Apartment
; default=C:\Program Files\MyPath\ShimCode.dll
; following values should be present, not those above.
@="mscoree.dll"
"ThreadingModel"="Both"
"Class"="MyCode.UDF01"
"Assembly"="MyCode, Version=1.0.2905.24564, Culture=neutral, PublicKeyToken=mykey123abc"
"RuntimeVersion"="v2.0.50727"
"CodeBase"="file:///C:/ MyDevelopmentPath/MyCode/bin/Release/MyCode.dll"
; Need to change Codebase for target platform
; missing
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\MyCode.UDF01]
@="MyCode.UDF01"
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\MyCode.UDF01\CLSID]
@="{GUID-01}"
That’s the news so far. My next step is to insert the missing keys into the target registry using a .reg file. If that works then I may be able to get away with manually adding the process to the Setup project. I’ll let you know…
Links to all articles in this series
Hi,
KB908002 (http://support.microsoft.com/kb/908002) seems to fix the problems, sort of.
I had the same problem you did. Plenty of simple examples that worked on the VS machine that failed to work on a target without any messages anywhere!
Although I haven’t tested the truth of it yet, the KB does state you need to run the patches for each user 🙁
Good luck,
Tim
Holy Cow, that looks so promising! Thanks Tim, I’ll check it out within the next couple weeks (travelling).
Hi Tony,
Great work on this series… wish I had found your blog sooner, it would have saved me a world of hurt!!
I realize that a couple years have past since the last update here, but nevertheless its still pretty relevant stuff, disappointingly very little has been done by Microsoft to make this diabolical process easier.
I have spent the last two weeks on getting my automation add-in right, I now have my solution, even though I am still working out all the kinks in production, but at least I have successfully deployed.
I went the route of VSTO with two assemblies (I noticed you wrote about this option somewhere but hadn’t explored it), this was incidental though, had I seen your blog I might have taken a different route… the idea is that they are both COM Visible, strong name signed and the Assembly with my UDF Class implements IDTExtensibility2 with the difference being that it is registered for COM Interoperability… I have to run regasm /codebase it seems on both dll’s after installing and only then does it work. I have seen some Microsoft bug reports about this, that the types don’t get registered correctly unless you explicitly do this.
I am not well versed yet with all of the semantics, VSTO and COM are new to me, but I appears I can get a reference to the Excel application using IDExtensibility2, it seems to be the same instance as well but my managed VSTO code resides in another AppDomain so I simply marshal the objects I need using domain.CreateInstanceAndUnwrap, and those objects extend MarshalByRefObject and are serializable so interoperability is quite seamless.
People suggest performance is not ideal with this approach, but I disagree, its certainly acceptable (they run within the same process for goodness sake) I find its pretty fast (even close to instant), and if you design it well even better.
I am using the VSTO managed code to preload some large datasets, and they don’t get too much larger than the ones I have, which I indecently fetch using SOAP Web services, native SQL will be even faster, and these DataSets can even be cached now.
I didn’t go the shim route, in theory that does look like a better option from reading your blog, nevertheless I thought I would share my alternative approach that seems to do the job, being slightly simpler to implement and deploy.
Cheers!
Peter
Peter, I thank you for your comments. I see I never finished this series, but I did have some success with Excel development. See my other posting about a product that unfortunately never went production:
http://nebula-rnd.com/blog/tech/2009/03/xchange-intro1.html
Skip the text and just notice that I do have nice formulas like this: =NxRead(“server”,”file,id,atb,val,sv”,”options”).
Then notice the screenshot of the Database Explorer I built into Excel, plus toolbar buttons, etc.
All of that was done with a great product called Add-in Express http://www.add-in-express.com/. I can’t say enough good things about the software, people, and community for that line of products (without drooling on my keyboard). Their helper libraries saved me from all the pains documented in this blog – but by the time I got to savor the tasty goodness of success I had to move on to other business, and I never got to publish that offering. Perhaps one of these days I’ll get a chance.
I strongly encourage Microsoft Office developers to get familiar with the Add-in Express offerings. The documentation might leave you wondering where you separate VSTO from their components, when you need either or both. But the answers are out there, at least you have a choice, and success is on the other side. Just looking at the screenshots you can see some great things done with Excel. End-users look at that and think “I expect this of Excel” but developers like us recognize the pain required to make it all happen, and wonder if it’s even possible outside of Redmond. That pain was eliminated with Add-in Express. (End unintentional and unpaid marketing speech.)
I hope I can find some time just to blog about Add-in Express, which I used successfully for both Excel and IE integration, but unfortunately not for anything for sale yet. Good luck!