Problem: COM Automation across multiple COM versions
Microsoft Visual Studio .NET makes linking to COM components extremely easy. You simply right click "References" in the Project Explorer, and Add New Reference. You can then browse through the available .NET Assemblies and COM components to find the code library you wish to automate. Every COM interface available on the machine is described by a Type Library (commonly held in a TLB file, and referenced by a CLASSID in the Registry). When you add this reference to your project, the type library is wrapped with a .NET Interop assembly that allows you to use the COM interface at design-time, and then handles the calls to COM at run-time. When type libraries exist with multiple versions, this can complicate your life significantly.
Case: Microsoft Excel
The Add Reference dialog shows Excel 11.0 Object Library when Excel 2003 installed
As an example, we will look at Microsoft Excel. Depending on which version of MS Excel is loaded on the machine on which your project is compiled, you will get a different Interop created. If the project is then deployed to a machine with a different version of Excel, you can expect to run into problems. Microsoft has kept Office backward compatible for a few generations so you can expect most previous functionality to be available still in Office 2003 (aka 11.0). In a Production environment that spans multiple versions of office, however, you may still run into errors, because the structure of type libraries and how they are linked in the registry has changed. The PIA (Primary Interop Assembly) concept further complicates .NET deployment where backward compatibility is concerned.
[Visual Basic]
Sub FillRange(ByRef rng As Excel.Range, ByVal iLength As Integer, ByVal iWidth As Integer)
Dim saRet(iLength, iWidth) As Object
' ... fill array with values
rng = rng.Resize(iLength, iWidth)
rng.Value = saRet
End Sub
|
VB code assigns a 2-dim String array to the Value property of an Excel.Range object[System.Runtime.InteropServices.COMException] -2147352559 (80020011) Does not support a collection.The above code produces this error when compiled on an Excel 2003 machine and run on Excel 2000
Generally, you can only install a single version of Office on a particular PC; various CLASSIDs that represent objects in the application are thus "installed", or "registered" to trigger this version. When a subsequent version is installed on the machine, previous versions are overwritten. Hence, Visual Studio will always only find one version to which it can link. In the case of PIAs, VS.NET will find a copy with a strong name, in the GAC, and hence will not even make an Interop assembly. Surprisingly, even if you explicitly link to a particular Type Library, VS.NET will override your reference and use the registered version.
Solution
You will find three distinct methods suggested to work around these problems:
- Build your project on different machines for compatibility with each respective version of Office.
- Install different versions of Office on your build machine (How-to).
- Use "Late Binding" to call whatever version of Office is available at run-time
Here, I suggest a fourth method: build to the lowest common denominator.
| NOTE: Although this strategy was suggested by MS Support in Writing Automation Clients for Multiple Office Versions, it does not work with VS.NET 2003/5 and Excel 2003. VS.NET now updates all Interop references to match ActiveX libraries installed in the Registry--any manually created reference to an unregistered version is overwritten at compile time (except by the steps outlined below). MS Support now recommends one of the above 3 approaches. In fact, when I suggested this as a feature for the VS 2005 Beta version, it was deemed too complicated and excluded "By Design". The steps outlined below are surprisingly simple. |
Find the lowest version with which the project will need to work in the Production environment and build to this. For advanced functionality only available on later versions this method can be extended--providing multiple Interop assemblies that will work in parallel. Additionally, this does not rely on having a particular version of the type library installed on the build machine--nor any version at all, for that matter. It does, however, require that you have access to the Type Library files in the version against which your project will build.
Step-By-Step
In order to build my project against Excel 2000, on a machine that has only Excel 2003 installed, I performed the following steps:
- Locate the Excel 9 (2000) Type Library
- Normally, type libraries are named *.TLB
- After a little googling, however, I found that the Excel 9 type library is called EXCEL9.OLB
- I quickly found the EXCEL.OLB file on my (fully licensed and registered!!!) Office 2000 installation CD.
- Copy the Excel 9 Type Library to the build machine
- Create an Interop Assembly from the Excel9 Type Library
- Choose a unique name that will distinguish YOUR Interop DLL from others that may be found
- Use the .NET SDK Tool called TLBIMP.exe to build your Interop Assembly from the command line
@echo off
SET prog="C:\Program Files\Microsoft Visual Studio .NET 2003\SDK\v1.1\Bin\TlbImp.exe"
%prog% EXCEL9.OLB /out=My.Excel9.Interop.dll
pause
|
DOS Batch commands to create the explicit Interop DLL using the TlbImp tool provided with the .NET SDK
- Link this new Interop Assembly to the VS.NET project, and build
- Set the "Private" (aka "CopyLocal") property to True
Browse... to the new Interop DLL in the Add Reference dialog
- Update code to reference the new Interop Library
[Visual Basic]
Sub FillRange(ByRef rng As My.Excel9.Interop.Range, ByVal iLength As Integer, ByVal iWidth As Integer)
|
- Deploy this Interop Assembly in the \bin directory along with the project
- Your project will now work on any machine with Office 2000 or higher
References
.NET Framework Tools : Type Library Importer (Tlbimp.exe)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cptools/html/cpgrftypelibraryimportertlbimpexe.asp
INFO: Writing Automation Clients for Multiple Office Versions
http://support.microsoft.com/kb/q244167
Working with the Office XP Primary Interop Assemblies
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoxpta/html/odc_oxppias.asp
Office XP Primary Interop Assemblies Known Issues
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoxpta/html/odc_piaissues.asp
Official Office 2003 Interop PIAs
http://support.microsoft.com/?scid=kb;en-us;897646&spid=2525&sid=global
INFO: Develop Microsoft Office solutions with Visual Studio .NET
http://support.microsoft.com/kb/q311452/