Creating and Deploying Managed COM Add-ins with VSTO 2005 SE – Part I

This blogpost, together with some upcoming blogposts, will end the series “The Transition Case” by discussing and viewing the Visual Studio 2005 Tools for Microsoft Office System Second Edition (VSTO 2005 SE) based solution. 

Since I have decided to set focus on VSTO & Excel during 2007 this blogpost is also a start for the series “Creating and Deploying Managed COM add-ins. However, it will not prevent me from continue to discuss .NET & Excel from a broader perspective. I will also later cover how to create and use classic commandbars in Excel 2003 with VSTO 2005 SE solution.  

Before “diving into the world of VSTO” I would like to remind that my recommendation VSTO 2005 SE is still valid mainly due to the present deployment issues. However, I sincerely hope that when version 3.0 of VSTO is shipped these issues have also been solved in a developer friendly way.

#Introduction 
VSTO 2005 SE was released in November 2006 and it provide us with the possibility to create application level add-ins, i e managed COM add-ins, for Excel 2003 and Excel 2007.

The following requirements must be met in order to create and distribute VSTO based add-ins:

• Visual Studio 2005 Professional or higher or
• Visual Studio 2005 Tools for Microsoft Office System (VSTO 2005) and
• Visual Studio 2005 Tools for Microsoft Office System Second Edition (VSTO 2005 SE) and
• .NET Framework 2.0 and later (also on the targeting computers)
• VSTO 2005 SE runtime (on the targeting computers)
• Excel 2003 (certain versions only) and Excel 2007 (all versions)

Special note:
Microsoft strongly recommend to not install VSTO 2005 SE on any computer that have more than one version of Microsoft Office installed.

#Registry entries
One important aspect is that we don’t need to shim the add-ins like we need to do with  Shared Add-ins (developed with VB.NET) since VSTO use an add-in loader file.

The following (Swedish) screenshot shows the entry in the HKEY_CLASSES_ROOT section and under the InprocServer32 subkey:

Compared with managed/unmanaged COM add-ins the registry entries in the HKEY_CURRENT_USER is the same except that VSTO managed COM add-ins also got a Manifest entry (that points to the Manifest.DLL file for the Add-in) as the following (Swedish) screenshot shows:

 # The Project
The project involved is the same as for the previously Transition case although it does not cover subclassing and reading/writing to the Windows registry.

The following screenshots shows the Solution Explorer for the project:

As we can see it contain a great number of Detected Dependencies.

#The Created skeleton
When creating the VSTO project with the add-in template for Excel 2007 it creates a class module – ThisAddin.vb – which contain only two procedures: 

In my opinion this is a welcome improvement as this two events are the ones we usually had used in unmanaged COM Add-ins.  The ExcelLocale1033Proxy class is interesting as it provides two methods we can use to modify the locale ID for individual Excel objects, i e wrap for locale ID 1033 (English United States) and unwrap for another Locale ID, for instance Swedish. An even better approach is to use ExcelLocale1033Attribute which allow us to pass all the native Excel objects locale ID and in that way consider the local regional settings (which was also pointed out as a shortcoming in previously version by Bullen et al in their book “Professional Excel Development”).

In the next blogpost I will discuss more in detail the project itself.

Kind regards,
Dennis

Using IRibbonExtensibility’s type library with COM Add-ins

In this second post about IRibbonExtensibility I will show how we can use the library with an unmanaged COM Add-in developed in classic VB. To read the first post on the subject please see:
Creating a standalone type library for IRibbonExtensibility

#1 Make a reference to the library
The filename of the attached library in the previously blogpost is XLRibbonExtensibility.tlb but its library name is XLRibbonExtensibility Object Library (which is viewed in the helpstring section on top of the tlb file) . The later is showed in the Reference dialog in classic VB which we should set a reference to in the VB Project.

#2 Implement the library
On top of the classmodule Connection we add the following expression:

Implements IRibbonExtensibility

#3 Create the necessary procedures
Before we compile the DLL we need to add some procedures in the Connection class. These procedure are available here.

#4 Creating the configuration XML file for the Ribbon in Excel 2007
The XML file that is used here is included in the attached file RibbonX.zip (see below).

#5 Do we actually need to ship the XLRibbon.tlb file with the solution?
No – We only need it during the developing process.
The implements statement as above and all procedures that is targeting the IRibbonExetensibility are disregarded when installed on a targeting computer, for instance with Excel 2000 installed.

However, if You get some issues due to the use of this approach You should include the TLB file in the setup package.

Despite the above I still want to know if it legal or not to extract the IRibbonExtensibility information from the MSO.DLL file.

If You want to take part of the created DLL You can download the RibbonX.zip file, which includes the compiled DLL, XML file and the Helpfile. You need to registrate the DLL in order to test it. Please note that the DLL is just a test file.

I would like to take this opportunity to explicit thank John West for his kindness to introduce me to the approach and how we can solved it in a rather easily way.

In my next blogpost I will finally port myself officially to the VSTO 2.5 SE with the Transition case.

Kind regards,
Dennis

Creating a standalone type library for IRibbonExtensibility

Introduction
Suppose we want to create unmanaged COM Add-ins with classic VB that should target version 2000 to 2007 of Excel.

One of the first questions we need to answer is whether we should use early or late binding. In my opinion we should, as long as it is feasible, use early binding which of course means in this case that we need to add a reference to the Excel 2000 object library.

