Re: Parsing through Excel Columns
Re: Parsing through Excel Columns
- Subject: Re: Parsing through Excel Columns
- From: Stan Cleveland <email@hidden>
- Date: Wed, 21 Nov 2007 12:46:31 -0800
- Thread-topic: Parsing through Excel Columns
On 11/21/07 7:29 AM, Justin Laden wrote:
> I'm having trouble figuring out how to parse through Excel columns
> with AppleScript.
>
> First, I will need to parse through a column of data in Excel, pulling
> out print que numbers, copying them, and them pasting them into a new
> column.
>
> Second, I will then need to go through another column that contains
> the paper size of each print job, Based on the cells in that column,
> a new column will be populated with the price (I can hard code the
> price into the program or populate this column with a formula,
> incrementing the cells in the formula). This price is also connected
> to a column with the quantity.
>
> Any help with this problem is appreciated.
I'd suggest grabbing data from a number of rows and columns all at once,
processing that data (a list of lists), then pushing the data back to Excel
all at once.
The following examples use Excel 2004. Watch out for text wrapping.
Assuming your data are simple values, grab data from the range:
tell application "Microsoft Excel"
set rangeData to value of range "A1:C3"
--> {{375.0, 375.0, ""}, {326.45, 307.88, ""}, {48.55, 67.12, ""}}
end tell
Use AppleScript to modify the resulting list of lists. (Each sub-list
represents the cells of one row of data in the range.)
repeat with thisRow in rangeData
set {actual, budgeted, over_under} to thisRow
if actual > budgeted then
set item 3 of thisRow to "over"
else if actual < budgeted then
set item 3 of thisRow to "under"
else
set item e of thisRow to "okay"
end if
end repeat
Then just write the modified list of lists back to Excel:
tell application "Microsoft Excel"
set value of range "A1:C3" to rangeData
end tell
If your data uses formulas, rather than values, you have much more work to
do. Making sense of the data is up to you, but here's how to read and write
formulas:
tell application "Microsoft Excel"
set rangeData to formula of range "A1:C3"
set formula of range "A1:C3" to rangeData
end
I hope that makes sense!
Stan C.
_______________________________________________
Do not post admin requests to the list. They will be ignored.
AppleScript-Users mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
Archives: http://lists.apple.com/archives/applescript-users
This email sent to email@hidden