Add-ins – Working with Windows registry

Add-ins – Working with the registry in Windows
In this second post about Add-ins and loading Excel via automation I’ll discuss how we can manipulate the Windows Registry without using Windows API.

To take part of the first part please see the following post:
Add-ins – Where does Windows store the information?

The Regobj.dll is an ActiveX Server that gives us an object model and a structure which reduce the complexity when working with the registry. A Word document is shipped together with the DLL file which gives a general description about the RegObj.dll. It’s available for download at the following place: RegObj

Locate the path to Excel’s main folders
For many applications it’s common to create flexible solutions in that we need to consider the existence of several versions of Excel. In addition, we cannot assume that Excel is always installed in the default location. So when we raise the question how to locate the paths to Excel main folders we are forced to consider these conditions. The goal of this process is to retrieve the names of XLAs/XLLs that are shipped with Excel and their respectively pathways.

I consider the following folders as the main folders for XLAs /XLLs shipped with Excel (in US/English version of Excel):

The below code is created with classic VB and in order to locate the main folder for Excel (based on which version we want to locate) we need to do a two steps approach. First locate the CLSID in the HKEY_CLASSES_ROOT and then retrieve the pathway from the subkey “LocalServer32” in the same section of the registry.

Private Sub ButtonLoad_Click()
Dim stXLversion As String

stXLversion = Me.Combo1.Text

If Locate_XLAs_XLLs_Take_One(stXLversion) = False Then
    MsgBox “The selected version is not available on the computer.”, vbInformation
    Exit Sub
   ‘Do other stuff
End If
End Sub

‘A reference to the Registration Manipulation Classes must be set.
‘A reference to the Microsoft Scripting Runtime must be set.

Function Locate_XLAs_XLLs_Take_One(ByVal stVersion As String) As Boolean
‘Notable: The string variable stVersion refers to “9”, “10”, “11” etc.
Dim stSubRoot As String, stCLSID As String, stExcel As String
Dim stMainPath As String, stLibrary As String, stAnalysis As String
Dim stFoldersArr(0 To 3) As String
Dim stSolver As String, stXLStart As String
Dim objRootKey As RegKey, objStartKey As RegKey
Dim fsoObj As Scripting.FileSystemObject
Dim fsoFolder As Scripting.Folder
Dim fsoFile As Scripting.File
Dim iCounter As Integer

On Error GoTo Error_Handling

‘Instantiate the FileSystemObject.
Set fsoObj = New FileSystemObject

‘Define the subkey.
stSubRoot = “Excel.Application.” & stVersion & “CLSID”

‘Define the rootkey.
Set objRootKey = RegKeyFromHKey(HKEY_CLASSES_ROOT)

‘Parse the subkey.
Set objStartKey = objRootKey.ParseKeyName(stSubRoot)

‘Retrieve the CLSID for the wanted Excel version.
stCLSID = objStartKey.Value

‘Redefine the subkey.
stSubRoot = “CLSID” & stCLSID & “LocalServer32”

‘Parse the subkey
Set objStartKey = objRootKey.ParseKeyName(stSubRoot)

‘Clean up the obtained value.
stExcel = Replace$(objStartKey.Value, “/automation”, “”)

‘Extract the main path in which Excel.exe is located in.
stMainPath = Mid$(stExcel, 1, InStrRev(stExcel, “”, , vbBinaryCompare))

stLibrary = stMainPath & “Library”
stAnalysis = stLibrary & “Analysis”
stSolver = stLibrary & “SOLVER”
stXLStart = stMainPath & “XLSTART”

‘Populate the array with the pathways to the main folders.
stFoldersArr(0) = stLibrary
stFoldersArr(1) = stAnalysis
stFoldersArr(2) = stSolver
stFoldersArr(3) = stXLStart

‘Iterate through the collection of folders and print
‘name and pathway for the located XLAs/XLLs in the immediate window.
For iCounter = 0 To 3
    If fsoObj.FolderExists(stFoldersArr(iCounter)) Then
        Set fsoFolder = fsoObj.GetFolder(stFoldersArr(iCounter))
        For Each fsoFile In fsoFolder.Files
            If LCase$(fsoFile.Name) Like “*xla” Or LCase$(fsoFile.Name) Like “*.xll” Then
                Debug.Print fsoFile.Name & “;” & fsoFile.Path
            End If
        Next fsoFile
    End If
Next iCounter

Locate_XLAs_XLLs_Take_One = True

‘Release objects from memory.
Set fsoFile = Nothing
Set fsoFolder = Nothing
Set fsoObj = Nothing
Set objStartKey = Nothing
Set objRootKey = Nothing
Exit Function

    ‘Error 35006 indicates that the choosen version is not available.
   If Err.Number = 35006 Then Locate_XLAs_XLLs_Take_One = False
    Resume ExitHere
End Function

