Thanks Bill for sharing this. I agree with your use of continuations, hope
everyone can un-wrap OK

Indeed this removes the entries, comments -

This key -
Private Const c_strKey_8a As String =
"Software\Microsoft\Office\8.0\Excel\Options\"

should be changed to -
Private Const c_strKey_8a As String =
"Software\Microsoft\Office\8.0\Excel\Microsoft Excel\"

Concerning removing entries from *\Excel\Options\ (& *Excel\Microsoft
Excel\ for xl97) -

The installed addins are in this section, listed under Name : Data. The Name
is OPEN, OPEN1, OPEN2 etc, data is the addin's fullname.

I don't know why but sometimes Excel will automatically increment down any
OPENx to the missing OPENx, eg delete OPEN1 then next time Excel opens OPEN2
will be renamed to OPEN1. But sometimes that doesn't happen, perhaps could
include KeepItCool's example to reorder the OPEN's

If deleting an installed addin while a given version of Excel is open, it
will be re-written when Excel closes.

Thanks again for posting,
Peter T

"Bill Pfister" wrote in message
...
As a personal preference, I limit the use of line continuations. Let's
see
how this looks. I'm assuming you can just copy paste from here without
unwanted characters being added...

Here's the example (which belongs in a separate module):

Option Explicit

' Registry value type definitions
Private Const REG_NONE As Long = 0
Private Const REG_SZ As Long = 1
Private Const REG_EXPAND_SZ As Long = 2
Private Const REG_BINARY As Long = 3
Private Const REG_DWORD As Long = 4
Private Const REG_LINK As Long = 6
Private Const REG_MULTI_SZ As Long = 7
Private Const REG_RESOURCE_LIST As Long = 8

Public Const REG_OPTION_NON_VOLATILE = 0

Private Const KEY_ALL_ACCESS As Long = &H3F

' Registry section definitions
Public Const HKEY_CURRENT_USER As Long = &H80000001

Private Const c_strKey_8a As String =
"Software\Microsoft\Office\8.0\Excel\Options\"
Private Const c_strKey_8b As String =
"Software\Microsoft\Office\8.0\Excel\Add-in Manager\"
Private Const c_strKey_9a As String =
"Software\Microsoft\Office\9.0\Excel\Options\"
Private Const c_strKey_9b As String =
"Software\Microsoft\Office\9.0\Excel\Add-in Manager\"
Private Const c_strKey_10a As String =
"Software\Microsoft\Office\10.0\Excel\Options\ "
Private Const c_strKey_10b As String =
"Software\Microsoft\Office\10.0\Excel\Add-in Manager\"
Private Const c_strKey_11a As String =
"Software\Microsoft\Office\11.0\Excel\Options\ "
Private Const c_strKey_11b As String =
"Software\Microsoft\Office\11.0\Excel\Add-in Manager\"


' Registry API functions
Private Declare Function RegCreateKeyEx Lib "advapi32.dll" Alias
"RegCreateKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, ByVal
Reserved As Long, ByVal lpClass As String, ByVal dwOptions As Long, ByVal
samDesired As Long, ByVal lpSecurityAttributes As Long, phkResult As Long,
lpdwDisposition As Long) As Long
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 RegSetValueExString Lib "advapi32.dll" Alias
"RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal
Reserved As Long, ByVal dwType As Long, ByVal lpValue As String, ByVal
cbData
As Long) As Long
Private Declare Function RegSetValueExLong Lib "advapi32.dll" Alias
"RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal
Reserved As Long, ByVal dwType As Long, lpValue As Long, ByVal cbData As
Long) As Long
Private Declare Function RegOpenKey Lib "advapi32.dll" Alias "RegOpenKeyA"
(ByVal hKey As Long, ByVal lpSubKey As String, phkResult As Long) As Long
Private Declare Function RegEnumValue Lib "advapi32.dll" Alias
"RegEnumValueA" (ByVal hKey As Long, ByVal dwIndex As Long, ByVal
lpValueName
As String, lpcbValueName As Long, ByVal lpReserved As Long, lpType As
Long,
ByVal lpData As String, lpcbData As Long) As Long
Private Declare Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As
Long)
As Long
Private Declare Function RegDeleteValue Lib "advapi32.dll" Alias
"RegDeleteValueA" (ByVal hKey As Long, ByVal lpValueName As String) As
Long




