Excuse me if this is the wrong place to post this but I am new here. Although I have over 40 years experience with computers and over 20 years with PC’s I have recently purchase a MacBook Pro so I have no experience of the Mac or its software.
On my PC I used Excel for all my spreadsheets some of which had macros to automate certain functions. I purchased iWork with the MacBook and gather that the equivalent of macros is applescript. I want to try and recreate some of these macros in applescript and have spent the last few days trying to discover how I use Applescript and Numbers and don’t seem to be getting anywhere. Can somebody please point me at a guide, a book or tutorial which covers Applescript and Numbers?
I think what I am after is fairly basic but some of the areas I need to start with are
How do you link Applescript with the Spreadsheet.
General moving / copying ranges of cells to other cells provided certain conditions are met.
I’m not familiar with iWork but I think it isn’t so much different from excel.
It’s not that you link a document with a script but more telling a document what to do
Here some scoping
tell application "microsoft excel"
--all code here is send to the application, see application suite in library
tell workbook 1 --or tell document 1
--all code here is send to the fist document of the application, see document suite in library
--also code will be send to the current sheet
tell worksheet 1
--all code here is send to the first sheet of first document of the application
end tell
--back to document 1
end tell
--back to the application
end tell
--back to script
To set an value in a cell in microsoft excel you could do this (there are more ways)
tell application "Microsoft Excel"
tell workbook 1
tell worksheet 1
set value of row 1 of column 1 to "hello world!"
end tell
end tell
end tell
more simple notation
tell worksheet 1 of workbook 1 of application "Microsoft Excel" to set value of row 1 of column 1 to "hello world!"
A Numbers ‘document’ contains ‘sheets’, each of which contains ‘tables’, each of which contains ‘cells’ which can be referred to in various ways: by address, as a numbered element of a named or numbered ‘row’, as a numbered element of a named or numbered ‘column’, as a numbered element of a ‘range’, etc. Quite a few permutations there.
tell application "Numbers"
tell document 1
tell table 1 of sheet 1
set value of cell "A2" to "hello world!"
-- or: set value of cell 2 of column 1 to "hello world!"
-- or: set value of cell 2 of column "A" to "hello world!"
-- or: set value of cell 1 of row 2 to "hello world!"
-- or: set value of cell 2 of range "A1:A2" to "hello world!"
-- etc.
clear range "A1:G5"
end tell
end tell
end tell
It’s still early days yet and Numbers isn’t wonderfully scriptable. Filling in a lot of cells can also take a surprisingly long time. But there’s just enough scriptability to be useful ” provided you don’t need whatever it can’t do yet.
I apologise for taking so long to reply but I have spent the last few days trying to understand Applescript and getting nowhere. What I eventually found was that I was overcomplicating matters, it must be the senility setting in!!
I was tied up on on how I told Applescript which particular spreadsheet I wanted it to work on and was making the assumption that the Tell Application statement would open Numbers. After spending several days banging my head against the brick wall and only getting a headache, this morning I opened my spreadsheet copied Nigel’s script into the editor, ran it and it worked. Doh!!!
I have now found that if I duplicate Nigel’s script and set Tell Document 1 to Tell Document 2 and I have two spreadsheets open, the instructions will work on the first or second spreadsheet as appropriate, i.e. Document 1, Document 2, etc work on relative placing. Is there any way in which instead of saying Document 1 I can give the Tell Document instruction the name of my file so that I don’t have to ensure positioning is correct?
Thanks for the help
Use a name reference to the document rather than an index reference ” ie. ‘document “My document.numbers”’ rather than ‘document 1’. If the script has told Numbers to open the file, it will have received a name reference to the resulting document anyway:
set aNumbersFile to (choose file of type "Numbers")
tell application "Numbers"
-- Launch Numbers without opening the Template Chooser.
launch
-- Bring Numbers to the front.
activate
-- Open the file.
set targetDoc to (open aNumbersFile) --> targetDoc contains a name reference to the opened document.
-- Send commands to the named document.
tell targetDoc
tell table 1 of sheet 1
-- etc.
end tell
end tell
end tell
Thank you for that Nigel, I just wonder if I could trouble you with something else. I have spent this morning trying to copy and paste from one cell to another but am getting nowhere.
What I currently have is
tell application “Numbers”
tell document 1
tell table 1 of sheet 1
global balance1
clear range “j7:p52”
copy cell “e6” to balance1
clear range “n6:n6”
set value of cell 6 of column “n” to balance1
end tell
end tell
end tell
This compiles alright but when i run it I get
error “Numbers got an error: AppleEvent handler failed.” number -10000
I don’t seem to be able to find a reference to this error number.
It’s probably because you need to set the variable to the value of cell “e6” rather than to the cell itself.
I expect you’re just experimenting at the moment, but it’s possible to set the value of one cell directly to the value of another, without needing to clear it first or to use a variable:
tell application "Numbers"
tell document 1
tell table 1 of sheet 1
clear range "j7:p52"
set value of cell 6 of column "n" to value of cell "e6"
end tell
end tell
end tell
