Weird AddIn behaviour

[Trefor]

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?

 

[Peter T]

Hi Trefor,

The behaviour you described is predictable, it's the behaviour Addins
collection that's weird!.

When you uninstalled addin-1 you didn't remove it from the Addins
collection. When you tried to addin-2 with the same title (in absence of a
title it defaults to name) it simply reinstalled the similarly 'titled'
addin that already exists in the addins collection, ie addin-1.

Possible solutions -
1 - remove addin-1 from the collection.

1a) Manually, after uninstalling the addin remove it from the current
folder. Try & re-check it in Tools > Addins. Follow the prompts to allow
removal.
Note - If the addin is not in one of the default addins locations need to do
this in same session as it was uninstalled. Otherwise in next session it
won't be visible in the drop down (but it still exists in the registry)

1b) Remove from the registry if it's not in a default location (manually or
programmatically). If it's in a default addins location and uninstalled it
won't be in the registry, so after uninstalling move it to another folder

2 Give addin-2 a unique title
Load it from file
wb.title = "new title"
wb.save

Now you should be able to install addin-2

Also note if your addins had same titles but different names located in
different folders the same scenario you described would occur.

Regards,
Peter T

PS, you didn't need to go to the trouble of modifying code in respective
addins to demonstrate. Select the file in Project Explorer (ctrl-R) and in
the intermediate window
?thisworkbook.fullname
hit enter

 

[Bill Pfister]

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

 

[Peter T]

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

 

[Peter T]

(check end of line col position in the VBE).

less than say 78 should be OK

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

<snip>

 

[Bill Pfister]

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]

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

 

[Trefor]

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.

 

[Bill Pfister]

I created the previously mentioned deployment utility to essentially handle
the same issue. I have not found a more effective solution if you want/have
to keep your add-in in the realm of VBA (as opposed to a solution withi VSTO
or similar platforms).

You can also bring code modules into workbooks (add-ins) to "update the
engine" but it is a relatively complex operation and it requires more
technical expertise in your maintenance staff.

John Walkenbach's PUP add-in has a unique and ingenious approach to
modular/updateable functionality within an add-in. He licenses the source,
but again, it is a more complicated undertaking.

I would appreciate hearing anyone else's thoughts on the issue.

 

[Peter T]

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]

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]

Bill,

Many thanks for all your comments and code. I have added you code to my
startup code and so far so good.

I am also going to try and keep the xla filename the same so hopefully this
will help.

 

[Peter T]

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 tools>references
(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]

Peter,

Many thanks again for the detailed reply. Perhaps I will summarize what I
was trying to do:

Start.xls - (contains basic checking code and all my worksheets)
(1) Check to see if "<networkdrive>\macro.xla" is different to
"<localdrive>\Application Directory". If different copy off the server.

(2) Check to see if "<localdrive>\Application Directory" is different to
Application.UserLibraryPath. If different copy from "<localdrive>\Application
Directory"

(3) Start.xls then runs code in macro.xla (Run addinname & "!procedurename")
and all code for the duration ‘session’ is then run from macro.xla. (Auto
Open/Close are in Start.xls)

When I originally posted this thread, the addin was named “macro v1.0.xlaâ€
and each upgrade was then distributed as “macro v1.0.xla†for small changes
and then “macro v1.1.xla†for more significant changes. I had/have three
problems:

(1) Although I also load the .xla from Application.UserLibraryPath
occasionally the addin loaded said it path was "<localdrive>\Application
Directory". I am not sure why this was happening, but it just seemed to
complicate what I was trying to do.

(2) When I distributed the update with no name change I often was not seeing
the new file, but the old file.

(3) When I distributed the update with a name change this seemed fix the (2)
problem, but I would then end up with a ever growing list in my Tool>Add-In.


