(Originally posted in a VB forum, slightly revised. It was recommended
I post it here - I agree.)

The following includes many details of how I arrived where I am now.
While not directly relevant to the question at hand I hope it may help
others who may be struggling with similar issues.

For anyone who would like to help, but wishes to skip the history
please skip down to the line of asterisks.

I have a project which has been consuming way too much time, of late,
fixing errors.

Originally all of the drafters where I work used an Excel file
(Ship.xls), stored on the network, as a template for creating a file
they all use - one per "job."

This meant that all changes to correct pre-existing errors in code
needed to be applied individually to a VERY large number of files.

I fixed this by placing each module (mod) in its own file on the
server and every time Ship.xls was opened on a users PC it deleted all
of the mods from itself and copied in the mods from the server. This
occurs for both the template AND any resulting files, as there are a
lot of changes being made right now.

This worked well, and was quite fast!

Then, one day, it just plain stopped working. It would infinite loop
on one mod, always the same one on any given machine, but not
necessarily the same one on different machines... That is to say, PC
"1" would infinite loop (IL) on mod "K", while PC "12" would never IL
on mod "K" but would on mod "B"... and so on. This happened
spontaneously (no changes to any code for two weeks prior) and
affected ALL the PCs, starting on the same day.

A file compare of the code which was not working with the code that
was showed no discrepancies. Replacing the template file with the
backup file had no effect, but after a resulting file was created I
was able to overwrite the code in ThisWorkbook of the resulting file
with the code from the backup of the template - then everything
functioned normally.

The end result is that almost four months after making this change I
nearly had to abandon it - enter XLA files.

With XLAs all my mods would once again be in an Excel file, but not
the one the users make use of as a template. However, it did, like my
earlier attempt, reside on the server.

It is NOT flawless...

For example: in a mod in the XLA I make use of
"Windows(strShipFileName).Activate". This promptly stopped working.

It seemed that I could activate any window I liked except the one that
held Ship.xls. Windows("ship.out").Activate worked fine,
Windows("cDet.shp").Activate worked without issue.

One interesting note was that I discovered that Hiding/not hiding
extensions effected this line of code... now that it was not running
from a mod inside Ship.xls.

After adding code to check the registry to confirm the hiding/un
hiding of extensions the code "Windows(strShipFileName).Activate"
resolved to "Windows("Ship").Activate" or
"Windows("Ship.xls").Activate" and kind'a worked. This worked for
most PCs, but two just would NOT work. I even reversed the registry
settings for extensions on these machines and still no effect.

The solution to this problem was to do a SaveAS prior to any other
file manipulation. For some reason the machines that cannot see
"Ship" or "Ship.xls" can see "08110hShip(.xls)" just fine.
("08110hShip" was what the file in this example would have been saved
to at the completion of a series of automated routines, instead I just
save to the new name prior to automation, then save again, upon
successful completion of said automation.)


************************************************
One last problem needs solving now, and I do not know how to do it.

As long as any ship file is open (template and/or multiple completed
ones) I need the xla to be open, but when the last ship file closes I
need the xla to close. The reason is that much of the code in the XLA
is used to manipulate these files, to run calculations, after the
aforementioned automation completes, and while the user makes changes,
prints, etc.

If the XLA remains open then in order for any changes that I may make
to be seen EVERY user MUST exit Excel completely and restart Excel.

This is NOT user friendly, as most of these users do not reboot often
and only rarely exit Excel as it is the one application they KNOW they
will be using daily.

This means that corrections to formula(s) and changes to VBA code can
go unnoticed by some users for days, even weeks.

I need Excel on the users machine to make use of the changes the next
day, and on occation after lunch.

Thank you.

