Trefor, either I didn't follow your earlier objective or you missed my
point, or possibly both!

I thought you want simply to replace xla1 with xla2, where each have the
same name and title (if there is a title), and xla1 is currently loaded as
an installed addin (though not necessarily).

Try this in the Immediate window -

first select your addin in Project explorer

?thisworkbook.FullName [hit enter]
(returns fullname here)
thisworkbook.Close [hit enter]

The addin unloads but it remains an installed addin even though currently
not loaded.
Manually move xla1 out of its current folder and replace with the new
identically named xla2.

Back in the immediate window copy the addin's fullname as returned above and
paste into the following

workbooks.open "addin-fullname" [hit enter]

For completeness you might also want to do -
workbooks("addin-fullname).RunAutoMacros xlAutoOpen [hit enter]]

Assuming name, title, and if relevant addin's project name are identical in
both versions that's all you need to do.

As I mentioned last time no other workbooks should be reference the addin
during this process, ie udf's in formulas or a ref' in toolsreferences
(probably not applicable). But same holds for whichever way you are updating
your addin.

In your real code you'd probably want to start by attempting to set object
references both to the xla as a workbook (is it loaded) and to its identity
in the addins collection (does it exist and if so is it installed). Retain
these settings for possible use after replacing the addin or installing for
the first time.

The registry code posted by Bill here, or by KeepItCool in the earlier link,
is very useful when updated addins' name/title are slightly different and
the folder is not the default addin folder, replacing in different folders,
or to remove all trace in the registry. But for your particular scenario I
don't think necessary.

Regards,
Peter T

"Trefor" wrote in message
...
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