How to call a Bash script from VBA (Excel)

There are a couple of surprises that make this tricky!

1. The Shell function expects paths to be separated by :, not /
2. When the script runs, it will have the root (/) directory as its working directory

Other than that you should be able to run a bash script using the Shell command. Here are step-by-step instructions to get you started.

• Create the following bash script in your Desktop directory and name it test.sh:

#!/bin/bashopen /Applications/Calculator.app/

• Make sure test.sh is executable (from a Terminal window, chmod +x test.sh)
• Try running it (type ./test.sh in Terminal). The calculator app should appear. Exit that.
• Run Excel and create a new workbook, Workbook1.
• Save this blank workbook in the Desktop directory as a Macro-enabled workbook
• Run VB: Tools > Macro > Visual Basic Editor
• If the immediate window is not showing, View > Immediate Window
• In the immediate window, type the following VBA code:

 Shell ActiveWorkbook.Path & ":test.sh"

• The calculator app should run.

I had no luck with the shell command, but here's what worked for me through applescript in Excel 2011:

script_s = "do shell script ""/Users/user/path/to/script/rubyscript_09.rb"""MacScript script_s

I found it necessary to have the script use a shebang of '#!/usr/bin/env ruby' (after the install of ruby version manager)

To address a file that is in a subfolder of the folder of the calling file, use this:

FilePathName_s = ThisWorkbook.Path & ":subfolder:filename"

This returns a path with :'s, which I believe must be converted to /'s for the do shell script. This can be done with the AppleScript, quoted form of POSIX path of FilePathName_s, or with the VBA FilePathName_s = "/" & Replace(FilePathName_s, ":", "/")

I did not use the FolderPath command, its seems not to be availble to me so I will investigate later why not. Below is some sample code that I have tested to make sure that it is possible to run a .sh file from OSX Excel.

The .sh file I used to test was placed on my desktop and only contains the following line: "open /Applications/Calculator.app"

The VBA code to invoke the .sh from Excel on OSX is below:

Dim DesktopFolder As StringDesktopFolder = MacScript("return (path to desktop folder) as string")Dim ScriptFile As StringScriptFile = DesktopFolder & "test.sh"RetVal = Shell(ScriptFile, vbNormalFocus)

I decided to check if its only functional on certain folders so I moved the .sh file to the /tmp folder and could not execute the file, simply got "file not found errors" no matter how I encoded the path. Finally realised that the /tmp file on OSX is a symbolic link that maps to /private/tmp and when I used that path it all seems to work fine again.

Dim ScriptFile As StringScriptFile = "Macintosh HD:private:tmp:test.sh"RetVal = Shell(ScriptFile, vbNormalFocus)

Anyway, hope it helps a bit. I will continue looking for a better way, need something to dynamically find what the file location is reliably.