Use API to edit registry keys to remove Add-In from Add-Ins List

The thread title says it all.

Below are the objectives I would like the solution to meet:

I want to programmatically remove an Add-In from the Add-Ins list (This is not the same as uninstalling an add-in from the list).

I do not want to have to close Excel as part of the solution.

The removal can be achieved by finding & removing the registry key for the Add-In. (I think an .ini file may need to be refreshed as well - seeing as how Excel updates the registry when the Application is closed)

I would prefer the registry editing to be done by API rather than WScript.

This may help http://answers.microsoft.com/en-us/o...4-66a08e4b62ac

This link contains code which almost achieves what I want - except it relies on closing Excel application and I'd prefer to use API rather than WScript.

Leaving aside the API outcome for now, would it be possible to alter the code in post#2 so that Excel doesn't need to be closed to remove the add-in from the list?

Also, can anybody explain where/what Excel writes as it closes? (It writes certain values to the registry. Anything else? I've heard of a .ini changing?)

I found some code on the net to INSTALL an addin using registry keys. So I am trying to convert this code to VBA (and from there to UNINSTALL instead of INSTALL). However I am floundering.

Anyone care to provide a few clues?

Option Explicit

Private Const strTestXLAMname As String = "AnAddinName.xlam"

Private Const REG_SZ = 1    'Constant for a string variable type.
Private Const HKEY_LOCAL_MACHINE = &H80000002
Private Const HKEY_CURRENT_USER = &H80000001
Private Const READ_CONTROL = &H20000

Private Declare Function RegOpenKeyEx Lib "advapi32.dll" _
            Alias "RegOpenKeyExA" ( _
                ByVal hKey As Long, _
                ByVal lpSubKey As String, _
                ByVal ulOptions As Long, _
                ByVal samDesired As Long, _
                phkResult As Long) _
            As Long

Private Declare Function RegQueryValueEx Lib "advapi32.dll" _
    Alias "RegQueryValueExA" ( _
        ByVal hKey As Long, _
        ByVal lpValueName As String, _
        ByVal lpReserved As Long, _
        lpType As Long, _
        lpData As Any, _
        lpcbData As Long) _
    As Long

Private Declare Function RegCloseKey Lib "advapi32.dll" _
       (ByVal hKey As Long) As Long

Function GetOPEN(Param As String) As String
'\ Get the first available OPEN entry name
    Dim i           As Integer
    Dim strValue    As String
    Dim strSubKey   As String

    strSubKey = "Software\Microsoft\Office\" + strOfficeVersion + "\Excel\Options"
    
    If Not RegQueryStringValue(HKEY_CURRENT_USER, strSubKey, "OPEN", strValue) Then GetOPEN = ""
    
    i = 1
    
    Do While RegQueryStringValue(HKEY_CURRENT_USER, strSubKey, "OPEN" + IntToStr(i), strValue) And (InStr(strTestXLAMname, LCase(strValue)) = 0)
        i = i + 1
    Loop

    GetOPEN = "OPEN" + IntToStr(i)
End Function

Private Function strOfficeVersion() As String

    Dim strPathToExcel      As String
    Dim strExcelVersion     As String
    Dim iDotInstr1          As Integer
    Dim i                   As Integer

'' for getting the Office version and the path to Excel like this:
'' C:\Program Files\Microsoft Office\Office10\
'' note that the Office version can be a non-integer number like 9.5
'' -----------------------------------------------------------------


    '??? strPathToExcel = RegQueryStringValue( HKEY_LOCAL_MACHINE,"SOFTWARE\Microsoft\Windows\CurrentVersion\AppPaths\Excel.exe","Path",strPathToExcel)
    strPathToExcel = Application.Path


'\ this will be file version of Excel.exe, something like 10.0.2.26
    '??? GetVersionNumbersString(strPathToExcel & "EXCEL.EXE", strExcelVersion)
Rem Need to create a VBA function equivalent to return the version number from the EXCEL.EXE file
    strExcelVersion = GetVersionNumbersString(strPathToExcel & "EXCEL.EXE")


'\ Extract the registry version, such as 10.0
    '' get the first dot
    iDotInstr1 = InStr(".", strExcelVersion)
    i = iDotInstr1 + 1

    '' to find the second dot
    Do While (strExcelVersion(i) <> ".") And (i <= Length(strExcelVersion))
        i = i + 1
    Next

    '' this will then be something like 10.0
    strOfficeVersion = Copy(strExcelVersion, 1, i - 1)
End Function

Private Function GetAddInSubKey(Param As String) As String
'\ Return the Subkey where the add-in should be added
    GetAddInSubKey = "Software\Microsoft\Office\" + strOfficeVersion + "\Excel\Add-in Manager"
End Function

Private Function GetOptionsSubKey(Param As String) As String
'\ Return the Options Subkey
    GetOptionsSubKey = "Software\Microsoft\Office\" + strOfficeVersion + "\Excel\Options"
End Function

Private Function RegQueryStringValue(ByVal hKey As Long, ByVal strValueName As String) As String

    Dim lResult         As Long
    Dim lValueType      As Long
    Dim strBuf          As String
    Dim lDataBufSize    As Long

    'retrieve nformation about the key
    lResult = RegQueryValueEx(hKey, strValueName, 0, lValueType, ByVal 0, lDataBufSize)

    If lResult = 0 Then
        If lValueType = REG_SZ Then
            'Create a buffer
            strBuf = String(lDataBufSize, Chr$(0))
            'retrieve the key's content
            lResult = RegQueryValueEx(hKey, strValueName, 0, 0, ByVal strBuf, lDataBufSize)
            If lResult = 0 Then
                'Remove the unnecessary chr$(0)'s
                RegQueryStringValue = Left$(strBuf, InStr(1, strBuf, Chr$(0)) - 1)
            End If
        End If
    End If
End Function

Current version below. I am floundering badly. Anyone skilled with using VBA/API to edit the Registry and/or can assist with what Excel does before closing?

Option Explicit

Private Const strTestXLAMname As String = "AnAddinName.xlam"

Private Const REG_SZ = 1    'Constant for a string variable type.
Private Const HKEY_LOCAL_MACHINE = &H80000002
Private Const HKEY_CURRENT_USER = &H80000001
Private Const READ_CONTROL = &H20000

Private Declare Function RegQueryValueEx Lib "advapi32.dll" _
    Alias "RegQueryValueExA" ( _
        ByVal hKey As Long, _
        ByVal lpValueName As String, _
        ByVal lpReserved As Long, _
        lpType As Long, _
        lpData As Any, _
        lpcbData As Long) _
    As Long

Rem Not Required?
Private Declare Function RegCloseKey Lib "advapi32.dll" _
       (ByVal hKey As Long) As Long

Rem Not Required?
Private Declare Function RegOpenKeyEx Lib "advapi32.dll" _
            Alias "RegOpenKeyExA" ( _
                ByVal hKey As Long, _
                ByVal lpSubKey As String, _
                ByVal ulOptions As Long, _
                ByVal samDesired As Long, _
                phkResult As Long) _
            As Long

Function GetOPEN(Param As String) As String
'\ Get the first available OPEN entry name
    Dim i           As Integer
    Dim strValue    As String
    Dim strSubKey   As String

    strSubKey = "Software\Microsoft\Office\" + Application.Version + "\Excel\Options"
    
    If Not RegQueryStringValue(HKEY_CURRENT_USER, strSubKey, "OPEN", strValue) Then GetOPEN = ""
    
    i = 1
    
    Do While RegQueryStringValue(HKEY_CURRENT_USER, strSubKey, "OPEN" + IntToStr(i), strValue) And (InStr(strTestXLAMname, LCase(strValue)) = 0)
        i = i + 1
    Loop

    GetOPEN = "OPEN" + IntToStr(i)
End Function

Private Function RegQueryStringValue(ByVal hKey As Long, ByVal strValueName As String) As String

    Dim lResult         As Long
    Dim lValueType      As Long
    Dim strBuf          As String
    Dim lDataBufSize    As Long

    'retrieve nformation about the key
    lResult = RegQueryValueEx(hKey, strValueName, 0, lValueType, ByVal 0, lDataBufSize)

    If lResult = 0 Then
        If lValueType = REG_SZ Then
            'Create a buffer
            strBuf = String(lDataBufSize, Chr$(0))
            'retrieve the key's content
            lResult = RegQueryValueEx(hKey, strValueName, 0, 0, ByVal strBuf, lDataBufSize)
            If lResult = 0 Then
                'Remove the unnecessary chr$(0)'s
                RegQueryStringValue = Left$(strBuf, InStr(1, strBuf, Chr$(0)) - 1)
            End If
        End If
    End If
End Function

Rem Not Required. Keep as may need later on
Private Function GetAddInSubKey() As String
'\ Return the Subkey where the add-in should be added
    GetAddInSubKey = "Software\Microsoft\Office\" + Application.Version + "\Excel\Add-in Manager"
End Function

Does anyone know of a webpage covering indepth what Excel does when it closes? (i.e. where it writes to/saves its settings, what registry keys, what .ini files if any)

If so, please post a URL! If the page is useful, I'll rep you!

The add-in names appear in the registry here: HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options (This location will vary depending on your Office version)

I need a way to loop through the key to find a matching value.