Help me with Automator Script

January 15, 2010 8:33 AM

Does anyone know how to use Automator in OSX 10.5x? This seems like it should be easy ...

I have two Word documents and two Excel spreadsheets acting as "input", then I have one more Word document acting as the final PRODUCT. I will manually highlight certain cells in both spreadsheets, then all I want to do is the following process:

Go to DOC1, select all contents, paste into PRODUCT
Go to XLS1, copy selected cells, paste into PRODUCT
Go to DOC2, select all contents, paste into PRODUCT
go to XLS2, copy selected cells, paste into PRODUCT

I have found Automator to be terribly frustrating, so screen shots or very explicit instructions would be appreciated.

(Editorial Comment: Why does everyone love Automator? I hate it. Give me my VBA macros back any day.)

posted by anonymous to Computers & Internet (6 answers total)

Since you have to select the cells yourself (unless I misunderstand) I'm not sure how Automator (or VBA, for that matter) would be a help here.
posted by ChurchHatesTucker at 8:55 AM on January 15, 2010

You're using it to accomplish tasks in 3rd party applications that aren't well suited for it. Most well-written OS X software is built in a way that accommodates Automator's "hooks."

I'm sorry, but I think the best advice I can give is "not the right tool for the job," and since you're working with closed formats, I'm not sure there is a right tool. Applescript, maybe, but it would be a burden.
posted by Mr. Anthropomorphism at 9:01 AM on January 15, 2010

I will manually highlight certain cells in both spreadsheets...

Sorry, but isn't this the hard part? If all you're trying to automate are four copy and paste commands, I can't see the benefit... but knowing this is all you need, you could fairly easily Applescript it. Think less about "copy selected cells and paste them into the specified document" and more "hit command-c, switch to [program], hit command-v," but honestly I don't know how friendly Office is to Applescript, either.

A few minutes of googling indicates the operative phrases could be:

tell application "System Events" to keystroke "c" using {command down}

and

tell application "System Events"
tell process "Word" to keystroke "v" using {command down}
end tell

but I can't test it, unfortunately.
posted by Mr. Anthropomorphism at 9:14 AM on January 15, 2010

Is it a consistent column or row that needs to be copied from the spreadsheets? If so, it might be possible (using something like Applescript - more complex than Automater) if you were to convert the spreadsheets into something more readable like CSV or XML. Perhaps (and don't quote me on this - it's merely speculation) maybe the more open and readable .docx and xlsx formats might be doable as well.

Could you do it on a PC and use VBA, since you seem to know that and it's far more useful and powerful than Automater with Office.
posted by Magnakai at 9:38 AM on January 15, 2010

Honestly, I think Apple's new UNIX-like layer might be more useful. I would convert the word document using antiword, the Excel files using xls2csv, and then use something like awk to produce a PRODUCT csv that can then be converted back into Excel. Terribly roundabout, I know, but it can be done.

By the way, if you can't compile either of those two for some reason, you probably need to install Xcode. IIRC, Leopard doesn't ship with a build environment by default.
posted by d. z. wang at 11:07 AM on January 15, 2010

>I would convert the word document using antiword, the Excel files using xls2csv, and then use something like awk to produce a PRODUCT csv that can then be converted back into Excel.

Yeah, it sounds like the wrong tools for the job were chosen long ago.
posted by ChurchHatesTucker at 11:59 AM on January 15, 2010