I am trying to keep this simple, but also flexible. Given that I distribute
the code I don't want the user to have to do anything manually and I would
prefer that everything is contained within Start.xla and macro.xla
--
Trefor

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 tools>references
(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

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.

 

[Peter T]

I don't entirely follow what you detail in your summary, eg.

- What do you mean by "Application Directory", the Excel.exe folder ?

- Start.xls, how is this loaded, in the start-up path? You say
"contains...and all my worksheets" Do other workbooks have any links
whatsoever to the addin. Apart from the .Run in Start.xls any other links to
the addin.

I well understand the points/problems you list under "When I originally
posted this thread". Confusion abounds about the Addins collection so I'll
try and list how Excel populates the collection with some further comments.

1. In Registry *\Excel\Options section, OPEN, OPEN1 etc. These detail
installed addins which will load on startup.

2. xla's found in .UserLibraryPath & .LibraryPath (latter for backward
compatibility with xl97)

3. xla fullnames in Registry *Excel\Add-in Manager\
These are any other non-installed addins not located in either of the
default addin paths.

(4. non-installed system addins found by other means)

Addin collection names are defined by .Title which defaults. to addin.name
if a title does not exist.

The Addins collection & Workbooks collection are not directly related. IOW
if you unload an installed addin its status in the addins collection does
not change (it will load on next Excel startup).

If you uninstall an addin, try and install a same name/title addin in a
different folder, Excel will look in its addins collection for a same
name/title addin. It will find the addin listed in either 2 & 3 above and
re-install that. This I think is the original problem you had (if you had
not deleted or moved old addin).

Even more confusing, if the old & new addin's titles are defined and both
same, yet new addin's name & folder location are different, if you
programmatically try and install the new addin the old addin may get
re-installed (ie if the entry exists in 3 above and the old file is found).

Uninstalled addins that are not in a default addin folder may not be visible
in Tools > Addins in next Excel session (yet remain in the addins collection
due to the entry in 3 above).

If you uninstall an addin that's not in a default folder, then delete the
file, its entry will persist in 3 and in the addins collection.

Ideally there ought to be a method to 'Remove' non-installed addins from the
addins collection and hence the list in 3. above. But there isn't and this
is why problems & confusion occurs.

Back to your issue, apart from code in Start.xls to update the addin I don't
follow the rest of the set up. Also how does code know that new & old addins
are not same (file size/date ?).

To summarize, if you are updating addins with slightly different version
names and/or folder location, the old addin's details will remain in the
addins collection. If the old addin is in a default addin folder it should
be removed (not merely renamed). If in a non-default folder you could use
the registry code to delete the entry in 3.above.

But if everything is identical concerning the addins, ie name, title &
intended folder (other than internal code) all you need to do is unload it,
remove from folder or Kill it, replace new addin in the same folder, and
load it. IOW in this particular scenario absolutely nothing concerning the
addins collection or registry entries change, hence registry code not
required.

Finally, if updating an addin with revised version name and/or folder, any
UDF links in other workbooks may also need updating (but from what I gather
this is not in your scenario).

Regards,
Peter T

Peter,

Many thanks again for the detailed reply. Perhaps I will summarize what I
was trying to do:

Start.xls - (contains basic checking code and all my worksheets)
(1) Check to see if "<networkdrive>\macro.xla" is different to
"<localdrive>\Application Directory". If different copy off the server.

(2) Check to see if "<localdrive>\Application Directory" is different to
Application.UserLibraryPath. If different copy from

Directory"

(3) Start.xls then runs code in macro.xla (Run addinname & "!procedurename")
and all code for the duration 'session' is then run from macro.xla. (Auto
Open/Close are in Start.xls)

When I originally posted this thread, the addin was named "macro v1.0.xla"
and each upgrade was then distributed as "macro v1.0.xla" for small changes
and then "macro v1.1.xla" for more significant changes. I had/have three
problems:

(1) Although I also load the .xla from Application.UserLibraryPath
occasionally the addin loaded said it path was "<localdrive>\Application
Directory". I am not sure why this was happening, but it just seemed to
complicate what I was trying to do.

(2) When I distributed the update with no name change I often was not seeing
the new file, but the old file.

