If you’ve tried out VSTO (and you should ) then you may have noticed that Word has both Document and Template projects whilst Excel has only a Workbook project. If you were thinking that the reason was because Excel was naughty but Word was nice, you’d be half right. And if you were thinking it had to do with security, you’d be dead right. (I guess the “Security” category kind of gave that away though, right?)
As you may already know, the template models used by Word and Excel are very different. In a nutshell, Word documents inherit some information from their template and maintain a link back to it, whilst Excel workbooks are basically just copies of their templates.
Because Word documents maintain a link to their templates, we were always able to tell what security context to use for documents based off templates — Word would tell us “Hey, the template being used for this document came from such-and-such a location,” and we’d use that to set up the security context. You can see my old blog on trusting document locations for more info on the document’s security context if you haven’t read it already.
But poor old Excel didn’t really have that information handy, and we couldn’t in good faith let you build solutions off Excel templates without knowing how to run them securely. So for the majority of the VSTO product cycle (and probably for the beta, as well) Excel was deliberately designed not run VSTO code for new workbooks created from customised templates, and so we didn’t have a project template (no pun intended) in Visual Studio for building such projects.
Then for some other obscure reasons (I think it was something to do with getting workbooks to load properly when hosted inside IE) Excel magically had the right information hanging around, and lo-and-behold we could now run Excel templates with the correct security context.
Unfortunately, this came too late in the cycle for us to add a new project to the VS toolset — it was a fairly trivial code change to allow Excel to run customised templates, and it was a relatively minor addition for the runtime QA team to add Excel templates to their test cases, but any kind of changes in the project system (especially since they involved UI) were far too expensive and risky to make at such a late stage.
So the long and the short of it is that if you start an Excel Workbook project in VSTO and then use Excel to “Save As” the workbook as a template, you should be able to get it working. At one stage we even had sample code in the help files telling you how to simulate a template using some code in a workbook; I don’t know if that still lives on or if we cut it out in time.
Yes, VSTO 2.0 should have designer support for Excel templates.
Thanks to Siew Moi for prodding me to write this blog