Running Applescript from Excel 2011 VBA

Question

Mac OS X, Excel 2011 VBA Question: I am trying to run a bash shell from an Excel 2011 macro. The command executes properly from the Mac OS X Lion 10.7.5 terminal. It also executes correctly using Applescript from the command line:

osascript -e 'do shell script "/Users/MyFolder/Desktop/MyShell"'

I can also execute it as ./test.sh, where test.sh is an executable (chmod +x test.sh) file including the lines

#!/bin/bash
osascript -e 'do shell script "/Users/MyFolder/Desktop/MyShell"'

From Excel, I am able to execute a similar test.sh file

#!/bin/bash
open /Applications/Calculator.app/

using the Excel VBA function code

Function TestShell() As Double

   Shell ActiveWorkbook.Path & ":test.sh"
   TestShell = 0

End Function

However, when I switch the test.sh file to have the desired content (the Applescript command that uses osascript above), it fails. One further aspect of the pathology is that if I erase the test.sh file, then replace it, on the first run it will often put up a window saying something like 'This is a file from the internet. Do you still want to run it?'. In this case, after clicking OK the code sometimes appears to run, but doesn't execute correctly, even though the function exits with no errors. But, invariably, if I try to run it again, it gives me

Run-time Error '75'
Path/File Access Error

and the function crashes.

For a little more context, the 'MyShell' script (not its real name) was written to take arguments from a file, because I found documentation online that Excel on the MacOS couldn't run a script with input arguments via 'Shell' in a macro. I haven't had a problem writing to the argument input file. Just difficulty getting the test.sh script that executes the 'MyShell' script to execute. I'm not a regular VBA programmer, but I've looked through as many webpages as I could and found a lot of differences between PCs and Mac OS X, but I have not been able to find the solution to this simple problem. Any suggestions would be much appreciated.

user2375957 user2375957 · Accepted Answer · 2013-05-31T01:04:47

OK. Answering my own question: To run the shell script, I needed to make an Applescript .app file that did what I needed from the Applescript utility. So, I inserted my original shell script call into the Applescript utility

do shell script "/Users/MyFolder/Desktop/MyShell"

compiled it as MyShell.app, then called the shell script test.sh that ran it from the Excel macro with

Function TestShell() As Double

Shell ActiveWorkbook.Path & ":test.sh"
TestShell = 0

End Function

where test.sh contained

#!/bin/bash
open /Users/MyFolder/Desktop/MyShell.app/

A pretty roundabout way of getting what I wanted to get done done, but I really needed to do it with Excel on Mac OS X. In the end (not shown here), I was able to get parameters to the shell script by writing them from the Excel macro to a file. Ran the MyShell.app. Then I had the shell output written to another file that was then read by the Excel macro.

Running Applescript from Excel 2011 VBA

1 Answers