M
Mystif
(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.
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.