(3) When I distributed the update with a name change this seemed fix the (2)
problem, but I would then end up with a ever growing list in my Tool>Add-In.


I am trying to keep this simple, but also flexible. Given that I distribute
the code I don't want the user to have to do anything manually and I would
prefer that everything is contained within Start.xla and macro.xla
--
Trefor

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 tools>references
(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

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


:

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

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


:

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

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)

 

[Trefor]

Peter,

Application Directory - my application directory
Start.xls resides in my app directory and is started through a shortcut or a
double click.
There are no links to the macro.xla.

I don't know what what you mean by OPEN, OPEN1, etc


“how does code know that new & old addins
are not same (file size/date ?).â€

In start.xls:

Public Const DataCollectClientVersion = "6.46c"
Public Const DCMaster = "'Customer Data Collect Master.xla'"
Public Const MinimumMacroVersion = "6.46b" ' This is the minimum version of
the macro file, that this program will run with.

Run DCMaster & "!Prepare_For_Startup", DataCollectClientVersion,
MinimumMacroVersion

In macro.xla (or as you can see above DCMaster)

Public Const DataCollectMacroVersion = "6.46b" ' The version of this
macro file
Public Const DataCollectMasterVersion = "6.46" ' The version that must
be run. I.E. the must upgrade to version

Sub Prepare_For_Startup(DataCollectClientVersion, MinimumMacroVersion As
String)
' Check Client/Master Versions
If MinimumMacroVersion > DataCollectMacroVersion Then
msg = "A crital error has occured." & vbCrLf & vbCrLf
msg = msg & "The '" & ThisWorkbook.name & "' file is version " &
DataCollectMacroVersion & "." & vbCrLf & vbCrLf
msg = msg & "The minimum version required for this Data Collect is "
& MinimumMacroVersion & "." & vbCrLf & vbCrLf
msg = msg & "This program can not continue until this is resolved."
MsgBox msg, vbCritical
Exit Sub
End If
..
..
..
..
End Sub

It sounds to me that by using a version number in my macro.xla file name I
am making this more complicated than it needs to be. This is not a big deal
in my case and so I will tweak the code to use the same name from now on.

Thankyou very much for your very complete answer.

One last question, do you see an issue with macro.xla existing in both the
default location and my apps directory?

--
Trefor

I don't entirely follow what you detail in your summary, eg.

- What do you mean by "Application Directory", the Excel.exe folder ?

- Start.xls, how is this loaded, in the start-up path? You say
"contains...and all my worksheets" Do other workbooks have any links
whatsoever to the addin. Apart from the .Run in Start.xls any other links to
the addin.

I well understand the points/problems you list under "When I originally
posted this thread". Confusion abounds about the Addins collection so I'll
try and list how Excel populates the collection with some further comments.

1. In Registry *\Excel\Options section, OPEN, OPEN1 etc. These detail
installed addins which will load on startup.

2. xla's found in .UserLibraryPath & .LibraryPath (latter for backward
compatibility with xl97)

3. xla fullnames in Registry *Excel\Add-in Manager\
These are any other non-installed addins not located in either of the
default addin paths.

(4. non-installed system addins found by other means)

Addin collection names are defined by .Title which defaults. to addin.name
if a title does not exist.

The Addins collection & Workbooks collection are not directly related. IOW
if you unload an installed addin its status in the addins collection does
not change (it will load on next Excel startup).

If you uninstall an addin, try and install a same name/title addin in a
different folder, Excel will look in its addins collection for a same
name/title addin. It will find the addin listed in either 2 & 3 above and
re-install that. This I think is the original problem you had (if you had
not deleted or moved old addin).

Even more confusing, if the old & new addin's titles are defined and both
same, yet new addin's name & folder location are different, if you
programmatically try and install the new addin the old addin may get
re-installed (ie if the entry exists in 3 above and the old file is found).

