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.

Does this question help http://stackoverflow.com/questions/6136798/vba-shell-function-in-office-2011-for-mac — Preet Sangha, Jun 14 '12 at 23:07
don't you need to include a "\" char between location and the script name? Good luck. — shellter, Jun 14 '12 at 23:53
The "Shell" command requires a ':' separated path, although this isn't the problem. I've tried a simple Shell call with a fully qualified path pointing to a bash shell file with mode 755, in the same directory as the Excel workbook, and I always get a permission denied error. This seems like a pretty easy thing but I can't make it work, either. — Joel Spolsky, Jun 22 '12 at 22:04
Joel, double check that the owner of the parent process is the same as the owner of the script. chown the script accordingly or chmod it temporarily so that anyone can execute it. (757) — Rob Kielty, Jun 24 '12 at 01:23
Saned's "FolderPath" is not a real thing... it must be a function in his own library ... but don't worry about that. I'll award the bounty to anyone who can provide step by step instructions to someone who can show how to create a one line bash script that does something trivial like touch a file, in the same directory as an Excel macro-enabled workbook, and successfully launch that script from VBA. — Joel Spolsky, Jun 25 '12 at 15:01
Hi Guys finally we figured it out. I think the issue is with the Shell command its self as I tried Shell ("pwd") and that gave in error in the vba. To overcome this I used Macscript to access and run my script. Joel if you are still having problems with what you were trying to do please let me know and I can try and help you. I will post my solution once I get home and everything looks good. — Mohamed, Jun 25 '12 at 18:38
I'll award the bounty to Joe who came closest. I've also posted my own answer, which shows a step-by-step procedure that should work for everybody to get a simple bash script to run from Excel VBA. — Joel Spolsky, Jun 26 '12 at 23:47

score 3 · Answer 1 · answered Jun 26 '12 at 23:46

There are a couple of surprises that make this tricky!

The Shell function expects paths to be separated by :, not /
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.

Steph · Answer 2 · 2012-10-02T19:08:20.377

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

Tom Kreutz · Answer 3 · 2017-03-17T03:29:44.907

At first, Joel's excellent tutorial didn't work for me (on OS X 10.11.6 and Excel for Mac 2011). After some sleuthing, I discovered that my problem was caused by "extended attributes" in my script file, test.sh. In Terminal, ls -l test.sh yields:

  -rwxr-xr-x@ 1 tgk  staff  456 Mar 16 13:12 test.sh

Note that "@" indicates the presence of extended file attributes. More information is given by invoking ls -l@ test.sh, which yields:

-rwxr-xr-x@ 1 tgk  staff  456 Mar 16 13:12 test.sh
      com.apple.FinderInfo   32 
      com.apple.TextEncoding     15

In other words, depending on how the file was created, it may or may not have extended attributes. So I stripped off the extended attributes via xattr -c test.sh, and then Joel's call: Shell ActiveWorkbook.Path & ":test.sh" worked perfectly! Many thanks to all.

____

I later learned that saving the shell script without writing extended attributes is possible in TextWrangler by first invoking defaults write com.barebones.textwrangler WriteExtendedAttributes never in Terminal. However, this only prevents the addition of the com.apple.FinderInfo attribute; the com.apple.TextEncoding attribute remains. Fortunately, however, Joel's VBA Script command now works when the shell script has just the com.apple.TextEncoding extended attribute! So, TextWrangler is the editor for me!

score 1 · Answer 4 · answered Jun 23 '12 at 14:16

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 String
DesktopFolder = MacScript("return (path to desktop folder) as string")

Dim ScriptFile As String
ScriptFile = 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 String
ScriptFile = "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.

score 1 · Answer 5 · edited May 23 '17 at 12:00

I also had trouble getting my simple bash script to work, even though I had it on the global path (/usr/bin) and all users had read and execute permissions. Two things caused my script to fail: permissions and paths due to differences between the AppleScript environment and my user's bash environment. I found the discussion over at VBA Shell function in Office 2011 for Mac more helpful in solving my problem. Here is the process I eventually settled on.

Because Excel was masking the underlying details, I recommend using the AppleScript Editor, which hopefully gives better insight during your troubleshooting than the meaningless Excel errors I saw:

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 ;-)
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)

score 0 · Answer 6 · answered Jun 24 '12 at 14:42

I don't have MS Office for Mac installed, so I can't test the answer I propose. I suppose it should be possible to call Bash the binary (i.e. /bin/bash) and pass the name of the script you want to execute as an argument. This would avoid any issues with permission bits, and would ensure that the path name for the second argument is the usual Unix-style /-separated one. For the first argument, it might be enough to call bash by its name, without any path, as that should use the PATH environment variable.

All in all, and still without testing, I'd try this:

Shell("bash runmybatch.sh")

Or, if that doesn't work due to incorrect working directory,

Shell("bash '" & location & "/runmybatch.sh'")

where location is the /-separated path to the directory of the workbook. The sngle quotes should take care of any spaces or other exotic characters in the path.

score 0 · Answer 7 · edited Sep 04 '14 at 00:13

What you need to do is write the following command in your VBA macro:

Shell "C:Path\to\script\location\script.sh"

(Remember to include double quotes surrounding the path)

The best way to get your script location is to record a macro to save a workbook in the same folder where your bash script resides. That way you can later see how Excel calls that path and copy/paste it to the shell command above.

How to call a Bash script from VBA (Excel)

7 Answers7