Automator=>AppleScript=>Excel Macro

Question

Automator=>AppleScript=>Excel Macro

Ok, so here is what I am trying to do. I want to open a bunch of excel files and have a VB macro I wrote run on them.

Currently I have automator:
-Get Specified Finder Items (the files I want to run the macro on)
-Open Finder Items
-Run Applescript

on run {input, parameters}

tell application "Microsoft Excel"
run VB macro "macroname"

end tell
end run

I can get this to run on one file, but it won't go through all the open files.

Can someone help me to figure out how to make it run on lots of files? I really don't want to have to repeat this for all 68 files I currently have (there will be more later)..

Thanks.

MacBook Pro Mac OS X (10.4.8)

Posted on Feb 22, 2007 9:49 AM

Reply

Answer 1

lc55

Level 3

750 points

Feb 22, 2007 10:56 AM in response to astrosquirrelbait

astrosquirrelbait,

I would suggest letting Excel open the files and include a repeat loop to process each file.

1) Get Specified Finder Items
2) Run AppleScript with following script

click here to ~~open this script in your editor~~ <pre style="font-family: 'Monaco', 'Courier New', Courier, monospace; overflow:auto; color: #222; background: #DDD; padding: 0.2em; font-size: 10px; width:400px">on run {input,parameters}

tell application "Microsoft Excel"

repeat with aFile in input
open aFile
run VB macro "macroname"
close aFile
end repeat

end tell

return input

end run</pre>

PowerBook 12" Mac OS X (10.4.8)

Reply

Answer 2

Feb 22, 2007 11:54 AM in response to lc55

Thank you for your comment. It seems like it will work, if I can figure out why I am getting this applescript error:

Microsoft Excel got an error: item 1 of {alias "path:file1.xls", alias "path:file2.xls"} doesn't understand the close message. (-1708)

Reply

Answer 3

Feb 22, 2007 12:39 PM in response to lc55

Well I found if I take out close aFile, it works, but leaves the files all open. So I will say that the question has been answered, but if anyone can figure out how to have this run and not have to leave all the files open at the end, that would be great.

Thanks.

Reply

Answer 4

lc55

Level 3

750 points

Feb 22, 2007 1:39 PM in response to astrosquirrelbait

Sorry about that I don't actually have Excel 2004 which is I assume what you are using.

I believe the statement should be:

close front document with saving

Reply