An alternative approach to search for files in the folders is to identify each add-in’s CLSID and retrieve the pathway in HKEY_CLASSES_ROOT. However, the downside is how we should do to solve it for custom’s add-ins.

Locate the present user’s path
Below is a snippet code to locate the present user’s path and to retrieve the XLAs/XLLs names and pathways in the user’s folder (if any add-ins exists at all):

‘This API call is necessary to get the present username.
Declare Function GetUserName Lib “advapi32.dll” Alias “GetUserNameA” _
                                    (ByVal lpBuffer As String, nSize As Long) As Long
Dim Buffer As String * 100
Dim BuffLen As Long

BuffLen = 100
GetUserName Buffer, BuffLen
stUserName = Left(Buffer, BuffLen – 1)

stUserFolder = “C:Documents and Settings” & stUserName & _
                          “Application DataMicrosoftAddIns”

If fsoObj.FolderExists(stUserFolder) Then
    Set fsoFolder = fsoObj.GetFolder(stUserFolder)
    For Each fsoFile In fsoFolder.Files
       If LCase$(fsoFile.Name) Like “*xla” Or _
          LCase$(fsoFile.Name) Like “*.xll” Then
           Debug.Print fsoFile.Name & “;” & fsoFile.Path
        End If
End If

Change load behavior of COM add-ins
COM Add-ins load behavior can be controlled by either changing the LoadBehavior subkey in the registry setting or by using the Connect property of the COM add-ins as the following snippet code shows:

xlApp.COMAddIns(“ProgID”).Connect = True

However, for COM add-ins that target the VB-editor the load behavior seems to be only controlled via the registry setting as the following sample shows:

Option Explicit
Const stSubKey As String = “SoftwareMicrosoftVBAVBE6.0Addins”

Sub Write_To_Registry()
Dim objRootKey As RegKey
Dim objStartKey As RegKey
Dim objCOMKey As RegKey
Dim objCOM As RegValue

Set objRootKey = RegKeyFromHKey(HKEY_CURRENT_USER)
Set objStartKey = objRootKey.ParseKeyName(stSubKey)
Set objCOMKey = objStartKey.ParseKeyName(“IndenterVBA.Connect”)
Set objCOM = objCOMKey.Values(“LoadBehavior”)

‘Change the load behavior to connected | bootload.
objCOM.Value = 3

Set objCOMKey = Nothing
Set objStartKey = Nothing
Set objRootKey = Nothing

End Sub

RegObj.dll create a less complex code writing then with Windows API however the use of the DLL as well as the use of Microsoft Scripting Runtime can cause an overhead in the application that may or may not be a problem.

For more information about Windows API and additional information about the subject please see the following articles:

How To use the Registry API to Save and Retrieve Setting
How to Determine the Path for an Office Application
How to build an Office 2000 COM add-in in Visual Basic
INFO: Excel COM Add-ins and Automation Add-ins
How to run multiple versions of Excel on the same computer

In the third and final post I’ll present a standalone utility that lets us choose which add-ins to be loaded or not when loading Excel through automation.

Kind regards,

Posted in Uncategorized

7 thoughts on “Add-ins – Working with Windows registry

  1. Ross – Thanks for Your kind comment. The DLL has been around for a while (at least in the VB-world) but apparently not when it comes to Excel.

    Of course, as we all know: “real programmers” use Windows API ;)

    Kind regards,

  2. Dennis Dennis Dennis
    What can I say. When you are on top of your game nobody knows this info like you.
    Very informative.

  3. Very informative, Dennis! I was searching for info when I found your blog. What I’m looking for is similar to this.

    I need to know if a certain application is installed in the client machine before my VBA would work. I’m calling a Web Service Method from excel 2k3 using SOAP and WSDL. The call requires SOAP Toolkit 3.0 to be installed in the client machine before it works. My idea is to search the registry or the file system to find out if SOAP Toolkit 3.0 is installed. If it’s not, I will redirect the user to the site where they can download it.

    The question is, in excel VBA, how do I search the registry or the file system (I’d prefer the registry) without having to reference to a library that may not be in the client machines.

    Maybe a “public declare” statement will do the trick. The thing is, which DLL is needed for the search in the registry?

    Thanks in advance. I do hope you are familiar with this.

  4. Ronald,

    Thanks for Your kind words :)

    Dick Kusleika is the owner of this blog and I’m part of the authoring group.

    Anyway, one easy approach is to check if the file exist or not in the filesystem:

    Const stFile As String = “c:Windowssystem32MSSOAP30.dll”

    If Len(Dir(stFile)) > 0 Then
    ‘The file exist – move along
    ‘The file does not exist – stop
    Debug.Print 1
    End If

    This assume that the client’s machines have the same configuration.

    If this is not the case then check up the KB-article that both me and Jake mention since You do it from inside Excel.

    Kind regards,

Leave a Reply

Your email address will not be published. Required fields are marked *