Hi all,
I have this AppleScript (below). It basically takes values from some “.ocw” files from Finder, then puts those values into Excel. I also want Excel to create graphs from each “column pair” after it has inputted them (see below for the graph chunk). The whole script works (though feel free to suggest refinements - I get the feeling it’s not very elegant 
) apart from the fact I can’t specify a reference for the series for the graph!
As I see it, the problem with the reference lies with the fact that Excel wants the range relatively (“A1:B6”) or absolutely (“$A$1:$B$6”), however I need to specify the range algebraically (i.e the column with a number not a letter) in order for the script to continue ad nauseum. Unfortunately, when I try something like:
tell application "Microsoft Excel"
tell active sheet
set theRange to range {column 1, cell 1}
end tell
end tell
OR
tell application "Microsoft Excel"
tell active sheet
set theRange to {column 1, cell 1}
set theRange2 to {column 2, cell 6}
set the actualRange to (theRange & ":" & theRange2)
end tell
end tell
It doesn’t work. Please tell me where I’m going wrong. It’s been driving me crazy. I just can’t figure out how to do it. Also, please note, I must be able to reference the range algebraically! By the way, I’m using Excel 2008.
Thanks guys! 
Also, in case you want/need them, here’s a shortened sample of the files I’m using: http://kttns.org/vlowf
[b]The Script[/b]
set AppleScript's text item delimiters to ""
set tid to AppleScript's text item delimiters
set i to 1 -- Column variable.
set x to 1 -- File variable.
set newLine to ASCII character 10
set theDir to (choose folder with prompt "Pick the folder containing the .OCW files you would like copied into Excel:") as string
set theDir2 to alias theDir
tell application "Finder"
set fileList to (get every item in theDir2 whose name ends with ".ocw") -- Getting all files.
set sortedList to (sort (items of fileList) as alias list by name) -- Making alphabetical.
end tell
set amountofFiles to the number of items of sortedList
repeat while x is less than or equal to amountofFiles -- Setting initial repeat loop to make sure all files in the folder are processed.
set n to 1 -- Items of file variable.
set theName to (name of item x) of sortedList
tell application "Microsoft Excel" -- Setting the first cell of the first column of the column pair to the name of file.
set value of cell 1 of column (((i + i) - 1) as integer) of active sheet to theName
end tell
set thePath to item x of sortedList
set theFile to thePath as alias
set theData to (read theFile as «class utf8» from 10) -- There's some spaces and an apostrophe I don't want putting into Excel.
set valuePairs to every paragraph of theData
set theValues to every item of valuePairs
set theAmount to the number of items of valuePairs
set AppleScript's text item delimiters to space
repeat while n is less than or equal to theAmount -- Making sure all pairs of values of the values are processed.
set onePair to the item n of theValues
set valueItems to text items of onePair
if number of items of valueItems is equal to 3 then -- Depending how the numbers are structured they leave behind more or less "" meaning a and b must be set differently.
set a to item 1 of valueItems
set b to item 2 of valueItems
else if number of items of valueItems is equal to 5 then
set a to item 3 of valueItems
set b to item 4 of valueItems
else if number of items of valueItems is equal to 6 then
set a to item 3 of valueItems
set b to item 5 of valueItems
end if
tell application "Microsoft Excel"
set value of cell (n + 1) of column (((i + i) - 1) as integer) of active sheet to a
set value of cell (n + 1) of column ((i + i) as integer) of active sheet to b
end tell
set n to (n + 1) -- Next value pair.
end repeat
if n is equal to (theAmount + 1) then -- Is this the last value pair of the file? If so, move onto the next file.
set x to (x + 1)
set i to (i + 1) -- Next value pair will go into the next pair of columns in Excel
end if
if ((x - 1) mod 4) is equal to 0 then -- If there has been four value pairs inserted into Excel, then leave a gap (I want scans 1, 2, 3, and 4 together).
set i to (i + 1) -- Leaves the gap.
end if
-- GRAPH CHUNK GOES HERE
end repeat
set AppleScript's text item delimiters to tid
beep 2
tell application "Microsoft Excel"
display dialog "Done!" with icon note
end tell
The Graph Chunk - (basically C/P’d from the MS Dev site)
tell application "Microsoft Excel"
--Create chart object
set oChartObj to make new chart object at end ¬
with properties {left position:400, top:100, width:500, height:400} ¬
--Now build the Chart within the ChartObject
tell chart of oChartObj
set chart type to xy scatter smooth -- define chart type
-- add each series
set newSeries to make new series at end ¬
with properties {series values:¬
(column (i + i)), xvalues:¬ -- If I do this, then Excel errors saying I can't have a series with more than 32, 000 values...
(column ((i + i) - 1)), name:¬ -- I assume it's saying this because it assumes I don't want just the cells with data in from the columns, I want the empty cells too... -_-
(A Name)}
-- need to specify _value_ (no default property)
end repeat
--'Add Titles and Format Chart and Axes
set has title to true
tell its chart title -- needs 'its !!
set caption to "My XY Scatter Chart"
tell font object
set name to "Verdana"
set font size to 12
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
tell its axis title -- needs 'its' !!
set caption to "X Values"
tell font object
set name to "Verdana"
set font size to 10
set bold to true
end tell
end tell
end tell
set valueAxis to get axis axis type value axis ¬
which axis primary axis
tell valueAxis
set has title to true
tell its axis title -- needs 'its' !!
set caption to "Y Values"
tell font object
set name to "Verdana"
set font size to 10
set bold to true
end tell
end tell
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
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 23
set transparency to 0.8 -- the same color
end tell
end tell
--format legend
set has legend to false
end tell
end tell
end tell
Model: MacBook 2,1
AppleScript: 2.0.1
Browser: Firefox 3.5.1
Operating System: Mac OS X (10.5)
That way I can still specify the column algebraically, e.g (cell x of (column (y)), but then coerce them into an absolute range like “$A$1:$B$8”. (The range can also be relative, like in regulus6633’s response).