How to call a Bash script from VBA (Excel)

Question

How can I run a bash file using VBA on MAC OSX

I tried the following code

location = FolderPath(ThisWorkBook.FullName) 

which returns the current directory minus the file name.

followed by

Shell(location &"runmybatch.sh")

I did this because the bash script is located within the same folder as the excel spreadsheet.

Answer 1

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/bash
open /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.

Answer 2

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, ":", "/")