Re: Scripting in Excel
Re: Scripting in Excel
- Subject: Re: Scripting in Excel
- From: Kai Edwards <email@hidden>
- Date: Sun, 03 Nov 2002 23:26:04 +0000
on 3/11/02 11:13 pm, I wrote:
>
...I've thrown together a routine which I believe does what you're now
>
asking. Since it's fairly lengthy (sorry!), I'm posting it separately.
Here it is:
-------------------------------------
set colPref to {2, 3, 4, 5, 7, 10, 11} -- set columns as required
set colList to makeColList from colPref
set {{theRange, rowTot}, valList, foundList} to [NO BREAK]
{getRange from colList, {}, {}}
repeat with theRef in theRange
tell application "Microsoft Excel" to [NO BREAK]
set valList's end to Range theRef's Value
end repeat
repeat with x from 1 to colList's length
set {vList, adj, colTot} to {valList's item x, [NO BREAK]
(colList's item x's item 1) - 1, colList's item x's length}
repeat with r from 1 to rowTot
set {col1Done, rowVal} to {false, vList's item r}
repeat with c from 1 to colTot
set v to rowVal's item c
if v is in {"missing", "empty", false} or [NO BREAK]
(v's class = real and v < 60) then
if not col1Done then
set foundList's end to "R" & r & "C1"
set col1Done to true
end if
set foundList's end to "R" & r & "C" & (c + adj)
end if
end repeat
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
to getRange from colList
tell application "Microsoft Excel" to set [NO BREAK]
lastRow to ActiveSheet's UsedRange's last Row's Row
set refList to {}
repeat with i from 1 to count colList
set c to colList's item i
set refList's end to [NO BREAK]
"R1C" & c's item 1 & ":R" & lastRow & "C" & c's item -1
end repeat
{refList, lastRow}
end getRange
to makeColList from colPref
set colList to {colPref's item 1 as list}
repeat with i from 2 to count colPref
set {c, p} to {colPref's item i, colPref's item (i - 1)}
if c = p + 1 then
set colList's item -1's end to c
else
set colList's end to c as list
end if
end repeat
colList
end makeColList
-------------------------------------
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.