' Delete all registry keys that contain the named add-in
Public Function GetAllValues(strAddInName As String) As Single
Dim varTemp As Variant
Dim strKey As String
Dim strValue As String
Dim strValueData As String
Dim strSection As String
Dim strDelVal As String
Dim i As Long
Dim j As Long
Dim sngErrorCode As Single

On Error GoTo ErrHandler

For i = 1 To 8
Select Case i
Case Is = 1: strSection = c_strKey_8a
Case Is = 2: strSection = c_strKey_8b
Case Is = 3: strSection = c_strKey_9a
Case Is = 4: strSection = c_strKey_9b
Case Is = 5: strSection = c_strKey_10a
Case Is = 6: strSection = c_strKey_10b
Case Is = 7: strSection = c_strKey_11a
Case Is = 8: strSection = c_strKey_11b
End Select

j = 0

sngErrorCode = 10 ' Search through specified registry key

'Searches through the add-in key and locates the Map add-in if it
exists, the value is then deleted
Do While Not (strValue = "Not Found")
varTemp = ReadRegistryGetAll(HKEY_CURRENT_USER, strSection, j)
j = j + 1

sngErrorCode = 20 ' Find which values are strings
If varTemp(0) = REG_SZ Then
strValue = varTemp(1)
strValueData = varTemp(2)

sngErrorCode = 30 ' Check if add-in is in the specified
strings
If (InStr(strValue, strAddInName) 0) Or
(InStr(strValueData, strAddInName) 0) Then
strDelVal = DeleteValue(HKEY_CURRENT_USER, strSection,
strValue)
End If
End If

sngErrorCode = 40 ' Check if loop needs to be ended
If varTemp(0) = 0 Then Exit Do
Loop
Next i

sngErrorCode = 0
GetAllValues = sngErrorCode
Exit Function

ErrHandler:
GetAllValues = sngErrorCode
End Function





' Retrieves all the values from anywhere in the Registry under a given
subkey, currently only returns string and double word values
Public Function ReadRegistryGetAll(ByVal lngGroup As Long, ByVal
strSection
As String, lngIndex As Long) As Variant
Dim lngResult As Long
Dim lngKeyValue As Long
Dim lngDataTypeValue As Long
Dim lngValueLength As Long
Dim lngValueNameLength As Long
Dim strValueName As String
Dim strValue As String
Dim dblTemp As Double

On Error Resume Next

lngResult = RegOpenKey(lngGroup, strSection, lngKeyValue)
strValue = Space$(2048)
strValueName = Space$(2048)
lngValueLength = Len(strValue)
lngValueNameLength = Len(strValueName)
lngResult = RegEnumValue(lngKeyValue, lngIndex, strValueName,
lngValueNameLength, 0&, lngDataTypeValue, strValue, lngValueLength)

If (lngResult = 0) And (Err.Number = 0) Then
If lngDataTypeValue = REG_DWORD Then
dblTemp = Asc(Mid$(strValue, 1, 1)) + &H100& *
Asc(Mid$(strValue,
2, 1)) + &H10000 * Asc(Mid$(strValue, 3, 1)) + &H1000000 *
CDbl(Asc(Mid$(strValue, 4, 1)))
strValue = Format$(dblTemp, "000")
End If

strValue = Left$(strValue, lngValueLength - 1)
strValueName = Left$(strValueName, lngValueNameLength)
Else
strValue = "Not Found"
End If
lngResult = RegCloseKey(lngKeyValue)

ReadRegistryGetAll = Array(lngDataTypeValue, strValueName, strValue)
End Function



' Deletes a specified key (and all its subkeys and values if on Win95)
from
the registry
Public Function DeleteValue(ByVal lngGroup As Long, ByVal strSection As
String, ByVal strKey As String) As String
Dim lngResult As Long
Dim lngKeyValue As Long

On Error Resume Next

lngResult = RegOpenKey(lngGroup, strSection, lngKeyValue)
lngResult = RegDeleteValue(lngKeyValue, strKey)
lngResult = RegCloseKey(lngKeyValue)
End Function



' Set the data field of a value
Public Function SetKeyValue(lngPredefinedKey As Long, strKeyName As
String,
strValueName As String, varValueSetting As Variant, lngValueType As Long)
Dim lngRetVal As Long ' result of the SetValueEx
function
Dim hKey As Long ' handle of open key


