Make your Office add-in version-independent

July 25, 2008 – 4:24 pm by JP

If you are working on code for any Office application, you might need to reference an object library for another Office application. I’ve been working on code for a new add-in for Excel, and needed to write code that would reference the appropriate object library, depending on the version of Office installed on someone’s computer. I want it to be version-independent, so it loads no matter which version of Office (2000-2007) is installed on the end user’s computer.

In other words, if you are writing code for an add-in in Excel 2003, but would like it to work in Office 2000 as well, you need to reference those object libraries (in the correct folder) that correspond to the version of Office you expect your users to be using. If you try to reference the 2003 version of Outlook, you’ll get an error because in Office 2000, the folder name (and filename) is different.

I found a great page that lists all of the object library files for each Office version from 2000-2007. If you are automating Office and need to account for different versions, this page lists the full paths for default installations of Outlook, Word, Excel, Access, and so on.

Here is a link to the KB article:

How to obtain built-in constant values for an Office application

The code sample on that page is for regular VB, but this information can be used to great effect in VBA as well. For example, your add-in could check the version of Excel being used, and depending on the result, install the appropriate Outlook library. Ideally, this would occur when the add-in is installed, or whenever it is loaded/called. Here is a short code snippet to demonstrate how the information in the KB article can be used.


With Application.VBE.ActiveVBProject.References
  Select Case Int(Application.Version)
    Case 9 ' Office 2000
      .AddFromFile "C:\Program Files\Microsoft Office\Office\msoutl9.olb"
    Case 10 ' Office 2002
      .AddFromFile "C:\Program Files\Microsoft Office\Office10\msoutl.olb"
    Case 11 ' Office 2003
      .AddFromFile "C:\Program Files\Microsoft Office\Office11\msoutl.olb"
    Case 12 ' Office 2007
      .AddFromFile "C:\Program Files\Microsoft Office\Office12\msoutl.olb"
  End Select
End With

This code will feature prominently in the add-in I’m working on, because my intention is for it to work in Office 2000-2007. I’ll need to make sure it works in any of those versions if I want it to be used as widely as possible.

I guess if you were really paranoid, or afraid that someone somewhere didn’t use the default installation location for Office (can you even do that?), you could use FileSearch (pre-Excel 2007) or FileSystemObject (all versions) to search for the object libraries and reference them that way, but that would add a lot of overhead to your code.

Enjoy,
JP

If you enjoyed this page, share it!

Print This Post |

Email This Post | rss

Subscribe to Posts Feed | rss

Subscribe to Comments

Filed Under: AddIns, Excel, VBA, automation
Tags: add-in, compatibility, Excel, version

6 Responses to “Make your Office add-in version-independent”:
If you want your code to be locality-independent, you should use Val(Application.Version) instead of Int(Application.Version).

The paths are also dependent on the user not customizing the Office installation path. To be fully robust, you can get the installation path from the registry. The code is something like this, which uses the Windows Script Host to access the registry, and extract the current Excel installation path.

Function RegXLSTART() As String
Dim wsh As New IWshShell_Class
Dim i As Integer
Dim sReg As String

On Error GoTo ErrHandler

For i = 12 To 9 Step -1
‘ find path to latest installed version (12 = 2007, 11 = 2003, 10 = 2002, 9 = 2000)
sReg = “HKLM\SOFTWARE\Microsoft\Office\” & CStr(i) & “.0\Excel\InstallRoot\Path”

On Error Resume Next
RegXLSTART = wsh.RegRead(sReg) & “XLSTART”
On Error GoTo ErrHandler

If Len(RegXLSTART) > 0 Then Exit For
Next i

Set wsh = Nothing

Exit Function

ErrHandler:
Debug.Print “Error ” & Err.Number & “: ” & Err.Description
Resume Next
End Function

By Jon Peltier on Jul 28, 2008
Very interesting Jon, I just had a sobering browse through regedit. I was aware of the registry option, but it’s hard to remember everything at once Outlook is the same except you can only have one version installed at a time, so there’s no version information in the registry path. I used Int because I was dropping the decimal part, but this option looks better.

Thx,
JP

By JP on Jul 28, 2008
hi,
congratulations for this site. Very useful.
Related to this post do you know if there is any way throw VBA code verifiy in excel if the MSXML is installed?

By Arlindo Mieiro on Jul 30, 2008
You could do something like this:

Function bIsMSXMLInstalled() As Boolean
Dim ref As Object

bIsMSXMLInstalled = False

For Each ref In Application.VBE.ActiveVBProject.References
If InStr(ref.Name, “MSXML”) > 0 Then
bIsMSXMLInstalled = True
Exit Function
End If
Next ref
End Function

This function returns “True” if any version of the MSXML type library is installed in your VB Editor.

HTH,
JP

By JP on Jul 30, 2008
JP - Your code doesn’t tell whether a library is installed in Windows, but only whether the active project has a reference set to it. I don’t offhand know how to find out what libraries are available. The way I’ve done it in the past is to attempt to set a reference, and if it is set, then the library is installed. But you need to know the path to the library.

By Jon Peltier on Jul 30, 2008
Jon,

I might have misinterpreted the original question. The only evidence I could find for installation information for MSXML is that it installs in the following folder:

%SystemRoot%\system32\

In which case you could use FileSystemObject to check if a file with that filename exists, before attempting to reference it. Or simply reference it (while trapping the error) like you suggested.

Thx,
JP

By JP on Aug 1, 2008