7 Replies Latest reply: Jan 25, 2012 8:50 AM by KOENIG Yvan
adamowens Level 1 Level 1 (0 points)

Hello All,

 

I know this is possible and I am feeling close - I just couldn't find a post that addressed the issue I am having exactly. Essentially I have a client list with 211 business names in it. I am trying to create a script that will scan the first column of my Numbers document and create a folder structure from that list. I found a post on another website that gave me a rough template and this is where I am at:

 

set destinationFolder to "MacHD:/Users//Documents/Work/Customers/:to:folder:"

tell application "Numbers.app"

          set theCells to value of range "A1:A211" of active sheet

end tell

repeat with oneCell in theCells

          tell application "Finder" to make new folder at folder destinationFolder with properties {name:item 1 of oneCell}

end repeat

 

 

When I run this I get "Expected end of line but found class name." and in the result window it display "

error "Can’t get value of range." number -1728 from «class NMCv» of «class NmCR»"

 

I am completely new to this and don't really know how to debug. Any help would be appreciated! Thank you so much!

 

Adam


Applescirpt, Mac OS X (10.7.2)
  • 1. Re: Use Applescript to create folder structure from Numbers document
    red_menace Level 6 Level 6 (14,615 points)

    I don't have Numbers, but the error is saying that it can't get the value of the range, although you should be getting the terms instead of the event codes.  From the scripting dictionary, a document has sheets, sheets have tables, tables have ranges, ranges have cells, and cells do have a value property, so you will need to use a valid descripter, such as value of cells of range "A1:A211" of table 1 of active sheet or something like that.  Also, your destinationFolder path is using both Finder and POSIX delimiters - you need to choose one or the other, for example

        set destinationFolder to (((path to documents folder) as text) & "Work:Customers:to:folder:")

  • 2. Re: Use Applescript to create folder structure from Numbers document
    adamowens Level 1 Level 1 (0 points)

    Thank you for the reply: So I think I have addressed the issues you pointed out but now it's says;

     

    "error "Numbers got an error: Can’t get table 1." number -1728 from table 1"

     

    in the Results window.

     

    Thanks in advance!

  • 3. Re: Use Applescript to create folder structure from Numbers document
    red_menace Level 6 Level 6 (14,615 points)

    There may not be a table 1, I was just guessing from looking at the scripting dictionary.  Usually you can do something like "get tables of active sheet" to see if there are any (or if the syntax is correct), and work your way down.

     

    If you happen to have the Developer Tools installed, there is an Accessibility Inspector application that can be used to look at the object hierarchy.

  • 4. Re: Use Applescript to create folder structure from Numbers document
    Level 8 Level 8 (41,760 points)

    (1) You define a path to a folder but you don't define the path to the Numbers document.

    (2) You fail to tell to Numbers which document it's supposed to treat

    (3) You wrote :

      set theCells to value of range "A1:A211" of active sheet

    But you aren't trying to drive Excel. 'active sheet' means nothing for Numbers.

    The word active doesn't exist in Numbers AppleScript dictionary

     

    (4)  I know that Excel is able to get value of a range but you aren't trying to drive this app.

    The Numbers AppleScript dictionary is clear. Range properties are :

    2012-01-25T12.26.55.jpg

    Only cells have a value property.

    2012-01-25T12.28.28.jpg

     

    --{code}

     

    set destinationFolder to "MacHD:/Users//Documents/Work/Customers/:to:folder:"

    (*

    tell application "Numbers.app"

              set theCells to value of range "A1:A211" of active sheet

    end tell

    repeat with oneCell in theCells

              tell application "Finder" to make new folder at folder destinationFolder with properties {name:item 1 of oneCell}

    end repeat

    *)

     

    set destinationFolder to "" & (path to desktop) & "  dossier"

     

    (*

    Select the range of source cells then run the script *)

     

    (*

    Grab parameters describing the selected cells *)

    set {dName, sName, tName, rowNum1, colNum1, rowNum2, colNum2} to my get_SelParams()

    tell application "Numbers" to tell document dName to tell sheet sName to tell table tName

      (*

    Extract the names from the selected cells *)

              set theNames to {}

              tell column colNum1

                        repeat with r from rowNum1 to rowNum2

                                  copy value of cell r to end of theNames

                        end repeat

              end tell -- column

    end tell -- Numbers

    (*

    I always do my best to use System Events which is faster than the Finder *)

    tell application "System Events"

              repeat with aName in theNames

                        try

      make new folder at end of folder destinationFolder with properties {name:aName as text}

                        end try

              end repeat

    end tell

     

    --=====

    (*

    set { dName, sName, tName,  rowNum1, colNum1, rowNum2, colNum2} to my get_SelParams()

    tell application "Numbers" to tell document dName to tell sheet sName to tell table tName

    *)

    on get_SelParams()

              local d_Name, s_Name, t_Name, row_Num1, col_Num1, row_Num2, col_Num2

              tell application "Numbers" to tell document 1

                        set d_Name to its name

                        set s_Name to ""

                        repeat with i from 1 to the count of sheets

                                  tell sheet i to set maybe to the count of (tables whose selection range is not missing value)

                                  if maybe is not 0 then

                                            set s_Name to name of sheet i

                                            exit repeat

                                  end if -- maybe is not 0

                        end repeat

                        if s_Name is "" then

                                  if my parleAnglais() then

                                            error "No sheet has a selected table embedding at least one selected cell !"

                                  else

                                            error "Aucune feuille ne contient une table ayant au moins une cellule sélectionnée !"

                                  end if

                        end if

                        tell sheet s_Name to tell (first table where selection range is not missing value)

                                  tell selection range

                                            set {top_left, bottom_right} to {name of first cell, name of last cell}

                                  end tell

                                  set t_Name to its name

                                  tell cell top_left to set {row_Num1, col_Num1} to {address of its row, address of its column}

                                  if top_left is bottom_right then

                                            set {row_Num2, col_Num2} to {row_Num1, col_Num1}

                                  else

                                            tell cell bottom_right to set {row_Num2, col_Num2} to {address of its row, address of its column}

                                  end if

                        end tell -- sheet…

                        return {d_Name, s_Name, t_Name, row_Num1, col_Num1, row_Num2, col_Num2}

              end tell -- Numbers

    end get_SelParams

     

    --=====

     

    on parle_anglais()

              return (do shell script "defaults read 'Apple Global Domain' AppleLocale") does not start with "fr_"

    end parle_anglais

     

    --=====

    --{code}

     

    Yvan KOENIG (VALLAURIS, France) mercredi 25 janvier 2012

    iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2

    My Box account  is : http://www.box.com/s/00qnssoyeq2xvc22ra4k

  • 5. Re: Use Applescript to create folder structure from Numbers document
    Level 8 Level 8 (41,760 points)

    Here is an enhanced version which no longer use a loop to extract the selected values.

     

    --{code}

     

    set destinationFolder to "MacHD:/Users//Documents/Work/Customers/:to:folder:"

    (*

    tell application "Numbers.app"

              set theCells to value of range "A1:A211" of active sheet

    end tell

    repeat with oneCell in theCells

              tell application "Finder" to make new folder at folder destinationFolder with properties {name:item 1 of oneCell}

    end repeat

    *)

     

    set destinationFolder to "" & (path to desktop) & "  dossier"

     

    (*

    Select the range of source cells then run the script *)

     

    (*

    Grab parameters describing the selected cells *)

    set {dName, sName, tName, rowNum1, colNum1, rowNum2, colNum2} to my get_SelParams()

    tell application "Numbers" to tell document dName to tell sheet sName to tell table tName

      (*

    Extract the names from the selected cells *)

              tell column colNum1

                        set theNames to value of cells rowNum1 thru rowNum2

              end tell -- column

    end tell -- Numbers

    (*

    I always do my best to use System Events which is faster than the Finder *)

    tell application "System Events"

              repeat with aName in theNames

                        try

      make new folder at end of folder destinationFolder with properties {name:aName as text}

                        end try

              end repeat

    end tell

     

    --=====

    (*

    set { dName, sName, tName,  rowNum1, colNum1, rowNum2, colNum2} to my get_SelParams()

    tell application "Numbers" to tell document dName to tell sheet sName to tell table tName

    *)

    on get_SelParams()

              local d_Name, s_Name, t_Name, row_Num1, col_Num1, row_Num2, col_Num2

              tell application "Numbers" to tell document 1

                        set d_Name to its name

                        set s_Name to ""

                        repeat with i from 1 to the count of sheets

                                  tell sheet i to set maybe to the count of (tables whose selection range is not missing value)

                                  if maybe is not 0 then

                                            set s_Name to name of sheet i

                                            exit repeat

                                  end if -- maybe is not 0

                        end repeat

                        if s_Name is "" then

                                  if my parleAnglais() then

                                            error "No sheet has a selected table embedding at least one selected cell !"

                                  else

                                            error "Aucune feuille ne contient une table ayant au moins une cellule sélectionnée !"

                                  end if

                        end if

                        tell sheet s_Name to tell (first table where selection range is not missing value)

                                  tell selection range

                                            set {top_left, bottom_right} to {name of first cell, name of last cell}

                                  end tell

                                  set t_Name to its name

                                  tell cell top_left to set {row_Num1, col_Num1} to {address of its row, address of its column}

                                  if top_left is bottom_right then

                                            set {row_Num2, col_Num2} to {row_Num1, col_Num1}

                                  else

                                            tell cell bottom_right to set {row_Num2, col_Num2} to {address of its row, address of its column}

                                  end if

                        end tell -- sheet…

                        return {d_Name, s_Name, t_Name, row_Num1, col_Num1, row_Num2, col_Num2}

              end tell -- Numbers

    end get_SelParams

     

    --=====

     

    on parle_anglais()

              return (do shell script "defaults read 'Apple Global Domain' AppleLocale") does not start with "fr_"

    end parle_anglais

     

    --=====

    --{code}

     

    Here is an alternate version of the piece of code extracting the values :

     

    --{code}

    (*

    Grab parameters describing the selected cells *)

    set {dName, sName, tName, rowNum1, colNum1, rowNum2, colNum2} to my get_SelParams()

    tell application "Numbers" to tell document dName to tell sheet sName to tell table tName

      (*

    Extract the names from the selected cells *)

              set cell1 to name of cell rowNum1 of column colNum1

              set cell2 to name of cell rowNum2 of column colNum1

              set theNames to value of cells of range (cell1 & ":" & cell2)

    end tell -- Numbers

    --{code}

     

    Yvan KOENIG (VALLAURIS, France) mercredi 25 janvier 2012

    iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2

    My Box account  is : http://www.box.com/s/00qnssoyeq2xvc22ra4k

  • 6. Re: Use Applescript to create folder structure from Numbers document
    adamowens Level 1 Level 1 (0 points)

    Thank you very much! Clearly I had no idea how lost I truly was!

     

    Regards,

    Adam

  • 7. Re: Use Applescript to create folder structure from Numbers document
    Level 8 Level 8 (41,760 points)

    You were just trying to drive Numbers with the vocabulary relevant to Excel.

    You're not the first one.

    When you want to drive an application, the first thing to do is to open an study its AppleScript's dictionary.

    2012-01-25T17.46.44.jpg

    It's exactly what I did before taking the screenshots which I posted.

     

    Don't worry. All of us, we started as ignorant about this or that.

    Starting from that, what make the difference is that some of us try to guess the way we may do this or that while some others take time to study the available resources.

     

    Yvan KOENIG (VALLAURIS, France) mercredi 25 janvier 2012

    iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2

    My Box account  is : http://www.box.com/s/00qnssoyeq2xvc22ra4k