G
gifer
OK, these may not be bugs, but they are varments if I created them, or if
they are a virus.LOL
I have a file that is using "Get External Data" to retrieve data from an
Intranet database.
I use one sheet to hold the data from the web site. On another sheet, I have
my formulas referencing the first sheet. On a final sheet, I basically
display what ever it is that that data represents in easy to read terms,
formats, etc.
Two problems can occur using the Get External Data function.
Excel prompts a message box requiring acknowledgment when ever the external
data is not avaliable. The external data is not avaliable for different
reasons- but most likely it is because the database was taken offlline to
repair data. Other times, the database may be fine, but traffic or something
causes Excel to think it can't find the address.
Regardless, the response that Excel takes requires human intervention to
resolve- which is either to hit the 'Esc' button (or hit "OK") so Excel will
attempt to pole for data again, or shutdown the file and launch it again. If
hitting Esc does not work, the second option is used.
If Excel responds with a message that it can't return the query because it
can't find the database, then is there a macro or somehting that I can set
to "hit OK" or the Esc button automatically, or something so that Excel will
continue to try multiple times to access the external address? That would
allow the program to begin running as soon as the database was back up.
Otherwise, Excel waits on me to hit Esc, which could be minutes, or days if
I am away from the office. In either case, I will not capture data while it
is waiting on someone to hit the Esc key.
Another problem using the "Get External Data" function is very, VERY weird,
but occurs about 30% of the time with Excel files: I have not figured out
what is triggering this, but something causes my formulas to be over written
with erronous cell locations! In other words, my origional file has a cell
that states, "=A7*A$4+ExternalDataSheet2!$G5". Something causes the A7, or
the A$4, or the $G5 to be over written by some other Excel cell reference.
And, it is always consistant with both the cells that it modifies, and what
it modifies them too! The result is $REF!. At least I am lucky that the cell
reference that this bug creates is a non existant reference allowing me to
realize there is a problem. This occurs on my laptop, my desktop, my shop
floor server, and has occures with different programs using the "Get
External Data" function going on three years now. The only way I can recover
from this error, is to close the file, and answer "no" to the do you want to
save the file question. BECAUSE if I do save the file, then the bogus
references are saved and thus the file is no good. Then saying "no" causes
me to loose data from the previous hours which up until the bug, was
captured in other various cell as good data.
Has anyone seen parts of their cell formulas being changed? If anyone knows
how to address either of my questions, it would be a great help.
Thanks,
John
they are a virus.LOL
I have a file that is using "Get External Data" to retrieve data from an
Intranet database.
I use one sheet to hold the data from the web site. On another sheet, I have
my formulas referencing the first sheet. On a final sheet, I basically
display what ever it is that that data represents in easy to read terms,
formats, etc.
Two problems can occur using the Get External Data function.
Excel prompts a message box requiring acknowledgment when ever the external
data is not avaliable. The external data is not avaliable for different
reasons- but most likely it is because the database was taken offlline to
repair data. Other times, the database may be fine, but traffic or something
causes Excel to think it can't find the address.
Regardless, the response that Excel takes requires human intervention to
resolve- which is either to hit the 'Esc' button (or hit "OK") so Excel will
attempt to pole for data again, or shutdown the file and launch it again. If
hitting Esc does not work, the second option is used.
If Excel responds with a message that it can't return the query because it
can't find the database, then is there a macro or somehting that I can set
to "hit OK" or the Esc button automatically, or something so that Excel will
continue to try multiple times to access the external address? That would
allow the program to begin running as soon as the database was back up.
Otherwise, Excel waits on me to hit Esc, which could be minutes, or days if
I am away from the office. In either case, I will not capture data while it
is waiting on someone to hit the Esc key.
Another problem using the "Get External Data" function is very, VERY weird,
but occurs about 30% of the time with Excel files: I have not figured out
what is triggering this, but something causes my formulas to be over written
with erronous cell locations! In other words, my origional file has a cell
that states, "=A7*A$4+ExternalDataSheet2!$G5". Something causes the A7, or
the A$4, or the $G5 to be over written by some other Excel cell reference.
And, it is always consistant with both the cells that it modifies, and what
it modifies them too! The result is $REF!. At least I am lucky that the cell
reference that this bug creates is a non existant reference allowing me to
realize there is a problem. This occurs on my laptop, my desktop, my shop
floor server, and has occures with different programs using the "Get
External Data" function going on three years now. The only way I can recover
from this error, is to close the file, and answer "no" to the do you want to
save the file question. BECAUSE if I do save the file, then the bogus
references are saved and thus the file is no good. Then saying "no" causes
me to loose data from the previous hours which up until the bug, was
captured in other various cell as good data.
Has anyone seen parts of their cell formulas being changed? If anyone knows
how to address either of my questions, it would be a great help.
Thanks,
John