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: Bjorn Van Blanckenberg <email@hidden>
- Date: Mon, 28 Oct 2002 17:49:52 +0100
Thanks the new version works better and faster but want to ask some more
questions
I have changed a bit to work with value that are smaller than 60
--
repeat with colNum from 1 to colTot
if rowVal's item colNum is in {"missing", "empty", false} [NO BREAK]
or rowVal's item colNum < 60 then set foundList's end [NO BREAK]
To "R" & rowNum & "C" & colNum
end repeat
--
If change the first line to start with the second colomn than
Excel colors the wrong cells
--
tell application "Microsoft Excel" to set valList to Range [NO BREAK]
("R1C2:R" & ActiveSheet's UsedRange's last Row's Row & [NO BREAK]
"C" & 5)'s Value set {foundList, rowTot, colTot} to {{}, [NO BREAK]
count valList, count valList's item 1}
--
It would be nice if the script could start at column 2 thru 5 and skip
column 6 and go on with column 7 and skip column 8 and 9 and again go on
with column 10 thru 11
And if 1 of the value's is found also color the first column
Thanks in advance
--
Oooo.
oooO ( )
( ) ) /
\ ( (_/
\_)
Thanks
Bjorn Van Blanckenberg
>
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
_______________________________________________
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.