J
JingleRock
Excel 2003 on a LAN:
I have VBA code stored in two .xlA files (neither is installed as an
'AddIn' in Excel): one macro fires at 9 am and the other fires at 5 pm.
The 5 pm macro imports data from an accounting server and updates two
..xlS data files and saves them using constant filenames (the .xlS files
contain zero VBA code). The 9 am macro, using the preceding day's data
files plus data imported from another server, crunches all the data and
sends out e-mails based on the values in specified cells. All of this
usually works.
However, there has occasionally been a problem with a macro crashing (a
pop-up error message indicating that a 'read-only' file cannot be
accessed <-- this really means that it cannot be saved). Of course,
this only occurs on the .xlS files since the .xlA files are never saved
(when the macros are executed). However, when revising code in an .xlA
file and subsequently attempting to save such file, I occasionally get
the same message; this is true even though the .xlA macro will run just
fine when the .xlA file is opened on an unintended 'read-only' basis
(regardless of the setting 'Application.DisplayAlerts=T/F').
I am currently in a testing mode; my ultimate goal is to locate these
macros and data files on an Autosys server -- therefore, zero pop-ups.
I am not sure what causes the above sickness, but here are the
symptons: the .xlS file or the .xlA file is "tied up" on my network
login, even though when I go into 'Windows Task Manager - Processes',
EXCEL.EXE is not listed; also, no change in "tied up" status when
powering the machine down and re-powering and re-logging-in to our LAN;
the file can be copied but it cannot be deleted. The only solution has
been to call our LAN Administrator and request that the "tie up" be
severed; he does so by making a few mouse clicks, I guess. (I think he
may be getting tired of my requests.)
I think this situation may even be caused by "stepping through" a
macro, allowing the macro to open various files, and then shutting the
macro down without allowing the macro to close files and shut-down
Excel (is it good form to close all files prior to shutting-down Excel
in a macro?).
I have coded another macro that opens each of these files, saves each
of these files, and then closes each of these files; currently, the
macro sends me an e-mail if all procedures are completed OK and it
sends me an e-mail if one or more of the files could not be saved,
including an error message and the name(s) of the files that could not
be saved. I have scheduled this macro to fire at 9:10 am and 5:10 pm
(shortly after each of the other two macros).
Can anyone shed any light on this situation?
Thanks in advance,
One last quirk: The 5:10 pm macro (see above) told me the other nite
that one of the .xlA files could not be saved. Of course, the next
morning, the code in that file ran just fine. However, the 9:10 am
macro (see above) told me that all of the files were opened, saved, and
closed successfully. (I did not have the time to call our LAN
Administrator and request that it be "untied".) Very strange
behavior.
I have VBA code stored in two .xlA files (neither is installed as an
'AddIn' in Excel): one macro fires at 9 am and the other fires at 5 pm.
The 5 pm macro imports data from an accounting server and updates two
..xlS data files and saves them using constant filenames (the .xlS files
contain zero VBA code). The 9 am macro, using the preceding day's data
files plus data imported from another server, crunches all the data and
sends out e-mails based on the values in specified cells. All of this
usually works.
However, there has occasionally been a problem with a macro crashing (a
pop-up error message indicating that a 'read-only' file cannot be
accessed <-- this really means that it cannot be saved). Of course,
this only occurs on the .xlS files since the .xlA files are never saved
(when the macros are executed). However, when revising code in an .xlA
file and subsequently attempting to save such file, I occasionally get
the same message; this is true even though the .xlA macro will run just
fine when the .xlA file is opened on an unintended 'read-only' basis
(regardless of the setting 'Application.DisplayAlerts=T/F').
I am currently in a testing mode; my ultimate goal is to locate these
macros and data files on an Autosys server -- therefore, zero pop-ups.
I am not sure what causes the above sickness, but here are the
symptons: the .xlS file or the .xlA file is "tied up" on my network
login, even though when I go into 'Windows Task Manager - Processes',
EXCEL.EXE is not listed; also, no change in "tied up" status when
powering the machine down and re-powering and re-logging-in to our LAN;
the file can be copied but it cannot be deleted. The only solution has
been to call our LAN Administrator and request that the "tie up" be
severed; he does so by making a few mouse clicks, I guess. (I think he
may be getting tired of my requests.)
I think this situation may even be caused by "stepping through" a
macro, allowing the macro to open various files, and then shutting the
macro down without allowing the macro to close files and shut-down
Excel (is it good form to close all files prior to shutting-down Excel
in a macro?).
I have coded another macro that opens each of these files, saves each
of these files, and then closes each of these files; currently, the
macro sends me an e-mail if all procedures are completed OK and it
sends me an e-mail if one or more of the files could not be saved,
including an error message and the name(s) of the files that could not
be saved. I have scheduled this macro to fire at 9:10 am and 5:10 pm
(shortly after each of the other two macros).
Can anyone shed any light on this situation?
Thanks in advance,
One last quirk: The 5:10 pm macro (see above) told me the other nite
that one of the .xlA files could not be saved. Of course, the next
morning, the code in that file ran just fine. However, the 9:10 am
macro (see above) told me that all of the files were opened, saved, and
closed successfully. (I did not have the time to call our LAN
Administrator and request that it be "untied".) Very strange
behavior.