Uninstalled addins that are not in a default addin folder may not be visible
in Tools > Addins in next Excel session (yet remain in the addins collection
due to the entry in 3 above).

If you uninstall an addin that's not in a default folder, then delete the
file, its entry will persist in 3 and in the addins collection.

Ideally there ought to be a method to 'Remove' non-installed addins from the
addins collection and hence the list in 3. above. But there isn't and this
is why problems & confusion occurs.

Back to your issue, apart from code in Start.xls to update the addin I don't
follow the rest of the set up. Also how does code know that new & old addins
are not same (file size/date ?).

To summarize, if you are updating addins with slightly different version
names and/or folder location, the old addin's details will remain in the
addins collection. If the old addin is in a default addin folder it should
be removed (not merely renamed). If in a non-default folder you could use
the registry code to delete the entry in 3.above.

But if everything is identical concerning the addins, ie name, title &
intended folder (other than internal code) all you need to do is unload it,
remove from folder or Kill it, replace new addin in the same folder, and
load it. IOW in this particular scenario absolutely nothing concerning the
addins collection or registry entries change, hence registry code not
required.

Finally, if updating an addin with revised version name and/or folder, any
UDF links in other workbooks may also need updating (but from what I gather
this is not in your scenario).

Regards,
Peter T

Peter,

Many thanks again for the detailed reply. Perhaps I will summarize what I
was trying to do:

Start.xls - (contains basic checking code and all my worksheets)
(1) Check to see if "<networkdrive>\macro.xla" is different to
"<localdrive>\Application Directory". If different copy off the server.

(2) Check to see if "<localdrive>\Application Directory" is different to
Application.UserLibraryPath. If different copy from

Directory"

(3) Start.xls then runs code in macro.xla (Run addinname & "!procedurename")
and all code for the duration 'session' is then run from macro.xla. (Auto
Open/Close are in Start.xls)

When I originally posted this thread, the addin was named "macro v1.0.xla"
and each upgrade was then distributed as "macro v1.0.xla" for small changes
and then "macro v1.1.xla" for more significant changes. I had/have three
problems:

(1) Although I also load the .xla from Application.UserLibraryPath
occasionally the addin loaded said it path was "<localdrive>\Application
Directory". I am not sure why this was happening, but it just seemed to
complicate what I was trying to do.

(2) When I distributed the update with no name change I often was not seeing
the new file, but the old file.

(3) When I distributed the update with a name change this seemed fix the (2)
problem, but I would then end up with a ever growing list in my Tool>Add-In.