'open the specified key
lngRetVal = RegCreateKeyEx(lngPredefinedKey, strKeyName, 0&,
vbNullString, REG_OPTION_NON_VOLATILE, KEY_ALL_ACCESS, 0&, hKey,
lngRetVal)
lngRetVal = SetValueEx(hKey, strValueName, lngValueType,
varValueSetting)
Call RegCloseKey(hKey)

End Function



' Set the data field of a value
Public Function SetValueEx(ByVal hKey As Long, strValueName As String,
lngType As Long, varValue As Variant) As Long
Dim lngValue As Long
Dim strValue As String

Select Case lngType
Case REG_SZ
strValue = varValue
SetValueEx = RegSetValueExString(hKey, strValueName, 0&,
lngType, strValue, Len(strValue))

Case REG_DWORD
lngValue = varValue
SetValueEx = RegSetValueExLong(hKey, strValueName, 0&,
lngType,
lngValue, 4)

End Select
End Function





"Peter T" wrote:

Unfortunately, I am new to the discussion groups, so I don't know
the best method for sharing code. Any suggestions?

Why not post it here. If long it's helpful if line-wrapping can be
removed
with continuations, though not necessary (check end of line col position
in
the VBE).

You might also be interested to see KeepItCool's method for removing
addins
from the registry. Also addins in default addin folders should also be
moved
elsewhere, or they will persist in the collection even though not
installed
and not in the registry.

http://tinyurl.com/euct6

Regards,
Peter T

"Bill Pfister" wrote in message
...
Trefor, Excel records add-in information in the registry and there is
a
design flaw (a flaw, from this perspective, anyway) that doesn't
update
the
key if the add-in is of the same name. The code must reside outside
of
Excel
/ VBA because anything you do to the registry will be overwritten when
you
exit XL (XL reads the registry when it opens and writes to the
registry
when
it closes). I have handled this by building a small deployment tool,
originally in VB6. The code is somewhat cumbersome but I'd be happy
to
share
if needed. Unfortunately, I am new to the discussion groups, so I
don't
know
the best method for sharing code. Any suggestions?

The essence is that is wipes out any reg strings in the following keys
that
contain the add-in name. You can then re-add properly once the
registry
is
wiped.


Keys of interest:
HKCU\Software\Microsoft\Office\xx.x\Excel\Options\
HKCU\Software\Microsoft\Office\xx.x\Excel\Add-in Manager\
where xx.x is the following 8.0, 9.0, 10.0, 11.0, 12.0

Regards,
Bill


"Trefor" wrote:


I seem to have this really weird problem and I am not sure how to
fix.
Let
me explain via an example:

1. Open a new workbook.
2. Open VBE and create a new module
3. In the module simply type a comment: ' This is file 1
4. Save As Add-In c:\test.xla
5. In the module edit the line to read: ' This is file 2
6. Save As Add-In c:\temp\test.xla <<__ Must be same filename but
different
directory
7. In any module:

With AddIns.Add(Filename:="c:\test.xla")
.Installed = True
End With

Edit the module and confirm the comment : ' This is file 1

With AddIns.Add(Filename:="c:\test.xla")
.Installed = False
End With

With AddIns.Add(Filename:="c:\temp\test.xla")
.Installed = True
End With

8. Edit the module and confirm the comment IT IS STILL: ' This is
file 1
i.e. the original file!
9. Close the file and exit from Excel, then double click on
"c:\temp\test.xla" and I get an error message saying the file is
already
open.
10. Excel Tools Add-Ins and uncheck test and exit excel

With AddIns.Add(Filename:=" c:\temp\test.xla")
.Installed = True
End With

11. Edit the module and confirm the comment IT IS STILL: ' This is
file
1
i.e. the original file!
12. Excel Tools Add-Ins and uncheck test and exit excel
13. From explorer double-click on "c:\temp\test.xla"
14. Edit the module and confirm the comment: ' This is file 2

In summary, once a file has been opened from a particular location,
even
if
you:

With AddIns.Add(Filename:="c:\temp\test.xla")
.Installed = False
End With

Any attempt to load the file from another location using AddIns.Add
simply
opens the file from the original path.

In VBA code how can I "update" to add the Add-In the new location?

--
Trefor