Entering Data into Excel with Applescript

Question

Entering Data into Excel with Applescript

I am looking to populate various worksheets in a single Excel file (Workbook?) with data that I enter via a dialog box in an Applescript. I'm a newbie and can't quite figure out the Applescript syntax of how to actually get data to be entered into an Excel cell.

dan

2 GHz Mac Book, Mac OS X (10.5.7)

Posted on Jun 13, 2009 7:11 AM

Reply

Answer 1

Arkouda

Level 4

3,377 points

Jun 13, 2009 3:45 PM in response to Dan Erlandson

Hello again!

<pre style="
font-family: Monaco, 'Courier New', Courier, monospace;
font-size: 10px;
margin: 0px;
padding: 5px;
border: 1px solid #000000;
width: 720px;
color: #000000;
background-color: #FFDDFF;
overflow: auto;">
tell application "Microsoft Excel"
set formula of range "D7" of worksheet "Sheet1" to "5"
end tell</pre>

there's a guide to scripting Excel here: http://www.google.com/url?sa=t&source=web&ct=res&cd=1&url=http%3A%2F%2Fdownload. microsoft.com%2Fdownload%2F3%2FA%2F7%2F3A70FB4B-0C6A-43E3-AAB7-AC9166B25632%2FEx cel2004AppleScriptRef.pdf&ei=8MozSti-N4LSjAfhnfj6Cg&usg=AFQjCNGR7CfdkTIrX9ADRQdT V6DkFu0jzQ&sig2=Pn2x5znZQx886Vdp7tdmQQ

Reply

Answer 2

Jun 13, 2009 4:17 PM in response to Arkouda

Thanks Bernard! Let me play with that for awhile and I'm sure there will be more questions.

dan

Reply

Answer 3

Jun 16, 2009 7:14 AM in response to Dan Erlandson

Having some trouble activating and then writing to a sheet within a workbook. I am using Office 2008. here is what I have so far and the error message is activate object worksheet {"Room 218"}
"Microsoft Excel got an error: The object you are trying to access does not exist" The worksheet Room 218 does exist and is the second worksheet. I know there is a feature to access the index number of the worksheet, but that seems way more complicated than just using the name of the worksheet since I have that info right at hand.

Thanks,

dan

<pre style="
font-family: Monaco, 'Courier New', Courier, monospace;
font-size: 10px;
margin: 0px;
padding: 5px;
border: 1px solid #000000;
width: 720px;
color: #000000;
background-color: #FFDDFF;
overflow: auto;"
title="this text can be pasted into the Script Editor">
set a to text returned of (display dialog "Please enter the slot (A-H) and the room #, separating them with a comma:" default answer "")
,set astid to AppleScript's text item delimiters
,set AppleScript's text item delimiters to ","
,set {chosenslot, chosenroom} to {(a's text item 1), (a's text item 2)}
,set AppleScript's text item delimiters to astid
,tell application "Microsoft Excel"
, activate
, open workbook workbook file name "MacBook HD:Users:Dano:Desktop:Classrooms.xlsx"
, activate object workbook "Classrooms.xlsx"
, set worksheetname to {"Room " & chosenroom}
, activate object worksheet worksheetname
, set formula of range "J3" of worksheet worksheetname to chosenslot
,end tell,</pre>

Reply

Answer 4

Arkouda

Level 4

3,377 points

Jun 16, 2009 8:09 AM in response to Dan Erlandson

I only have 2004 to test on at the moment, and I can't see your whole worksheet, but:

I made a file Workbook1.xls on my desktop and made the second worksheet name "Room 218".

Entering "H,218" works OK with this script, so I suspect there's something odd about the two extra "activates" that you have in your code, unless it is to do with 2008.

<pre style="
font-family: Monaco, 'Courier New', Courier, monospace;
font-size: 10px;
margin: 0px;
padding: 5px;
border: 1px solid #000000;
width: 720px;
color: #000000;
background-color: #FFDDFF;
overflow: auto;">
set a to text returned of (display dialog "Please enter the slot (A-H) and the room #, separating them with a comma:" default answer "")
set astid to AppleScript's text item delimiters
set AppleScript's text item delimiters to ","
set AppleScript's text item delimiters to astid
set {chosenslot, chosenroom} to {(a's text item 1), (a's text item 2)}
tell application "Microsoft Excel"
activate
open workbook workbook file name ((path to desktop as text) & "Workbook1.xls")
set worksheetname to "Room " & chosenroom
activate object worksheet worksheetname
set formula of range "J3" of worksheet worksheetname to chosenslot
end tell </pre>

