Launching Excel VBA Macro From AppleScript

[Steve Drenker]

I just upgraded to Office 2004 to be able to get an AppleScript in 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 short test AppleScript:

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

 

[JE McGimpsey]

I just upgraded to Office 2004 to be able to get an AppleScript in 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 short test AppleScript:

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.

This works for me

tell application "Microsoft Excel"
open "hard disk:users:xxx:desktop:test.xls"
run VB macro "test.xls!test"
close active workbook saving no
end tell

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.

I've been running XL04 alongside XLv.X for two years with no problems.

 

[Steve Drenker]

Hi, JE.

Thanks. Your solution worked, but I did have to take out the name of the
macro from the "open" command (you probably knew that). I also found out I
had a capitalization error in the path and keeps the named file from
opening.

Here's the final solution that works:

tell application "Microsoft Excel"
open "Hard Disk:Users:xxxesktop:Test.xls"
run VB macro "test.xls!test"
end tell

It drives me crazy that Microsoft publishes incorrect information, in this
case in their dictionary. In Office vX, the "execute" command to trigger
macros didn't work at all and MS finally acknowledged the bug.

Steve

 

[JE McGimpsey]

Thanks. Your solution worked, but I did have to take out the name of the
macro from the "open" command (you probably knew that).

Yup - bad paste on my part - I obviously used a different path/file
name than you did.