Re: Scripting in Excel -> search different words in column and color them
Re: Scripting in Excel -> search different words in column and color them
- Subject: Re: Scripting in Excel -> search different words in column and color them
- From: Kai Edwards <email@hidden>
- Date: Sun, 27 Oct 2002 15:18:36 +0000
on 27/10/02 3:12 pm, I wrote:
>
It seemed the key to better performance would be to perform these
>
time-critical operations in bulk - rather than individually.
>
>
To avoid message length difficulties, I'll send separately some details of my
>
efforts to achieve this - together with a sample script and some further
>
performance comparisons.
The following variation starts with a single call to Excel - to get the
values of _all_ cells in the specified range.
Then, entirely in AS, a cell reference list compiled (but comprised of only
those cells that match the comparison criteria).
Finally, Excel is called to carry out the formatting operation - again in
just one* hit.
* Well, it _would_ have been one - if a limit to the number of cell
references hadn't been encountered. (Sometimes Excel seems to choke on
numerous references, other times it completes only part of the job and
quietly ignores any remaining cells.) So, in case the limit is exceeded, the
script's 'chunk' handler breaks up the data for Excel to format in batches.
(Slightly slower than one shot - but still much faster than formatting
individually.)
Here's the resulting script:
---------------------------------------------------------------------
tell application "Microsoft Excel" to set valList to Range [NO BREAK]
("R1C1:R" & ActiveSheet's UsedRange's last Row's Row & "C" & 5)'s Value
set {foundList, rowTot, colTot} to {{}, [NO BREAK]
count valList, count valList's item 1}
repeat with rowNum from 1 to rowTot
set rowVal to valList's item rowNum
repeat with colNum from 1 to colTot
if rowVal's item colNum is in {"missing", "empty", false} [NO BREAK]
then set foundList's end to "R" & rowNum & "C" & colNum
end repeat
end repeat
if foundList = {} then return
set {tid, text item delimiters} to {text item delimiters, ","}
set {foundStr, text item delimiters} to {foundList as string, tid}
repeat with theChunk in chunk(foundStr)
tell application "Microsoft Excel" to set {Range theChunk's [NO BREAK]
Font's Bold, Range theChunk's Font's ColorIndex} to {true, 3}
end repeat
to chunk(str)
set lim to 256
if str's length < lim then return str as list
set {tid, text item delimiters} to {text item delimiters, ","}
set {chunkEnd, text item delimiters} to [NO BREAK]
{lim - 1 - (str's text 1 thru lim's text item -1's length), tid}
(str's text 1 thru chunkEnd as list) & [NO BREAK]
chunk(str's text (chunkEnd + 2) thru end)
end chunk
---------------------------------------------------------------------
As speed comparisons go, this turned out to be generally much faster than
both previous methods. Compared with the 'cell-by-cell' approach, it was
consistently between 8 to 10 times faster.
In most cases, it also outperformed any find-and-format routine in Excel.
This is how it compared with the 'find-in-Excel' method:
------------- ----------------
proportion of 'batch-calls'
found cells speed difference
------------- ----------------
100% + 1668%
50% + 1107%
25% + 760%
20% + 641%
15% + 511%
10% + 349%
5% + 155%
0% - 83%
-------------------------------
So it was only when there were no cells (or a negligible number of them) to
be found, that this approach lagged a little behind 'find-in-Excel'. (And
since fewer found cells mean shorter delays generally, I'm not too unhappy
about that.)
I thought these findings might be worth sharing in case anyone encounters a
similar situation in future ( - or perhaps they might elicit an even better
way altogether!) ;-)
Kai
--
email@hidden
email@hidden
_______________________________________________
applescript-users mailing list | email@hidden
Help/Unsubscribe/Archives:
http://www.lists.apple.com/mailman/listinfo/applescript-users
Do not post admin requests to the list. They will be ignored.