From: Charles Roper <charles.roper@...>
Date: 2005-10-29T06:52:18+09:00
Subject: Re: Oldest Ruby hacker?

On 28/10/05, Terje Tjervaag <flecktone@gmail.com> wrote:
> It's a simple trick but in case you don't know about it, turn off
> screen updating while you're doing your routine using:
> excel.screenupdating = false
> ..where excel is your reference to the application (e.g.
> WIN32OLE::new('excel/application'))
>
> Remember to turn it back on with '= true' when you're done or excel
> won't show your changes.

Terje,

Many thanks for the suggestion. I'm actually doing something similar
and hiding the whole Excel app:

excel.Visible = false

I also tried your suggestion, but reading and writing to cells was still slow.

However, I found a solution. Iterating over a range of cells and
reading or writing to them individually is what was causing the
bottleneck, so I experimented with reading whole ranges into an array
like so:

my_range = "$A$2:$A$8000"
my_array = sheet.range(my_range).Value

Not only is this more terse, it's also much, much faster. Using my
8000 row sheet, it reads the range into an array in about a 1.5
seconds, as opposed to the iterator taking over 1 minute and 45
seconds.

I was also delighted to discover that this works for writing back to a
range of cells too:

sheet.range(my_range) = my_array

Beautiful.

--
Charles Roper
www.charlesroper.co.uk