VBA Shell function in Office 2011 for Mac

Question

I am trying to launch a shell script from a VBA macro in Word 2011 for Mac that will run in a Terminal window. I have tried using both the Shell function and the MacScript function, but the VBA interpreter doesn't seem to be able to find the script in either case.

According to the VBA reference documentation, the following should work:

 RetVal = Shell("Macintosh HD:Applications:Calculator.app", vbNormalFocus)

This produces a run-time error 53 'File not found'.

Any suggestions?

Answer 1

The Shell() VBA function on Mac appears to require the full path as an HFS-style path (with colons instead of slashes). It also doesn't appear to accept arguments as it does on Windows (reporting a 'Path not found' error if any arguments are added).

The MacScript() VBA function can also be used: MacScript("do shell script ""command"""). This is likely to be the simplest option and what I would suggest doing. The downside is that it has quite a lot of overhead (100-200ms per call).

Another alternative is the system() function from the standard C library:

Private Declare Function system Lib "libc.dylib" (ByVal command As String) As Long

Sub RunSafari()
    Dim result As Long
    result = system("open -a Safari --args http://www.google.com")
    Debug.Print Str(result)
End Sub

See http://pubs.opengroup.org/onlinepubs/009604499/functions/system.html for documentation.

system() only returns the exit code. If you want to get the output from the command, you could use popen().

Private Declare Function popen Lib "libc.dylib" (ByVal command As String, ByVal mode As String) As Long
Private Declare Function pclose Lib "libc.dylib" (ByVal file As Long) As Long
Private Declare Function fread Lib "libc.dylib" (ByVal outStr As String, ByVal size As Long, ByVal items As Long, ByVal stream As Long) As Long
Private Declare Function feof Lib "libc.dylib" (ByVal file As Long) As Long

Function execShell(command As String, Optional ByRef exitCode As Long) As String
    Dim file As Long
    file = popen(command, "r")

    If file = 0 Then
        Exit Function
    End If

    While feof(file) = 0
        Dim chunk As String
        Dim read As Long
        chunk = Space(50)
        read = fread(chunk, 1, Len(chunk) - 1, file)
        If read > 0 Then
            chunk = Left$(chunk, read)
            execShell = execShell & chunk
        End If
    Wend

    exitCode = pclose(file)
End Function

Sub RunTest()
    Dim result As String
    Dim exitCode As Long
    result = execShell("echo Hello World", exitCode)
    Debug.Print "Result: """ & result & """"
    Debug.Print "Exit Code: " & str(exitCode)
End Sub

Note that several of the Long arguments in the above example are pointers, so will have to be changed if a 64bit version of Mac Word is ever released.

Answer 2

Hopefully you've found the answer by now but you just need the full path:

RetVal = Shell("Macintosh HD:Applications:Calculator.app:" & _
              "Contents:MacOS:Calculator", vbNormalFocus)

Another example is something like:

RetVal = Shell("Macintosh HD:Users:brownj:Documents:" & _
              "rnaseq:IGV_2.0.14:igv.sh", vbNormalFocus)

Answer 3

Another problem presents exactly like this: permissions cause your script to fail due to differences between the AppleScript environment and your user's bash environment. This Q&A helped me figure this out. To get my script to work, I had to resolve some path and permissions issues (not the script itself, but things touched by the script).

Here is my recommendation, which hopefully gives better insight during your troubleshooting than the meaningless Excel errors I was seeing before I used AppleScript Editor:

1. Use AppleScript Editor to confirm that the script actually works as whatever user and with whatever environment variable happens to be used:

   1. In Spotlight, start typing "applescript editor" until it shows up and then click on it
   2. Create a new AppleScript Editor file

   3. Type your simple script into the new file without doubling the double quotes - mine reads

      do shell script "parseCsvAndOpen.sh"
      

   4. Hit the "Run" button for your script
   5. Track down any issues, make changes, and repeat hitting the "Run" button until you get it to execute from within AppleScript Editor
      • The good news here is that you have a narrower search if you need to go back to StackOverflow or Google for help ;-)

2. now copy your simple script from AppleScript Editor to your vba and confirm it still works

   1. I was able to just double my double quotes and put it in double quotes after the MacScript code:

      MacScript "do shell script ""parseCsvAndOpen.sh"""
      

      That is indeed one, two, and then three double-quote characters! (presumably escaping the double quotes)

Answer 4

For anyone using 64-bit Office here's Robert's code updated to be compatible with both 32 and 64 bit versions. Note that due to a change in sandboxing, the full path to the libc must be used in the declarations otherwise you'll end up with an error 53 "File not found" on never versions of OSX/Office.

#If Mac Then
  #If VBA7 Then
    ' 64 bit Office:mac
    Private Declare PtrSafe Function popen Lib "/usr/lib/libc.dylib" (ByVal command As String, ByVal mode As String) As LongPtr
    Private Declare PtrSafe Function pclose Lib "/usr/lib/libc.dylib" (ByVal file As LongPtr) As Long
    Private Declare PtrSafe Function fread Lib "/usr/lib/libc.dylib" (ByVal outStr As String, ByVal size As LongPtr, ByVal items As LongPtr, ByVal stream As LongPtr) As Long
    Private Declare PtrSafe Function feof Lib "/usr/lib/libc.dylib" (ByVal file As LongPtr) As LongPtr
    Private file As LongPtr
  #Else
    ' 32 bit Office:mac
    Private Declare Function popen Lib "libc.dylib" (ByVal command As String, ByVal mode As String) As Long
    Private Declare Function pclose Lib "libc.dylib" (ByVal file As Long) As Long
    Private Declare Function fread Lib "libc.dylib" (ByVal outStr As String, ByVal size As Long, ByVal items As Long, ByVal stream As Long) As Long
    Private Declare Function feof Lib "libc.dylib" (ByVal file As Long) As Long
    Private file As Long
  #End If

  Public Function execShell(command As String, Optional ByRef exitCode As Long) As String
    file = popen(command, "r")
    
    If file = 0 Then
      Exit Function
    End If
    
    While feof(file) = 0
      Dim chunk As String
      Dim read As Long
      chunk = SPACE(50)
      read = fread(chunk, 1, Len(chunk) - 1, file)
      If read > 0 Then
        chunk = Left$(chunk, read)
        execShell = execShell & chunk
      End If
    Wend
    
    exitCode = pclose(file) ' 0 = success
  End Function

#End If

Answer 5

Just not enough points to make a comment, but feel this as an addition to Robin Knights answer. Credits of course still to and for him.
For Excel 15.18 (2015) the open call used in the system() function from the standard C library doesn't need the --args keyword anymore:

Private Declare Function system Lib "libc.dylib" (ByVal command As String) As Long

Sub RunSafari()
    Dim result As Long
    result = system("open -a Safari http://www.google.com")
    Debug.Print Str(result)
End Sub

This works fine. Didn't test this under 2011.