AddIns("myAddin").Installed = False

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Mystif" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> (Originally posted in a VB forum, slightly revised. It was recommended
> I post it here - I agree.)
>
> The following includes many details of how I arrived where I am now.
> While not directly relevant to the question at hand I hope it may help
> others who may be struggling with similar issues.
>
> For anyone who would like to help, but wishes to skip the history
> please skip down to the line of asterisks.
>
> I have a project which has been consuming way too much time, of late,
> fixing errors.
>
> Originally all of the drafters where I work used an Excel file
> (Ship.xls), stored on the network, as a template for creating a file
> they all use - one per "job."
>
> This meant that all changes to correct pre-existing errors in code
> needed to be applied individually to a VERY large number of files.
>
> I fixed this by placing each module (mod) in its own file on the
> server and every time Ship.xls was opened on a users PC it deleted all
> of the mods from itself and copied in the mods from the server. This
> occurs for both the template AND any resulting files, as there are a
> lot of changes being made right now.
>
> This worked well, and was quite fast!
>
> Then, one day, it just plain stopped working. It would infinite loop
> on one mod, always the same one on any given machine, but not
> necessarily the same one on different machines... That is to say, PC
> "1" would infinite loop (IL) on mod "K", while PC "12" would never IL
> on mod "K" but would on mod "B"... and so on. This happened
> spontaneously (no changes to any code for two weeks prior) and
> affected ALL the PCs, starting on the same day.
>
> A file compare of the code which was not working with the code that
> was showed no discrepancies. Replacing the template file with the
> backup file had no effect, but after a resulting file was created I
> was able to overwrite the code in ThisWorkbook of the resulting file
> with the code from the backup of the template - then everything
> functioned normally.
>
> The end result is that almost four months after making this change I
> nearly had to abandon it - enter XLA files.
>
> With XLAs all my mods would once again be in an Excel file, but not
> the one the users make use of as a template. However, it did, like my
> earlier attempt, reside on the server.
>
> It is NOT flawless...
>
> For example: in a mod in the XLA I make use of
> "Windows(strShipFileName).Activate". This promptly stopped working.
>
> It seemed that I could activate any window I liked except the one that
> held Ship.xls. Windows("ship.out").Activate worked fine,
> Windows("cDet.shp").Activate worked without issue.
>
> One interesting note was that I discovered that Hiding/not hiding
> extensions effected this line of code... now that it was not running
> from a mod inside Ship.xls.
>
> After adding code to check the registry to confirm the hiding/un
> hiding of extensions the code "Windows(strShipFileName).Activate"
> resolved to "Windows("Ship").Activate" or
> "Windows("Ship.xls").Activate" and kind'a worked. This worked for
> most PCs, but two just would NOT work. I even reversed the registry
> settings for extensions on these machines and still no effect.
>
> The solution to this problem was to do a SaveAS prior to any other
> file manipulation. For some reason the machines that cannot see
> "Ship" or "Ship.xls" can see "08110hShip(.xls)" just fine.
> ("08110hShip" was what the file in this example would have been saved
> to at the completion of a series of automated routines, instead I just
> save to the new name prior to automation, then save again, upon
> successful completion of said automation.)
>
>
> ************************************************
> One last problem needs solving now, and I do not know how to do it.
>
> As long as any ship file is open (template and/or multiple completed
> ones) I need the xla to be open, but when the last ship file closes I
> need the xla to close. The reason is that much of the code in the XLA
> is used to manipulate these files, to run calculations, after the
> aforementioned automation completes, and while the user makes changes,
> prints, etc.
>
> If the XLA remains open then in order for any changes that I may make
> to be seen EVERY user MUST exit Excel completely and restart Excel.
>
> This is NOT user friendly, as most of these users do not reboot often
> and only rarely exit Excel as it is the one application they KNOW they
> will be using daily.
>
> This means that corrections to formula(s) and changes to VBA code can
> go unnoticed by some users for days, even weeks.
>
> I need Excel on the users machine to make use of the changes the next
> day, and on occation after lunch.
>
> Thank you.

You may want to look at my reversioning add-in loader
http://www.DecisionModels.com/downloads.htm

(you would probably need to customise it but I think it basically solves
most of your problems. You could also add some code to the template so that
it automatically runs the loader each time a template gets opened).

The Add-In Loader is designed to solve some of the problems of maintaining
and loading Add-Ins on a network:

Dynamic reversioning of add-ins with automatic link updating.

Control from a central point which add-ins will be automatically loaded
when Excel starts.

Control from a central point which add-ins will be available for
on-demand loading.

Enable updated versions of an add-in to be placed on a server at the
same time as previous versions are in use.

Optionally specify and/or change the network path for each add-in.

No Registry changes for load and unload.

The Add-In Loader helps the User:

Improve Excel start-up time by bypassing the loading of add-ins that are
only needed intermittently.

Minimize memory use by loading add-ins when needed and easily unloading
them when not needed.

Dynamically load the latest version of an add-in, whilst unloading any
version which is currently loaded.

Dynamically update any links that point to old versions of loaded
add-ins when a workbook is opened or an add-in is loaded.

The Zip file contains:

AddLoaderV2.XLA - the Add-InLoader Add-In.

AddinLoad.txt - example file containing test Add-In Root names.

Add-in Loader V2.Doc - Word document explaining how to install and use
the Add-In Loader V2.

You can customise the Add-In Loader code as required. The XLA is password
protected to prevent accidental alteration.

To view the VBA code in the Addin unprotect the XLA using a password of dm
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Mystif" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> (Originally posted in a VB forum, slightly revised. It was recommended
> I post it here - I agree.)
>
> The following includes many details of how I arrived where I am now.
> While not directly relevant to the question at hand I hope it may help
> others who may be struggling with similar issues.
>
> For anyone who would like to help, but wishes to skip the history
> please skip down to the line of asterisks.
>
> I have a project which has been consuming way too much time, of late,
> fixing errors.
>
> Originally all of the drafters where I work used an Excel file
> (Ship.xls), stored on the network, as a template for creating a file
> they all use - one per "job."
>
> This meant that all changes to correct pre-existing errors in code
> needed to be applied individually to a VERY large number of files.
>
> I fixed this by placing each module (mod) in its own file on the
> server and every time Ship.xls was opened on a users PC it deleted all
> of the mods from itself and copied in the mods from the server. This
> occurs for both the template AND any resulting files, as there are a
> lot of changes being made right now.
>
> This worked well, and was quite fast!
>
> Then, one day, it just plain stopped working. It would infinite loop
> on one mod, always the same one on any given machine, but not
> necessarily the same one on different machines... That is to say, PC
> "1" would infinite loop (IL) on mod "K", while PC "12" would never IL
> on mod "K" but would on mod "B"... and so on. This happened
> spontaneously (no changes to any code for two weeks prior) and
> affected ALL the PCs, starting on the same day.
>
> A file compare of the code which was not working with the code that
> was showed no discrepancies. Replacing the template file with the
> backup file had no effect, but after a resulting file was created I
> was able to overwrite the code in ThisWorkbook of the resulting file
> with the code from the backup of the template - then everything
> functioned normally.
>
> The end result is that almost four months after making this change I
> nearly had to abandon it - enter XLA files.
>
> With XLAs all my mods would once again be in an Excel file, but not
> the one the users make use of as a template. However, it did, like my
> earlier attempt, reside on the server.
>
> It is NOT flawless...
>
> For example: in a mod in the XLA I make use of
> "Windows(strShipFileName).Activate". This promptly stopped working.
>
> It seemed that I could activate any window I liked except the one that
> held Ship.xls. Windows("ship.out").Activate worked fine,
> Windows("cDet.shp").Activate worked without issue.
>
> One interesting note was that I discovered that Hiding/not hiding
> extensions effected this line of code... now that it was not running
> from a mod inside Ship.xls.
>
> After adding code to check the registry to confirm the hiding/un
> hiding of extensions the code "Windows(strShipFileName).Activate"
> resolved to "Windows("Ship").Activate" or
> "Windows("Ship.xls").Activate" and kind'a worked. This worked for
> most PCs, but two just would NOT work. I even reversed the registry
> settings for extensions on these machines and still no effect.
>
> The solution to this problem was to do a SaveAS prior to any other
> file manipulation. For some reason the machines that cannot see
> "Ship" or "Ship.xls" can see "08110hShip(.xls)" just fine.
> ("08110hShip" was what the file in this example would have been saved
> to at the completion of a series of automated routines, instead I just
> save to the new name prior to automation, then save again, upon
> successful completion of said automation.)
>
>
> ************************************************
> One last problem needs solving now, and I do not know how to do it.
>
> As long as any ship file is open (template and/or multiple completed
> ones) I need the xla to be open, but when the last ship file closes I
> need the xla to close. The reason is that much of the code in the XLA
> is used to manipulate these files, to run calculations, after the
> aforementioned automation completes, and while the user makes changes,
> prints, etc.
>
> If the XLA remains open then in order for any changes that I may make
> to be seen EVERY user MUST exit Excel completely and restart Excel.
>
> This is NOT user friendly, as most of these users do not reboot often
> and only rarely exit Excel as it is the one application they KNOW they
> will be using daily.
>
> This means that corrections to formula(s) and changes to VBA code can
> go unnoticed by some users for days, even weeks.
>
> I need Excel on the users machine to make use of the changes the next
> day, and on occation after lunch.
>
> Thank you.

By the way there is almost no reason to ever try to activate Windows.