I am trying to keep this simple, but also flexible. Given that I distribute
the code I don't want the user to have to do anything manually and I would
prefer that everything is contained within Start.xla and macro.xla
--
Trefor

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 tools>references
(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

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


:

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

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


:

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

 

[Peter T]

I don't know what what you mean by OPEN, OPEN1, etc

Look for OPEN, OPEN1 etc in your registry under
HKCU - Software\Microsoft\Office\xl-ver\Excel\Options\ (or \Excel\Microsoft
Excel\ in xl97)

these are the Installed Addins, ie ticked in Tools> Addins

Also look in the nearby \Add-in Manager for uninstalled addins

It sounds to me that by using a version number in my macro.xla file name I
am making this more complicated than it needs to be. This is not a big deal
in my case and so I will tweak the code to use the same name from now on.

There may be good reasons to append the addin name with its version though
it does add to complications as discussed earlier. You could of course put
the version inside the xla, eg in a cell or one of the file properties. Read
this from your Main.xls by setting a ref to the xla as a Workbook.

Just wondering, if your xla is only used by your Main.xls does the xla even
need to be 'installed' as an addin, perhaps you could just load it on demand
from Main.xls, eg

on error resume next

Public gbAddinRunning as boolean

Sub SomeRoutineThatCallsMyAddin()

If Not CheckAddinRunning then Exit sub

'OK to run code from myAddin

End Sub

Function CheckAddinRunning() as boolean

On error resume next
If not If gbAddinRunning then
set wb = workbooks("myAddin.xla")
if wb is nothing then
set wb = workbooks.open(addin-fullname)
CheckVersion wb
end if
gbAddinRunning = not wb is nothing
End if
CheckAddinRunning = gbAddinRunning

End sub

function CheckVersion(wb as workbook) as boolean
'appropriate error handling throughout & messages if/as required
if wb.sheets(1).range("a1") <> "required-version" then
wb.close
set wb = nothing
' kill or move addin-fullname
' bring in new addin from server
set wb = workbooks.open(addin-fullname)
end if

CheckVersion = not wb is nothing 'everything seems OK
end function

(only an idea)

One last question, do you see an issue with macro.xla existing in both the

default location and my apps directory?

It would only be a problem if both same name/title files are in the Addins
collection from respective folders, even if only one is to be installed.
Obviously avoid possibility of both being loaded concurrently regardless as
to their status in the addins collection.

Regards,
Peter T

Peter,

Application Directory - my application directory
Start.xls resides in my app directory and is started through a shortcut or a
double click.
There are no links to the macro.xla.

I don't know what what you mean by OPEN, OPEN1, etc


"how does code know that new & old addins
are not same (file size/date ?)."

In start.xls:

Public Const DataCollectClientVersion = "6.46c"
Public Const DCMaster = "'Customer Data Collect Master.xla'"
Public Const MinimumMacroVersion = "6.46b" ' This is the minimum version of
the macro file, that this program will run with.

Run DCMaster & "!Prepare_For_Startup", DataCollectClientVersion,
MinimumMacroVersion

In macro.xla (or as you can see above DCMaster)

Public Const DataCollectMacroVersion = "6.46b" ' The version of this
macro file
Public Const DataCollectMasterVersion = "6.46" ' The version that must
be run. I.E. the must upgrade to version

Sub Prepare_For_Startup(DataCollectClientVersion, MinimumMacroVersion As
String)
' Check Client/Master Versions
If MinimumMacroVersion > DataCollectMacroVersion Then
msg = "A crital error has occured." & vbCrLf & vbCrLf
msg = msg & "The '" & ThisWorkbook.name & "' file is version " &
DataCollectMacroVersion & "." & vbCrLf & vbCrLf
msg = msg & "The minimum version required for this Data Collect is "
& MinimumMacroVersion & "." & vbCrLf & vbCrLf
msg = msg & "This program can not continue until this is resolved."
MsgBox msg, vbCritical
Exit Sub
End If
.
.
.
.
End Sub

It sounds to me that by using a version number in my macro.xla file name I
am making this more complicated than it needs to be. This is not a big deal
in my case and so I will tweak the code to use the same name from now on.

Thankyou very much for your very complete answer.

One last question, do you see an issue with macro.xla existing in both the
default location and my apps directory?

--
Trefor

I don't entirely follow what you detail in your summary, eg.

- What do you mean by "Application Directory", the Excel.exe folder ?

- Start.xls, how is this loaded, in the start-up path? You say
"contains...and all my worksheets" Do other workbooks have any links
whatsoever to the addin. Apart from the .Run in Start.xls any other links to
the addin.

I well understand the points/problems you list under "When I originally
posted this thread". Confusion abounds about the Addins collection so I'll
try and list how Excel populates the collection with some further comments.

1. In Registry *\Excel\Options section, OPEN, OPEN1 etc. These detail
installed addins which will load on startup.

2. xla's found in .UserLibraryPath & .LibraryPath (latter for backward
compatibility with xl97)

3. xla fullnames in Registry *Excel\Add-in Manager\
These are any other non-installed addins not located in either of the
default addin paths.

(4. non-installed system addins found by other means)

Addin collection names are defined by .Title which defaults. to addin.name
if a title does not exist.

The Addins collection & Workbooks collection are not directly related. IOW
if you unload an installed addin its status in the addins collection does
not change (it will load on next Excel startup).

