Speeding Up Excel

[Chris Kelleher]

In preliminary timing tests, we've determined that, in all likelihood, we
may face a user rebellion when we deliver our first version of our
application. The reason is performance, and the culprit is Excel. We use a
template, so we don't have to "build" a workbook, graphs, etc. from scratch
each time.

We shut off recalculation during the data loading & formula copying
process. Tried to make it invisible, but can't, so the user gets flashing
all over the place. This leads me to believe that calculation may not be as
'shut off' as I think it is.

Slow points:
- Bringing up Excel in the first place
- Loading flat file (ASCII) data into Excel
- Copying formulas within Excel

Trade-off:
- We can eliminate the need to copy formulas in Excel by pre-loading every
one the user could possibly want. This is 3 * 21 * 2,700 = 170,100 cells
worth of not-so-trivial formulas. The current arrangement is to copy only
the number of cells needed for the data being loaded. The penalty for
pre-loading formulas is a slow initial Excel startup (could be resolved by
never closing it, I guess).

Do you know of any way to speed up any of this? How can I verify that
recalculation is indeed shut off when we need it to be?

Thanks for any insights, tips, etc. that you can send along.
Cc
------------------------------
I went to see my doctor. "Doctor, every morning when I get up
and look in the mirror... I feel like throwing up; What's wrong
with me?" He said..."I don't know but your eyesight is perfect."
- Rodney Dangerfield

 

[Chris Kelleher]

Charlie,

>Tried to make it invisible, but can't, so the user gets flashing
>all over the place.

to build up a spreadsheet 'in the dark' I use these statements:

/* create a new Excel Application object */
CREATE "Excel.Application" chExcelApplication.
chExcelApplication:Visible = FALSE.
/*
copy data into cells etc.
...
*/
chExcelApplication:Visible = TRUE.

Regards,
Thorsten