L
Laszlo
I have an application I inherited that's programmed in
Excel VBA (not my idea - over 5K lines of code) that has
an update function and a file copy function. I consider
myself rather astute and well-versed in VBA, but am a
little rusty - but only a little. Anyway, I have a problem
pulling what little hair I have left out.
Update function Theory: The update function checks for a
specifically named xls file, opens the workbook if exists,
makes this active, copies a module to a .bas file,
activates original workbook, closes previous workbook,
imports module from .bas file, kills .bas file, runs code
in module, removes module, and goes on with rest of code.
This update function is the first thing that happens upon
the workbook getting opened.
File Copy Theory: A value in a cell determines if current
file is one that has been opened since being deployed to
end-users. If not opened before, open end-user's XLS file
(filename is known and expected to be in same path as
current workbook). [I notice that there is no
workbook.activate call after the open...as it was in
Update function] Then, two sheets are copied from the
user's workbook to the new workbook. The users workbook is
then renamed with an extension change to '.TMP' using
the 'saveas' workbook function and then workbook.close
(this close occurs BEFORE workbook running this code is
activated). The new workbook is activated & renamed as the
user's original workbook using workbook.saveas then the
original filename is killed....
Problem - once everything happens as expected - there
seems to be residual copies in memory of the user's
original and the new file where things do not behave as
expected - forms displaying information where there should
be nothing, etc.
Is there a method to ensure a working file that is saved
as a renamed file and the previous filename killed while
the routine calling the kill is from the file being
killed - even though the saveas renamed and activated the
new file as the active file - sounds confusing??? (hmmm...
perhaps opening and activating the saveas file
explicitly?) Is there a way of ensuring memory of all
unwanted previously opened and now closed and killed
workbooks are purged? Curiously, I have found that double-
clicking the .TMP file will open Excel - even though there
is no association of .TMP to Excel. If I reboot, then
double-click on the .TMP file, Excel does not open and I
get the expected "Open with..." dialog.
Antone else have a similar experience? Is there a simpler
solution? (code available if requested - I wanted to keep
this question brief at first...)
Thanks,
Laszlo
Excel VBA (not my idea - over 5K lines of code) that has
an update function and a file copy function. I consider
myself rather astute and well-versed in VBA, but am a
little rusty - but only a little. Anyway, I have a problem
pulling what little hair I have left out.
Update function Theory: The update function checks for a
specifically named xls file, opens the workbook if exists,
makes this active, copies a module to a .bas file,
activates original workbook, closes previous workbook,
imports module from .bas file, kills .bas file, runs code
in module, removes module, and goes on with rest of code.
This update function is the first thing that happens upon
the workbook getting opened.
File Copy Theory: A value in a cell determines if current
file is one that has been opened since being deployed to
end-users. If not opened before, open end-user's XLS file
(filename is known and expected to be in same path as
current workbook). [I notice that there is no
workbook.activate call after the open...as it was in
Update function] Then, two sheets are copied from the
user's workbook to the new workbook. The users workbook is
then renamed with an extension change to '.TMP' using
the 'saveas' workbook function and then workbook.close
(this close occurs BEFORE workbook running this code is
activated). The new workbook is activated & renamed as the
user's original workbook using workbook.saveas then the
original filename is killed....
Problem - once everything happens as expected - there
seems to be residual copies in memory of the user's
original and the new file where things do not behave as
expected - forms displaying information where there should
be nothing, etc.
Is there a method to ensure a working file that is saved
as a renamed file and the previous filename killed while
the routine calling the kill is from the file being
killed - even though the saveas renamed and activated the
new file as the active file - sounds confusing??? (hmmm...
perhaps opening and activating the saveas file
explicitly?) Is there a way of ensuring memory of all
unwanted previously opened and now closed and killed
workbooks are purged? Curiously, I have found that double-
clicking the .TMP file will open Excel - even though there
is no association of .TMP to Excel. If I reboot, then
double-click on the .TMP file, Excel does not open and I
get the expected "Open with..." dialog.
Antone else have a similar experience? Is there a simpler
solution? (code available if requested - I wanted to keep
this question brief at first...)
Thanks,
Laszlo