Similar to Norman's response, I also couldn't answer as there's too many
variables to say, such as do you have auto calculation in place. How much
of an area are you formatting? If you using Conditional Formatting, are you
using formulas within it, which causes calculation to take place when in
auto mode. For this reason, you may want to try to have it in manual mode,
if there's a lot of calculations in the file(s).
How are you updating the data? Are you using MS Query or some other means
such as via a third party tool? As for MS Query, I hate it cause it's such
a memory leaker that of the 12 production files that gets it's data updated
to the summary files daily, if I was to use MS Query, it would cause Excel
to crash during the time it's updating the 3rd file, but yet, with the way I
use VBA to do the updating instead, I can run Excel for a couple of weeks
before I would need to reboot the system, and that's also with me doing a
lot of other things on the system too.
MS Query depends on ADO, which ADO within Excel has been documented to be a
memory leaker. I therefore mark MS Query as unavailable during installation
process due to it's more severe ill side effect.
I also update data from a database into Excel, but I never experienced the
slow down that you are claiming. Even in my production files that has
10,000's of range names (note, can't exceed 65536 defined names within a
single workbook else Excel puts the file into repair mode when it's opened,
which then the file is useless after that as a lot of things are lost. Yeah
yeah, spec help file states it's limited to system resources, but it's not
true as I have exceeded the capacity of the 2 byte Count property on the
Names collection object in the past to run into this repair mode issue. But
even then, the file becomes unstable after passing up the 32,768 defined
names give or take a couple, such as pulling up the workbook properties has
been known to go into an infinite loop cycle, and eventually causing Excel
to crash as Excel eats up more and more and more system resources.)
We use ShowCase Strategy Query provided by SPSS to pull data into Excel from
our main DB and outside of some issues, though unrelated to any sort of
slowness issues to Excel, I haven't had too much of a problem updating
Excel. Of course, my knowledge of the query program far exceeds the typical
program user, so I have ran into and uncovered a few bugs in the program,
which the tech support people there has worked with me on these various
issues. It may take them some time to find the issue, which at times, it
seems as though they don't believe what we are saying, but I'm sure they are
mainly doing that to cover themselves as they probably have heard too many
of such type things from too many customers when there really wasn't an
issue, which I can relate to very easily, especially since I have also
written my own production reporting system within Excel using VBA and
userforms.
--
Sincerely,
Ronald R. Dodge, Jr.
Master MOUS 2000