Office Development–The Bad

clip_image002

Visual Studio Tools for Office (VSTO) is a set of development tools available in the form of a Visual Studio add-in (project templates) and a runtime that allows Microsoft Office 2003 and later versions of Office applications to host the .NET Framework Common Language Runtime (CLR) to expose their functionality via .NET.

Quote: https://en.wikipedia.org/wiki/Visual_Studio_Tools_for_Office

As we have seen in my previous blog post Office Development – The Ugly the first option to build your Office extensions using Visual Basic for Applications (VBA) did not win the beauty contest. A very powerful toolset to create beautiful and especially productive customizations. I’m sure that VBA, the tools to set Office to your hand, allowing you to tailor Office in a way that it perfectly fits the needs of your company, is one of the main reasons that made Office such a popular box of applications.

It was however risky – a potential security risk and code management is a pain. In the real world it happened that a ‘new’ version was rolled into production where all the sudden existing features ‘disappeared’ as a result of the developer picking the ‘wrong file’ containing an older version of the solution and continued developing features using the incomplete version.

This needed to change … in the meantime managed code -.NET- appeared at the horizon and the next option at least had to be a) secure and b) needed an improved source control option.

The solution Microsoft put on the table were the:

Microsoft Visual Studio Tools for the Microsoft Office System.

The product name was most likely the longest name available at that time. The Visual Studio Tools for the Microsoft Office System (VSTO) was architected as a bridge between Visual Studio and The Microsoft Office System. On one side the languages team with Visual Studio, on the other side the Office team and in the middle the VSTO team – code named Trinity …

clip_image004

So how is VSTO different compared to VBA?

Applications, add-ins or document customizations, are built using Visual Studio. Initially in a separate Visual Studio .NET 2003 VSTO SKU and after a few version inserted in the Visual Studio 2008 box. The code no longer lived in the documents or other Office files, but from now on lived in an external assembly that would be triggered by the Office host application if the right properties were available in the document.

Loading the assembly was done in a relative complex manner and it was made sure that security wise the pain seen with VBA would not exist with VSTO add-ins.

Here is a schematic overview of the load pattern:

clip_image005

As you can see the Office Application is looking at the Registry to see what add-ins are installed, if found any the Deployment manifest (pointed to by the Registry) is read and following that the Application manifest is read and the assembly loaded.

A real extension to the Office Application … to make this all secure the security is enforced at installation time. There was a whole slew of prereqs that must be covered before the add-in would be installed:

clip_image006

This part made VSTO the “Bad” in The Good, The Bad and The Ugly … it appeared to be a real pain for the IT Pros at the time. By default, VSTO used Click-Once to install the extension on the machine and a lot went wrong … certificates expired, not added to the Trusted Publisher lists, etc. etc. There was another option, to install an add-in using an .msi installer but the average VBA developer had a hard time getting around all of this, jumping all the hoops to make it work.

Was it all Bad?

Not at all! By using managed code, C# or VB.NET – whatever your preference was, your world of Office completely opened. Whatever you could do with .NET (and that is basically everything) you could add to your solution. Calling third party libraries, UI components, later even XAML UI interfaces, sky is the limit.

For me personally this is still my fav option to build Office customizations. I wasn’t awarded nine-year Visual Studio Tools for the Microsoft Office System MVP – I guess the longest MVP title as well, without reason. VSTO controlled my life for the large part of these years (hopefully my wife is not reading this ) did a lot of forum support in this area and built VSTO applications for large enterprise companies. At the time, we still had on Microsoft campus Software Design Review meetings to share feedback and we gave the team a hard time to make sure they released the features that we really needed.

Where to go next

But … the world changed, Windows is no longer the only platform that needs to be supported. Because of that a new mechanism was proposed. By using the common web standards and JavaScript a platform independent system was developed by providing OfficeJS APIs to access the Office object model from web based add-ins.

And this is where we will dive in soon. It’s a relative new world, although development already started years ago, so there is a lot to learn there. Let’s see if we can do the same with OfficeJS as we can do with VSTO – but now on multiple platforms and several clients.

Maybe I will return to VBA and VSTO in separate blog posts just for fun. There is just a ton of information to share on all off these areas.

Book

Oh, and if you really want to know the ins and outs of VSTO I really recommend you reading the VSTO bibles by Eric Carter and Eric Lippert: Visual Studio Tools for Office 2007: VSTO for Excel, Word and Outlook. It is a huge and heavy piece of reference with over a thousand pages of inside information.

Office templates not available in VS2012 with ‘converted’ project.

Some time ago I hit a somewhat strange issue where I opened one of our existing projects targeting Office 2010, .NET 4 Client in Visual Studio 2010 and moved this project forward to Visual Studio 2012. After the migration I did not see the Office templates in the Add New Item dialog in Visual Studio 2012.

When adding a ‘new item’ to the project it did not display the Office options to insert a Ribbon for instance:

OfficeTemplatesMissing1

When you create a -new- project, the Office Templates show up without issues:

OfficeTemplatesMissing2

What we see here is a minor issue in the migration code in Visual Studio 2012 and is relatively easy to solve by manually editing the .csproj project file.

What you need to do here to fix this minor issue is to go and look for the entry in the project file called AddItemTemplatesGuid. The migration code changed the AddItemTemplatesGuid property from

AddItemTemplatesGuid="{51063C3A-E220-4D12-8922-BDA915ACD783}”
TO
AddItemTemplatesGuid="{147FB6A7-F239-4523-AE65-B6A4E49B361F}”

Changing it back to the original value makes the Add Item dialog start showing Office category correctly again. So the right Guid should be: "{51063C3A-E220-4D12-8922-BDA915ACD783}”

You can easily check this by creating a small new AddIn project, open the project .csproj file in your editor and search for the AddItemTemplates Guid again. You will see that it is the “{51….” one as specified above. Even better, if you go back into your source control software (TFS or other source control option) environment and look at the value in your original file before migration.

So if you also encounter the fact that you don’t get the Office Templates when adding a New Item in your Office project, dive right in to your .csproj file and change the AddItemTemplatesGuid back to its original value the one that was there before migrating your code.

An update rollup is available for Visual Studio 2010 Tools for Office Runtime 4.0

Today a rollup update for Visual Studio 2010 for Office Runtime 4.0 that was auto-magically released November 13th has been deployed to (at least one of) my machines.

This rollup fixes about four issues with the Visual Studio 2010 Tools for Office Runtime and one fix in particular of a bug that I found and posted on Connect:

http://connect.microsoft.com/VisualStudio/feedback/details/705610/cached-data-storage-fails-in-protected-documents

We worked close with the VSTO development team to identify and isolate the issue and they were able to solve it for us to allow us to continue working with the runtime.

The issue isn’t touched a lot beside our organization (it is a specific user scenario not used by many others) so distribution of the fix was ‘delayed’ until deployment of yesterdays rollup (a hotfix could be requested for in case you ran into it).

So as of yesterday the fix became available for everyone along with a couple of other fixes. Again, probably not many of you will notice any differences but I’m sure the users using one or more of the fixed features will be pleased to see it released in the main branch of the product.

If you want to download the VSTO Runtime on your own, not waiting for Microsoft Update or WSUS you can download it at the Microsoft Download Center.

On a side note, also Office 2013 tooling was released this week to enable you to build VSTO solutions targeting Office 2013 but I will spend some time on that in a separate blog post.