Excel Scripting

Ray_Barber · April 6, 2004, 7:15pm

Ok, I am completely new to the realm of AppleScritping. I have searched through this board and through AppleScript The Definitive Guide and I still can’t find a good starting point for doing what I need to do. I have some shell scripts that spew out data that is in the format of heading followed by two tab delimited columns. Like this.

Lumber sold on Monday
2x4 43
6x8 22
6x9 11

Lumber sold on Tuesday
2x4 46
6x8 32
6x9 34

Left column is always static so that can be ignored if the initial excel template is configured properly. I just need to get the right column put into a new cell for each day. That being said I haven’t the foggiest idea as to how to approach that. Can anyone point me to a good place to start? A published example, excel script book, anything?

Thanks,

Figaro

jonn8 · April 7, 2004, 5:22pm

I’m not sure there are any books that cover scripting Excel. Try searching this board for “Excel” topics. This should help get you started:

–Open this script in a new Script Editor window.

–set the_result to paragraphs of (do shell script “path/to/script”)
–which should then be the same as:
set the_result to paragraphs of “Lumber sold on Monday
2x4 43
6x8 22
6x9 11”

set the_values to {}
set old_atid to AppleScript’s text item delimiters
set AppleScript’s text item delimiters to tab
repeat with i from 2 to (count of the_result)
try
set end of the_values to {(text item 2 of (item i of the_result)) as integer}
end try
end repeat
set AppleScript’s text item delimiters to old_atid
–>the_values = {{43}, {22}, {11}} --needs to be a list of lists

tell application “Microsoft Excel”
Activate
Select Range (“R1C1:R” & ((count of the_values) as string) & “C1” as string) –R=rows; C=columns
set Value of Selection to the_values
end tell

Jon

[This script was automatically tagged for color coded syntax by Convert Script to Markup Code]

Ray_Barber · April 7, 2004, 6:43pm

Great! Thanks a bunch I will give that a shot! I actually did scour this board, apples boards, and google for anything about applescript and excel but there was nothing even close to relevant. Maybe what I was looking for was just so rudimentary that it was just never discussed or my search skills just plain stink.

Thanks again,
Figaro

Ray_Barber · April 7, 2004, 7:21pm

D’oh! I tried it and I got this error

“Microsoft Excel got an error: Range “R1C1:R0C1” doesn’t understand the Select message.”

jonn8 · April 8, 2004, 3:58am

This error means that parsing your data resulted in no values being added to the list (0 = count of the_values). Did you just try the script as is, without adding your shell script? Does that work? If so, then the data coming out of your shell script looks a little different than what you posted. There are a number of threads on this board related to Excel (I’ve posted on most of them). Use the search link at the top of the page and simply search for the word “excel”.

Jon