If you uninstall an addin, try and install a same name/title addin in a
different folder, Excel will look in its addins collection for a same
name/title addin. It will find the addin listed in either 2 & 3 above and
re-install that. This I think is the original problem you had (if you had
not deleted or moved old addin).

Even more confusing, if the old & new addin's titles are defined and both
same, yet new addin's name & folder location are different, if you
programmatically try and install the new addin the old addin may get
re-installed (ie if the entry exists in 3 above and the old file is found).

Uninstalled addins that are not in a default addin folder may not be visible
in Tools > Addins in next Excel session (yet remain in the addins collection
due to the entry in 3 above).

If you uninstall an addin that's not in a default folder, then delete the
file, its entry will persist in 3 and in the addins collection.

Ideally there ought to be a method to 'Remove' non-installed addins from the
addins collection and hence the list in 3. above. But there isn't and this
is why problems & confusion occurs.

Back to your issue, apart from code in Start.xls to update the addin I don't
follow the rest of the set up. Also how does code know that new & old addins
are not same (file size/date ?).

To summarize, if you are updating addins with slightly different version
names and/or folder location, the old addin's details will remain in the
addins collection. If the old addin is in a default addin folder it should
be removed (not merely renamed). If in a non-default folder you could use
the registry code to delete the entry in 3.above.

But if everything is identical concerning the addins, ie name, title &
intended folder (other than internal code) all you need to do is unload it,
remove from folder or Kill it, replace new addin in the same folder, and
load it. IOW in this particular scenario absolutely nothing concerning the
addins collection or registry entries change, hence registry code not
required.

Finally, if updating an addin with revised version name and/or folder, any
UDF links in other workbooks may also need updating (but from what I gather
this is not in your scenario).

Regards,
Peter T

Peter,

Many thanks again for the detailed reply. Perhaps I will summarize what I
was trying to do:

Start.xls - (contains basic checking code and all my worksheets)
(1) Check to see if "<networkdrive>\macro.xla" is different to
"<localdrive>\Application Directory". If different copy off the server.

(2) Check to see if "<localdrive>\Application Directory" is different to
Application.UserLibraryPath. If different copy from

Directory"

(3) Start.xls then runs code in macro.xla (Run addinname & "!procedurename")
and all code for the duration 'session' is then run from macro.xla. (Auto
Open/Close are in Start.xls)

When I originally posted this thread, the addin was named "macro v1.0.xla"
and each upgrade was then distributed as "macro v1.0.xla" for small changes
and then "macro v1.1.xla" for more significant changes. I had/have three
problems:

(1) Although I also load the .xla from Application.UserLibraryPath
occasionally the addin loaded said it path was

Directory". I am not sure why this was happening, but it just seemed to
complicate what I was trying to do.

(2) When I distributed the update with no name change I often was not seeing
the new file, but the old file.

(3) When I distributed the update with a name change this seemed fix

the
(2)

problem, but I would then end up with a ever growing list in my Tool>Add-In.


I am trying to keep this simple, but also flexible. Given that I distribute
the code I don't want the user to have to do anything manually and I would
prefer that everything is contained within Start.xla and macro.xla
--
Trefor


:

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 tools>references
(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

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


:

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

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


:

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

 

[Trefor]

Peter,

Sorry for the delay in getting back to you and many thanks for the reply,
but I am still have issues.


Function AddinPresent(DCMaster2) As Boolean
' This Funcation checks that the add-in is available for use by the Client
Dim AddInInstalled As Boolean, wb As Workbook
With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2)
' Is Addin Installed?
AddInInstalled = .Installed
End With
If AddInInstalled Then
On Error Resume Next
Run DCMaster & "!any_macro"
LastError = Err
If LastError = 0 Then
' Addin is installed, exit function
AddinPresent = True
Exit Function
End If
End If

' Try and clear up all reference to the AddIn
Call Clear_XLA(DCMaster2)
' This calls a series of .Installed = False for every path I can
think of
' With AddIns.Add(FileName:=Application.UserLibraryPath &
MemoryFile)
' .Installed = False
' End With
' Then it calls Bill's registry cleaner code'