Anyway, the most important question is how we can control and manipulate the RibbonX when the COM Add-ins are used with Excel 2007. In more technical terms the question is how to implement the interface for the IRibbonExtensibility in COM Add-ins enabling it to work with both Excel 2007 as well as with previously versions of Excel without causing any additional issues.

One approach would be to include the Office 12 type library file within the project. But this scenario is not doable as the EULA does not permit us to use that file under these circumstances (at least that’s my understanding). Except for that the file size is around 16.5 MB…

A better approach is to copy the part that include the interface for the IRibbonExtensibility from the above file and create a new standalone type library file. It would then only include the necessary technical information to implement the interface in COM Add-ins.

The second blogpost about the subject is available here:
Using IRibbonExtensibility’s type library with COM Add-ins

The question
Before moving on I would like to highlight the following question:

Does the EULA for Microsoft Office 12 allow us to extract information from the MSO.DLL file in order to create standalone type libraries that are used with COM Add-ins?

If the EULA does not allow us then the remaining part of this blog will immediately be removed as soon as the information is received by me from Microsoft. It will then be replaced with the answer for the question.

Creating a standalone type library for IRibbonExtensibility
The following discuss how to retrieve the necessary data from the MSO.DLL file, create the Interface Definition Language (IDL) file, generate the UUID GUID (where GUID is  MSFT’s implementation of the UUID standard) and finally how to create the Type Library file (TLB).

# Extract the information about the interface of the IRibbonExtensibility
In order to grab the wanted information we need to have access to the file MSO.DLL which in general should be available in the following location:
C:\Program Files\Common Files\Microsoft Shared\OFFICE12\

Next, we need the utility OLE/COM Object Viewer to view the file (the utility is shipped with both the Microsoft Visual Studio 6.0 as well as Visual Studio.NET 2005).

Click on the “View TypeLib” button in the OLE Viewer and open the above file. The whole content can be copied from the OLE Viewer into a text editor (that can show row numbers). The wanted information is available from line 9580 (where “interface IRibbonUI” is located) and ends at line 9640 (where “} MsoTextDirection;” is located).  Save the file under a desirable filename.

# Create the UUID
In order to create a unique UUID (GUID) we need to use the utility GUIDGen.exe. It’s shipped with various Microsoft’s softwares including Visual Studio 6.0 and Visual Studio.NET.

The generated UUID should be added on top in the textfile (see the attached sample file xlRibbon.idl).

Make sure that the textfile is saved and then in the Explorer You can easily change the file extension to IDL.

# Create the TLB file
One of the more common tools to use for this task is the Microsoft Interface Definition Language (MIDL) command line tool. The following command creates the wanted TLB file:

midl /tlb <fullpath-filename.tlb> <fullpath-filename.idl>

# The sample files
The compiled files attached to this blogpost are made available “as is”.
The files xlRibbon.idl and xlRibbon.tlb are packed into one single zip: xlRibbon.zip

# Final comments
I had some major difficulties before I got the MIDL to create the wanted file. Be prepared to spend some time to configure the computer in order to get the MIDL to work properly. On the other hand, the attached files can be used without the need of additional work with them.

By creating a standalone type library for the IRibbonExtensibility interface and adding a reference to the library in COM Add-ins we get a poweful solution that actually can target all the Excel versions between 2000 to 2007. Of course, the compiled files can also be used with the other softwares in the Office suite. I therefore hope that the presented approach don’t violate the terms of the EULA.

The above should be considered as an introduction. Use the above keywords to search for additional information about the utilities that are used here.

In my next blogpost I will discuss (at least, I hope so) how to leverage the created typed library with unmanaged COM Add-ins.

Kind regards,
Dennis

Transfer Excel ’07 data to SQL Server 2005 EE with ADO.NET

I have decided to place all code in articles at my English site and refer to them in the blogpost instead of trying to ‘formatting’ the code here. 

An update:
My good local friend Stefan Holmberg had the kindness to give me some additional input about ADO.NET 2.0. In return I have updated the sample in use where the method UpdateBatchSize of the SQLDataAdapter allow us to add all records in a batch mode to the database. 

This post is about how we can:

• Retrieve data from closed workbooks (Excel 2007) with a new Provider and
• Update a SQL Server 2005 EE (Express Edition) database with the retrieved data. 

With the release of Office 2007 the Jet OLEDB Provider has been replaced with a new Provider. The new Provider’s name is “Microsoft ACE OLEDB 12.0” and there is the following good news with it:

• The 255 charachter limitation is removed.
• It reads both Excel 8 ‘source databases’ (version 2000/2002 (XP) and 2003) and Excel 12 (version 2007) ‘source databases’. It means that the provider can read workbooks in the following file formats XLS, XLSX, XLSM and XLSB.

The provider works with both classic ADO and with ADO.NET. Since this blog is about .NET and Excel I decided to create an example (available on my English site) where we use ADO.NET and with untyped Datasets:

Transfer Excel ’07 data to SQL Server 2005 EE with ADO.NET

The use of Datasets can be discussed but here I find it to be a rather smooth solution, especially when using ‘local’ dataset where the data from it is merged into a ‘public’ dataset.

At present I’ve not yet find any sources that explicit discuss the provider and Excel workbooks as ‘database sources’.  Please let me know if You know of any source related to the subject.

Kind regards,
Dennis