Peter,

Many thanks for your thoughts. Your final line "do this while no other
workbooks are open referencing the addin" is an issue because I would like to
contain this issue witin excel. For now I will go with Bill's code.

--
Trefor


"Peter T" wrote:

Trefor, from what you describe below I'd keep it simple.

Assuming name + title pair are same in both addins, and the old addin is
loaded and installed as an addin -

unload the old addin as if a normal workbook (don't uninstall it),
move it to a different folder or Kill it,
copy the new addin into the original folder, eg Userlibrarypath,
load it as a normal workbook (no need to install as an addin).

As far as the addins collection is concerned nothing has changed.

do this while no other workbooks are open referencing the addin

Regards,
Peter T

"Trefor" wrote in message
...
Bill/Peter,

Thankyou for you replies I have not had a chance to go through all the
code
yet, but I thought I would ask another question first. Am I going about
this
the right way in the first place?

What I have is "Main.xls" which checks the network for a newer
"Macro.xla",
if one exists it copies the "Macro.xla" to Application.UserLibraryPath and
and then tries to use the later version.

Should I do this differently? "Macro.xla" has the same name, but I could
easily give each updated addin a new name, but then I notice I start
collecting heaps of addin in the list and in the directory.

I don't want the user to have to do this manually, this all has to work
like
magic behind the scenes.

--
Trefor


"Peter T" wrote:

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