The recommended method is to use workbook and worksheet object references:

Thisworkbook always refers to the workbook that contains the code (ie the
XLA)
Activeworkbook refers to whichever workbook currently has the focus,
regardless of how many windows there are open for the workbook.

You can refer by name to stuff regardless of whether it is active or in
focus etc:
Workbooks("ship.xls").Worksheets("Fred").range("a1:z99")

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Mystif" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> (Originally posted in a VB forum, slightly revised. It was recommended
> I post it here - I agree.)
>
> The following includes many details of how I arrived where I am now.
> While not directly relevant to the question at hand I hope it may help
> others who may be struggling with similar issues.
>
> For anyone who would like to help, but wishes to skip the history
> please skip down to the line of asterisks.
>
> I have a project which has been consuming way too much time, of late,
> fixing errors.
>
> Originally all of the drafters where I work used an Excel file
> (Ship.xls), stored on the network, as a template for creating a file
> they all use - one per "job."
>
> This meant that all changes to correct pre-existing errors in code
> needed to be applied individually to a VERY large number of files.
>
> I fixed this by placing each module (mod) in its own file on the
> server and every time Ship.xls was opened on a users PC it deleted all
> of the mods from itself and copied in the mods from the server. This
> occurs for both the template AND any resulting files, as there are a
> lot of changes being made right now.
>
> This worked well, and was quite fast!
>
> Then, one day, it just plain stopped working. It would infinite loop
> on one mod, always the same one on any given machine, but not
> necessarily the same one on different machines... That is to say, PC
> "1" would infinite loop (IL) on mod "K", while PC "12" would never IL
> on mod "K" but would on mod "B"... and so on. This happened
> spontaneously (no changes to any code for two weeks prior) and
> affected ALL the PCs, starting on the same day.
>
> A file compare of the code which was not working with the code that
> was showed no discrepancies. Replacing the template file with the
> backup file had no effect, but after a resulting file was created I
> was able to overwrite the code in ThisWorkbook of the resulting file
> with the code from the backup of the template - then everything
> functioned normally.
>
> The end result is that almost four months after making this change I
> nearly had to abandon it - enter XLA files.
>
> With XLAs all my mods would once again be in an Excel file, but not
> the one the users make use of as a template. However, it did, like my
> earlier attempt, reside on the server.
>
> It is NOT flawless...
>
> For example: in a mod in the XLA I make use of
> "Windows(strShipFileName).Activate". This promptly stopped working.
>
> It seemed that I could activate any window I liked except the one that
> held Ship.xls. Windows("ship.out").Activate worked fine,
> Windows("cDet.shp").Activate worked without issue.
>
> One interesting note was that I discovered that Hiding/not hiding
> extensions effected this line of code... now that it was not running
> from a mod inside Ship.xls.
>
> After adding code to check the registry to confirm the hiding/un
> hiding of extensions the code "Windows(strShipFileName).Activate"
> resolved to "Windows("Ship").Activate" or
> "Windows("Ship.xls").Activate" and kind'a worked. This worked for
> most PCs, but two just would NOT work. I even reversed the registry
> settings for extensions on these machines and still no effect.
>
> The solution to this problem was to do a SaveAS prior to any other
> file manipulation. For some reason the machines that cannot see
> "Ship" or "Ship.xls" can see "08110hShip(.xls)" just fine.
> ("08110hShip" was what the file in this example would have been saved
> to at the completion of a series of automated routines, instead I just
> save to the new name prior to automation, then save again, upon
> successful completion of said automation.)
>
>
> ************************************************
> One last problem needs solving now, and I do not know how to do it.
>
> As long as any ship file is open (template and/or multiple completed
> ones) I need the xla to be open, but when the last ship file closes I
> need the xla to close. The reason is that much of the code in the XLA
> is used to manipulate these files, to run calculations, after the
> aforementioned automation completes, and while the user makes changes,
> prints, etc.
>
> If the XLA remains open then in order for any changes that I may make
> to be seen EVERY user MUST exit Excel completely and restart Excel.
>
> This is NOT user friendly, as most of these users do not reboot often
> and only rarely exit Excel as it is the one application they KNOW they
> will be using daily.
>
> This means that corrections to formula(s) and changes to VBA code can
> go unnoticed by some users for days, even weeks.
>
> I need Excel on the users machine to make use of the changes the next
> day, and on occation after lunch.
>
> Thank you.