Using AppleScript to Launch Excel VBA Macro

Question

Level 1

39 points

Using AppleScript to Launch Excel VBA Macro

Using MS Office 2004 and AppleScript 1.9.3. I want to use AppleScript in MS Entourage to kick off a VBA macro in Excel. But no luck.

To test things, I created an Excel file ("test.xls") with a one-line VBA macro named "test." It just puts up a dialog box with the message "success." I'm calling it from A.S as follows (per the AppleScript Dictionary for Excel 2004):

tell application "Microsoft Excel"
run VB macro "hard disk:users:xxx:desktop:test.xls!test"
end tell

I get two successive error messages:
1. 'test.xls' cannot be accessed. The file may be read-only or you may be trying to access a read-only location."
2. "Microsoft Excel got an error: "hard disk:users:xxx:desktop:test.xls!test" doesn't understand the run VB macro message."

"test" file permissions are -rw-r--r-- and it is sitting right on the desktop.

FWIW, I did not remove the old Office vX yet, but renamed old Excel "Microsoft ExcelOld". I read on some other site that this may cause problems.

This has been endlessly frustrating. HELP!!??

Steve

PowerBook G5, Mac OS X (10.3.9)

Posted on Feb 6, 2006 9:57 PM

Reply

Answer 1

Ed Mansky

Level 3

735 points

Feb 7, 2006 8:28 AM in response to Madzertron

Hi Steve,
Have you tried dealing with the return value from your macro? If you are invoking the MsgBox function in your test macro, you may need to allow the return value from MsgBox to pass thru back to AS. Something like :
set MyRetCode to run VB macro "Macintosh HD:Users:xxx:Desktop:test.xls!test"

(Note unless you've changed the hard drive name to "hard drive", the default boot drive name will probably be "Macintosh HD". Case does matter here)

I also suspect you'll need to have Entourage up as well in the full case of running a macro in Entourage from Excel (or the other way around). Hence, the command ActivateMicrosoftApp in the Excel dictionary may also be useful.

Second, you can have a macro, in a startup template, in Entourage call a macro in a startup template in Excel to accomplish what you want without going into AppleScript at all. This latter, VB-only, method will require a "Reference", as it's called in VBA, to be added to the TemplateProject in Entourage that points to the Excel macro you want to fire off. The added benefit of this latter method is that you will have access to the full range of VBA commands/options, rather than the smaller dictionary available in AS.

Ed

PB G4 Mac OS X (10.2.x)

Reply

Answer 2

Madzertron Author

Level 1

39 points

Feb 7, 2006 1:44 PM in response to Ed Mansky

Hi, Ed. Another poster at microsoft.public.mac.office.excel told me he had luck splitting the command into two pieces as follows:

tell application "Microsoft Excel"
open "Hard Disk:Users:username:Documents:filename.xls"
run VB macro "macroname"
end tell

This did the trick for me. First open the file, then invoke the macro. The combined "run VB macro "filename!macroname" would not work for me at all.

Thanks for your input.

Reply