8 Replies Latest reply: Jan 26, 2009 3:39 AM by Hiroto
jaxjason Level 4 Level 4 (3,535 points)
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)
  • Budgie Level 4 Level 4 (1,120 points)
    Hi jaxjason

    nearly, try the below

    set value of range "B2" to "34"




    Budgie

    Message was edited by: Budgie
  • jaxjason Level 4 Level 4 (3,535 points)
    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
  • jaxjason Level 4 Level 4 (3,535 points)
    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
  • hhas Level 2 Level 2 (190 points)
    If you haven't already seen it:

    http://www.mactech.com/vba-transition-guide/
  • jaxjason Level 4 Level 4 (3,535 points)
    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
  • Level 8 Level 8 (41,790 points)
    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)
  • jaxjason Level 4 Level 4 (3,535 points)
    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
  • Hiroto Level 5 Level 5 (6,050 points)
    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