How to script Excel or Numbers on a mac, and launch from bash?

views:

answers:

How to script Excel or Numbers on a mac, and launch from bash?

Hi,

I want to write a bash script for a mac which takes a text file containing a table of numbers (can be .csv), converts the numbers to a chart, saves the chart (any file format that I can display on my web page), and exits. It must do this unattended. No user interaction.

I know bash, perl, and a little AppleScript, and I can learn VBA or whatever else. I am about to plunk down money for the new Excel 2011 for Mac. I think I want Excel rather than Numbers'09, but I am not at all sure, and I think VBA or AppleScript will allow me to automate most of the work.

Is AppleScript + Excel a good choice? If so, then I'm home free. Applescript programs can be launched from bash via osascript command.

But maybe VBA is better? But how can I launch a Excel VBA script from bash on a mac?

Summary of questions: (1) AppleScript or VBA or something else? (2) Excel or Numbers? (3) How to launch Excel VBA script from bash?

Thanks in advance, Ken

Microsoft removed VBA support from Office with v2008. iWork '08 has no Applescript support, and while I can't speak directly about v'09 given Apple's recent decline in scriptable applications I wouldn't count on it.

The sure, long-term bet here would be to use Excel with Applescript.

Philip Regan 2010-10-27 10:13:22

Thank you, Philip, but Excel 2011, the version I'm "about to plunk down money for", according to macworld (link below), restores VBA.

Ken 2010-10-27 13:20:21

http://www.macworld.com/reviews/product/671497/review/excel_2011_for_mac.html

Ken 2010-10-27 13:21:01

I still stand behind my recommendation to use Applescript. My experience has been that Applescript is actually easier (at least editor-wise). In addition, Applescript open to bringing in processes from other applications whereas VBA is very much limited to the Office suite.

Philip Regan 2010-10-27 13:28:16

OK, thanks, I think I will take your advice.

Ken 2010-10-27 20:41:57

I'm still curious - can a VBA script be launched from the command line (eg bash)?

Ken 2010-10-27 20:43:25

If running a VBA script in Office is exposed in the Applescript API, then you could write a small script to run the VBA via osascript. That is, if there isn't something similar for VBA in bash, but I highly doubt MS went that far with their implementation.

Philip Regan 2010-10-27 23:08:58

OK thanks. Cool. So I bought Excel 2011. THEN I googled for "excel applescript reference". All hits are fore Excel 2004. Sigh. Shoulda googled first.

Ken 2010-10-27 23:16:35

If I want more help I'll post a new question, tagged only for excel and applescript. I am making some progress developing my script, but it is a major handicap having Excel 2004 AppleScript reference manual ... which sometims has applicable info and sometimes wrong info.

Ken 2010-10-27 23:28:57

My understanding is that the AS API was almost completely overhauled to be as robust as the then-former VBA support in 2008. But, there shouldn't be too much difference between 2008 and 2011. If you have 2011 already, then MS should have manuals on their ms.com/mac site, otherwise look for the 2008 materials, and there is always the Dictionaries for the apps themselves, of course.

Philip Regan 2010-10-27 23:46:04

Ah, didn't know about the AS dictionary. Cool!

Ken 2010-10-28 05:47:14

OK I think I have the framework I was seeking for writing my script.In spite of a couple lingering questions which I will pose in a moment.My framework:myscript.bash # executable file=============#!/usr/bin/osascript tell application "Microsoft Excel" activate open workbook workbook file name "Macintosh HD:Users:myusername:myfolder:foo.xls" run VB Macro "foo.xls!mymacro()" end tell

Ken 2010-10-28 05:50:24

Sorry my line breaks did not come thru.All the within-Excel work is done by an Excel macro "mymacro", which I create via Tools->Macro->Record New Macro...

Ken 2010-10-28 05:53:00

make the file myscript.bash executable and it is a shell command.

Ken 2010-10-28 05:54:04

I believe I now have adequate knowledge to write that script start to finish, (THANK YOU!!) but here are my lingering questions: (1) the AppleScript Editor app brings up a window with a "Record" button. I haven't figured out how to "Record" anything. How to use this? and (2) the AS dictionary uses slightly cryptic notation. I mostly get it, but not 100%. Is there documentation somewhere regarding how to interpret this notation?

Ken 2010-10-28 06:03:43

The Record button is a boondoggle. I've been doing AS for years and I've never been able to get that to do anything useful; ignore it. The Dictionary is written by the developer, and there are no real rules. I would recommend reading the Applescript Language Guide on Apple's ADC site before going too much further. That's the core document to the language and it will help fill in a lot of the gaps. There is a program called Script Debugger by Late Night Software that does a better job presenting the Dictionary, but it's probably more expensive than what you need right now.

Philip Regan 2010-10-28 10:46:19

Excellent. Thank you so much, Philip. Case closed. :)

Ken 2010-10-28 11:15:07

iWork 09 does support Applescript. Pages is pretty good with its support. Numbers far less so. I think most expect more expansive Applescript support with the new version as Numbers only has partial support. So for what the original question asks you really need to use Excel.

If you are going to script I'd look seriously at Python or Ruby combined with Appscript rather than Applescript or VBA. Applescript is a bear of a language to write in and using one of the other languages gives you all the libraries of that language. That allows quite a bit more powerful code to be written.

If you are just writing for Office and don't need much other functionality then I'd probably stick with VBA.

Clark 2010-10-29 23:47:26

ansaurus

tags:

views:

answers:

How to script Excel or Numbers on a mac, and launch from bash?

related questions