No problem, quite straightforward as you can see below.
Open the entire script, including the three handlers at the end, have a Worksheet open with some data in it starting at “A1” such as:
"DATE " “CAPACITY” “FREE SPACE” “PERCENTAGE FREE” “NODES”
5/23/10 2235.51 1119.693285 49.91% 70.72085723
5/24/10 2235.51 1396.145357 37.55% 2.619078576
5/25/10 2235.51 490.0714946 78.08% 18.24029273
5/25/11 2236.51 1448.154329 35.25% 72.23326657 etc. etc.
Note that in the first line of the script you need to enter the title of the specific column you want, say “PERCENTAGE FREE”, and a chart will be created, with the value axis on the right ( my preference) and with a maximum and minimum scale adjusted to the max and min values in your data
–::HardCoded Input, we want to chart ONLY the data in the column that corresponds to this header label “5yr Br”
set ourRequiredDataByHeaderValue to “NODES”
–:: Start of Excel Chart Creation
tell application “Microsoft Excel”
–dynamically grab the data in columns
tell active sheet
–::Find the headers in the first row:resize range A1 to last used cell of row 1
set rHeaders to get resize range “A1” column size (first column index of (get end (cell (count columns) of row 1) direction toward the left))
set rHeaderValues to value of rHeaders --pass the values from a range reference to a list
--::Now find the number of data rows and set rData to whole range from row 2
set rData to get resize range ("A2:A" & first row index of (get end range ("A" & (count rows)) direction toward the top)) column size (count (columns of rHeaders))
--note: rData returns a range reference
--::Check that you have some data to chart
if (count (columns of rHeaders)) = 1 or (count (rows of rData)) = 1 then return -- no data, so quit
--::Create chart object specifiying its dimensions
set oChartObj to make new chart object at end ¬
with properties {top:75, left position:1, height:400.0, width:500.0}
--::Now build the Chart within the ChartObject
tell chart of oChartObj
--::Define the Chart type..here we go for a line chart
set chart type to line chart
-- add each series
set lowValue to {}
--Define the position of our Required Data By Header Value within the list of Headers
set ourRequiredColumn to my list_position(ourRequiredDataByHeaderValue, item 1 of (rHeaderValues)) --rHeaderValues is a list of a list
-- returns an integer value eg ourRequiredColumn --4
repeat with i from ourRequiredColumn to ourRequiredColumn
set newSeries to make new series at end ¬
with properties {series values:¬
(column i of rData), xvalues:¬
(column 1 of rData), name:¬
(value of cell i of rHeaders)}
-- need to specify _value_ (no default property)
--::To check the high and low values in our data range for scaling purposes
set theDataRange to value of column i of rData
set theRangeHigh to my highest_number(theDataRange)
set theRangeLow to my lowest_number(theDataRange)
-- copy theRangeHigh to the end of highValue
copy theRangeLow to the end of lowValue
end repeat
set myScaleMax to theRangeHigh
set myScaleMin to my lowest_number(lowValue)
--::Add Titles and Format Chart and Axes
set has title to true
tell its chart title -- needs 'its !!
set caption to "Chart of column header you defined in line 1 of this script.."
tell font object
set name to "Calibri"
set font size to 16
set bold to true
end tell
end tell
set categoryAxis to get axis axis type category axis ¬
which axis primary axis
tell categoryAxis
set has title to true
--set has title to false
try
set crosses at to maximum scale
end try
try
tell its axis title -- needs 'its' !!
set caption to "X Values"
tell font object
set name to "Arial"
set font size to 10
set bold to true
end tell
end tell
end try
end tell
set valueAxis to get axis axis type value axis ¬
which axis primary axis
tell valueAxis
--set has title to true
set major unit is auto to true -- or false
set major tick mark to tick mark none --
(*options are:tick mark cross / tick mark inside / tick mark none / tick mark outside*)
set minor tick mark to tick mark none
set tick label position to tick label position high
(*this places value labels (ie. the scale) outside the chart*)
(*options are: tick label position high/tick label position low/tick label position next to axis/tick label position none*)
set maximum scale to myScaleMax as real
set minimum scale to myScaleMin as real
set has title to false
try
tell its axis title -- needs 'its' !!
set caption to "Y Values"
tell font object
set name to "Arial"
set font size to 10
set bold to true
end tell
end tell
end try
end tell
--::Format Plot area
tell plot area object
tell its border -- needs its
set color to {0, 0, 255}
-- will this work?, if not:
--set its color index to 5
set line weight to 1
set its line style to continuous
-- no transparency property in AppleScript
--set transparency to 0
end tell
tell its chart fill format object
set visible to true
--set fore color to {150, 200, 255}
--can't set color
--set transparency to 0.5
set foreground scheme color to 1 --<<<original value 23 = pinkie; 5 is sky blue
set transparency to 0.8 -- the same color
end tell
end tell
--::Format legend :
set has legend to false --can be true or false
try
tell legend object
--set its position to legend position right
-- set its position to legend position top
set its position to legend position bottom
tell its chart fill format object
set visible to true
--set fore color to {150, 200, 255}
--can't
--set transparency to 0.5
set foreground scheme color to 23
set transparency to 0.8 -- the same color
end tell
tell its font object
set name to "Arial"
set font size to 10
set its font color index to 5
end tell
end tell
end try
end tell
end tell
end tell
log myScaleMax
log myScaleMin
on list_position(this_item, this_list)
repeat with i from 1 to the count of this_list
if item i of this_list is this_item then return i
end repeat
return 0
end list_position
on highest_number(values_list)
set the high_amount to “”
repeat with i from 1 to the count of the values_list
set this_item to item i of the values_list
set the item_class to the class of this_item
if the item_class is in {integer, real} then
if the high_amount is “” then
set the high_amount to this_item
else if this_item is greater than the high_amount then
set the high_amount to item i of the values_list
end if
else if the item_class is list then
set the high_value to highest_number(this_item) --note recursion
if the the high_value is greater than the high_amount then ¬
set the high_amount to the high_value
end if
end repeat
return the high_amount
end highest_number
on lowest_number(values_list)
set the low_amount to item 1 of values_list
repeat with i from 1 to the count of the values_list
set this_item to item i of the values_list
set the item_class to the class of this_item
if the item_class is in {integer, real} then
if the low_amount is “” then
set the low_amount to this_item
else if this_item < the low_amount then
set the low_amount to item i of the values_list
end if
else if the item_class is list then
set the low_value to lowest_number(this_item) --note recursion
if the the low_value < the low_amount then ¬
set the low_amount to the low_value
end if
end repeat
return the low_amount
end lowest_number
Let us know if this works for you…You may note that the last two handlers use recursion in their method. I was concerned that a stack overflow could occur after around 273 items in a list, but I have tested the script to 400 row values without any problems. Perhaps other members in the group may want to comment on this…?