' Addin is NOT install, attempt to load it
With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2)
.Installed = True
End With
' Now check that the Addin has loaded
With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2)
AddInInstalled = .Installed
End With
If AddInInstalled Then
' Addin is installed, exit function
AddinPresent = True
Exit Function
End If

' Still not loaded

On Error Resume Next
Set wb = Workbooks(DCMaster2) <<<< Error 9 at this point
If wb Is Nothing Then
Set wb = Workbooks.Open(Application.UserLibraryPath & DCMaster2)
End If

' Given the file was loaded as a workbook, I am not sure whether I can now
make it an Addin or whether this matters?
' Addin is NOT install, attempt to load it
With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2)
.Installed = True
End With

With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2)
AddInInstalled = .Installed
End With
If AddInInstalled Then
' Addin is installed, exit function
AddinPresent = True
Exit Function
Else

' I am exiting here with .XLA loaded in the project folder.
' ThisWorkbook.path = Application.UserLibraryPath & DCMaster2
' If I got to Tools > Add-Ins, DCMaster2 is listed and is unticked.
' If I click on it is says the file does not exist in myAppPath & DCMaster2
and do I want to remove it.
‘ If I remove this last Add-In check at this point everything seems to work.
' Addin has failed to install
AddinPresent = False
Exit Function
End If
End Function

So as you put it:

Just wondering, if your xla is only used by your Main.xls does the xla even
need to be 'installed' as an addin, perhaps you could just load it on demand
from Main.xls, eg

Good question, I just presumed based on previous feedback that was what I
needed to do. If I loaded the file this way, what are the advantages/
disadvantages compared to an Add-In? I.E. Is there catch I have not found yet?

 

[Peter T]

Hello again Trefor,

I still haven't fully worked out your whole setup but the code you posted
seems unnecessarily complicated. I assume "DCMaster2" is the addin name (a
string) but confused. If the first character is not a "\" path separator
some of your code will fail, but if it is another part of your code will
fail (I think).

I'm not sure if you are confused between an Addin and the Addins collection

An Addin is a workbook with its IsAddin property True, typically saved with
an xla extension

The Addins collection (manager) is basically a set of references to Addins.
A given addin may or may not be installed (ie if checked in tools > addins
will load on startup). If the addin has been removed from known location it
will continue to exist in the collection, (though error and/or warning may
occur on attempt to install it).

I would suggest giving your addin a title as I mentioned previously, then
test its existence in the collection

dim adn as Addin
on error resume next
Set adn = application.addins(sTitle)
bInManager = not adn is nothing

If it exists in the collection is it installed
bInstalled = adn.installed

If it's in the collection you can return it's path whether or not it's
installed or even if it no longer exists on disk
sPath = adn.path

To conclusively prove the addin is loaded if installed (or loaded by some
other method)
on error resume next
set wb = application.workbooks("myAddin.xla")

As for trying to find the location of the addin, if not installed or not
loaded, use the Dir function rather than trying to force install from
various possible locations. Don't forget the path separator.

Good question, I just presumed based on previous feedback that was what I
needed to do. If I loaded the file this way, what are the advantages/
disadvantages compared to an Add-In? I.E. Is there catch I have not found

yet?

Your comment "compared to an Add-In" is what made me wonder about possible
confusion. Having an Addin in the addins collection is merely a convenience
to have it automatically load on startup. There is no difference as to how
the addin will operate once it is loaded. It seems the addin is only used by
your Main.xls and contains no UDF's. Therefore providing you know it's
fullname it's easy for code in your Main.xls to test if the addin (ie
workbook) is loaded and if not load it. You might do this in Main's open
event. Do your checks to verify the current version is up to date etc. Error
handler's elsewhere to do same if trying to run code from it fails. FWIW you
could even rename the default xla extension to disguise it and reduce
temptation for users to load it independently.

Regards,
Peter T