Incidentally, all those commas in the formatted script threw me, and it happened to me as well.

Insert this line at the top of the forum formatting script to be safe:

<pre style="
font-family: Monaco, 'Courier New', Courier, monospace;
font-size: 10px;
margin: 0px;
padding: 5px;
border: 1px solid #000000;
width: 720px;
color: #000000;
background-color: #FFDDFF;
overflow: auto;">
set AppleScript's text item delimiters to " "
</pre>

Reply

Answer 5

Jun 16, 2009 9:43 AM in response to Arkouda

thanks for the help Bernard...I'm still having trouble and I think it is due to the quotes that I am getting in worksheetname variable. Excel can't find the worksheet "room218"... might it be the quote issue? Here is what I have...thanks by the way for the input on the list question.

dan

<pre style="
font-family: Monaco, 'Courier New', Courier, monospace;
font-size: 10px;
margin: 0px;
padding: 5px;
border: 1px solid #000000;
width: 720px;
color: #000000;
background-color: #FFDDFF;
overflow: auto;"
title="this text can be pasted into the Script Editor">
set entered to text returned of (display dialog "Please enter the slot (A-H) and the room #, separating them with a comma:" default answer "")
set astid to AppleScript's text item delimiters
set AppleScript's text item delimiters to ","
set {chosenslot, chosenroom} to {(entered's text item 1), (entered's text item 2)}
set cellreference to {"J3", "K3", "L3", "M3", "O3", "P3", "Q3"}
set slots to "ABCDFGH"
set chosenslot2 to (offset of chosenslot in slots)
set AppleScript's text item delimiters to astid
tell application "Microsoft Excel"
activate
open workbook workbook file name "MacBook HD:Users:Dano:Desktop:Classrooms 09-10.xls"
set worksheetname to {"Room " & chosenroom}
get worksheetname
activate object worksheet worksheetname
set formula of range (get item chosenslot2 of cellreference) of worksheet worksheetname to chosenslot
end tell </pre>

Reply

Answer 6

Arkouda

Level 4

3,377 points

Jun 16, 2009 12:19 PM in response to Dan Erlandson

This line:

set worksheetname to {"Room " & chosenroom}

doesn't need the curly brackets - that is making a list with a single item.

try

set worksheetname to "Room " & chosenroom

you don't need:

get worksheet name

Reply

Answer 7

Jun 16, 2009 2:18 PM in response to Arkouda

Ahhh those curly brackets! Thanks, works great now. One more question pertinent to this thread...can I force Excel to save the file each time I have entered new data into the spreadsheet? The Applescript Reference doesn't have a reference to this function, but does talk about the "saving as" feature. I guess I'm thinking that this would complicate things, but maybe that is the safety feature built into Excel and Applescript to prevent unwanted changes.

dan

Reply

Answer 8

Arkouda

Level 4

3,377 points

Jun 16, 2009 3:04 PM in response to Dan Erlandson

save workbook

After your changes will save the current workbook in its current file.

Reply

Answer 9

Jun 16, 2009 4:26 PM in response to Arkouda

funny how that command isn't included in the reference manual...or maybe I could not find it!! Got everything working in the script except for one thing: I have an if then clause to detect if the worksheet is already open or needs to be opened, and it isn't recognizing the first condition. Syntax for this is?? here is what I have as the if statement: if active workbook is "Classrooms 09-10.xlsx" then So the script never evaluates this to be true (even though it IS open) so it always goes to the then part which is fine except it reopens the file every time, which does take a few extra seconds, and considering that I'm going to be doing this hundreds of times each year I'd like it to be as quick as possible. Hope that is clear 🙂

Thanks

dan

Reply

Answer 10

Arkouda

Level 4

3,377 points

Jun 17, 2009 12:29 AM in response to Dan Erlandson

The active workbook has loads of properties, so you need to test for one of those.

Run this line

properties of active workbook

amongst the others is 'name' which you'll need to refer to in your test.

E.g.

if (name of active workbook) is "Workbook1.xls" then ...

Reply

Answer 11

Jun 17, 2009 5:42 AM in response to Arkouda

Perfect... I ended up using the full name comand, and yes there are lots of properties!!!

Thanks,

dan

Reply