HI,
i have an excel spreadsheet which i use to keep track of my finances.
i want to create an program in applescript which simple reads and writes values to and from this excel spreadsheet.
is this possible and if so how?
james
Sure.
The following code shows you how to get and set values of cells on the current worksheet
tell application "Microsoft Excel"
get Value of Cell "A1"
set Value of Cell "A2" to "some string"
end tell
does the worksheet have to be open for this cos if so then the program i want to make is pretty useless.
if not how do i tell it which worksheet to use and which sheet in the worksheet i’m referring to.
many thanks,
james
Yes, the worksheet has to be open since nothing else can decode Excel’s file formats and get specific cell entries - at least, AppleScript can’t do it on its own.
However, Excel can be in the background and hidden, so you don’t have to see it.
As for which worksheet, the following snippet should get you started:
tell application "Microsoft Excel"
set myVal to get Value of Cell "A1" of Worksheet 2 of document 1
end tell
You can be more specific on the document, using document names rather than indexes if you prefer.
thanx
well then how could i make a financial planning program in applescript without storing the numbers in excel. how do programs like iCash work?
james
They probably don’t use Excel 
While linking applications together is one of AppleScript’s main features, it’s entirely possible to build applications entirely within AppleScript itself, using AppleScript to perform calculations, save/read data off disk, interact with the user, etc. without third-party applications.
In the case of iCash, I assuming it’s writing its own files and performing its own calculations rather than relying on Excel to do it.
If you want to develop full-blown standalone applications, take a look at AppleScript Studio ( http://www.apple.com/applescript/studio/ ). It has tools that let you develop user interface elements (menus, windows, etc.) and build standalone apps.
ok
well then how do i create and then read and write to my own file(s)?
james
Read:
set myData to read file “path:to:file”
→ myData now contains the entire contents of the file “path:to:file”
Write:
set myData to “whatever data you want to save in the file”
set myFile to open for access file “path:to:file” with write permission
write myData to myFile
close access myFile
The standard additions dictionary has more details of these commands.
hi
that is great thanks very much for help.
i am new to mac so i have two more questions:
how do i get the path of the file: lets say it is in a folder called finances in the ‘home’ folder (the home folder being the standard folder that comes with os x with documents, music, shared etc etc)
2.how can i view the standard additions dictionary.
is this on my mac already?
thanks
james
[quote="jshrager"how do i get the path of the file: lets say it is in a folder called finances in the ‘home’ folder (the home folder being the standard folder that comes with os x with documents, music, shared etc etc)
[/quote]
The ‘path to’ command will give you the path to common system directories. In the case of the home directory:
path to current user folder
will return your home directory in the form: alias “Macintosh HD:Users:username:”
From here you can build the pathname to your file using:
set file_path to (path to current user folder as string) & "finances:myFile"
→ “Macintosh HD:Users:username:finances:myFile”
so you can then:
set myFile to open for access file file_path with write permission
Note that ‘open for access’ will create the file if it doesn’t already exist, but it won’t create directories, so you’ll need to make sure the directory exists first.
Yes. Use the Script Editor’s “Open Dictionary’ command and choose 'Standard additions” from the list that displays.
The dictionary is the basic tool for finding what commands each application supports.