your first numbers applescript questions...

Question

Level 5

5,047 points

your first numbers applescript questions...

My full time job is writting vba anc vba for excel to automate manual processes. I am finding it very hard to "get" how applescript does sometihng that I could do in a few lines in VBA...

I am just trying to learn the basics, how to set values in cells. Lets start there.

I have this done up to the commented parts:


tell application "Numbers"
 activate
 set MyDocName to name of document 1
 tell document 1
 tell sheet 1
 tell (make new table)
 set name to "Tester" 
 --not working below here
 --set value of range ("B2") of it to 34
 -- also tried
 --set value of range("b2") to 34
 end tell
 end tell
 end tell
end tell

I can set the name of the table, but then I am stuck trying to set the value of B2.

Hopefully some of you Applescripting gurus can just look at it and know what to do.

Please obi-wans, your my only hope...
Jason

Power Mac Dual 2.0 G5, Mac OS X (10.5.1)

Posted on Jan 8, 2009 2:51 PM

Reply

Answer 1

Best reply

Budgie

Level 4

1,121 points

Jan 8, 2009 4:18 PM in response to jaxjason

Hi jaxjason

nearly, try the below

set value of range "B2" to "34"

Budgie

Message was edited by: Budgie

Reply

Answer 2

jaxjason Author

Level 5

5,047 points

Jan 8, 2009 4:48 PM in response to Budgie

That didn't seem to work.. I finally figured this out somehow.

tell application "Numbers"
 activate
 tell document 1
 tell sheet 1
 set newtable to make new table with properties {name:"Test Table"}
 tell newtable
 set value of cell 3 of row 2 to count of rows
 set background color of cell 3 of row 2 to {0, 65535, 0} as RGB color
 The following also seems to work properly if put in the correct order
 --remove row 1
 --set myRange to range "B1:C2"
 
 --repeat with i from 1 to count of rows
 -- set value of cell i of column 3 to i * 3
 --end repeat
 
 --set selection range to range "a1:c3"
 end tell
 end tell
 end tell
end tell

I found a good site that had some stuff about moving from vba for M$ office to applescript for MS Office. Its not a direct translation from excel script dictionary and the Numbers. Actually they don't look very similar at all, but I was able to gleen some structure from it that seemed to work.

Thanks alot for your suggestion.
Jason

Reply

Answer 3

jaxjason Author

Level 5

5,047 points

Jan 9, 2009 6:08 AM in response to jaxjason

for the curious... Here is the equivalent in VBA:


dim MyDocName as string
mydocname=activeworkbook.name
msgbox mydocname
'There is no equivalent to Count fo rows in XL, every 'table' has 65536 rows
'You cannot make a new table in XL, so the closest thing is to create a new sheet.
sheets.add
activesheet.name="Testtable"
Range("C2").Value=65536
Range("C2").Interior.ColorIndex = 50
Range("C2").Interior.Pattern = xlSolid

took me less than a minute to do the VBA, hours to get the applescript to go right. But now that I "get it". I think that will be reduced greatly.

Thanks to everyone on this board and others (numbers boards specifically), for helping out all the time.
Jason

Reply

Answer 4

hhas

Level 2

199 points

Jan 9, 2009 11:00 AM in response to jaxjason

If you haven't already seen it:

http://www.mactech.com/vba-transition-guide/

Reply

Answer 5

jaxjason Author

Level 5

5,047 points

Jan 9, 2009 12:04 PM in response to hhas

yep, thats what I found earlier... heres another link to someone that had a good example posted for taking a selected range of cells and creating a new slide in Keynote with that range as a bulleted list.

osx hints for creating bullets with numbers range

Thanks alot,
Jason

Reply

Answer 6

KOENIG Yvan

Level 8

41,807 points

Jan 25, 2009 8:30 AM in response to jaxjason

Hi jason

I grabbed the script from osx hints.

It's wrong because it works only if the selection is in table 1 of sheet 1.

The reason is that the filter:

table whose selection range is not missing value

is verified by every table.

_When nothing is selected the 'value' of selection range is not missing value._

Try to run this script with a range selected in table 2 of sheet 2 in a doc containing 3 sheets.
In the log report you will see that for tables where nothing is selected the result is not missing value but nothing at all.


tell application "Numbers"
 tell document 1
 -- DETERMINE THE CURRENT SHEET
 set {currentsheetindex, currenttableindex} to {0, 0}
 repeat with i from 1 to the count of sheets
 tell sheet i
 set x to the count of tables
 if x > 0 then
 repeat with j from 1 to x
 try
 log "j = " & j
 set theRange to (selection range of table j) as text
 log theRange
 on error errMsg number errNum
 log errMsg
 set {currentsheetindex, currenttableindex} to {i, j}
 exit repeat
 end try
 end repeat --table
 end if
 end tell
 if currentsheetindex > 0 then exit repeat
 end repeat -- sheet
 if the currentsheetindex is 0 then error "No sheet has a selected table."
 log {currentsheetindex, currenttableindex}
 
 -- GET THE VALUES OF THE SELECTED CELLS
 tell sheet currentsheetindex to tell table currenttableindex
 set the range_values to the value of cells of the selection range
 end tell -- table of sheet
 end tell -- document 1
end tell -- application

Yvan KOENIG (from FRANCE dimanche 25 janvier 2009 17:30:50)

Reply

Answer 7

jaxjason Author

Level 5

5,047 points

Jan 25, 2009 8:45 AM in response to KOENIG Yvan

thanks Yvan, looks like we are going to have to get a standard set of code samples to handle these situations. maybe post a webpage with "Best Practices" made up for others to look at.

Jason

Reply

Answer 8

Hiroto

Level 5

7,461 points

Jan 26, 2009 3:39 AM in response to KOENIG Yvan

Hello Yvan,

Well, I couldn't resist this.
From what I read about Numbers scripting here and there, I came up with something like the code below. Would you please try this? IF I'm not mistaken, it will shortly return the value of cells of current selection. If it fails, please disregard this.

Hiroto

--CODE (NOT TESTED)
return getSelectionValues()
on getSelectionValues()
script o
property pp : {}
tell application "Numbers"
tell document 1
set pp to selection range of every table of every sheet as list
repeat with p in my pp
set q to (p as list)'s every reference -- list of application objects
if q is not {} then
--return value of cells of (q's item 1) -- # this should work, normally
set specrec to q's item 1 as record -- object specifier record
set c to specrec's every reference's item 1 -- container object: table x of sheet y
return value of cells of selection range of c
end if
end repeat
return {} -- no selection
end tell
end tell
end script
tell o to run
end getSelectionValues
--END OF CODE

Reply