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


"Peter T" wrote:

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

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


"Peter T" wrote:

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