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



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