Is it possible to run Jruby/Celerity scripts via Excel VBA and output the results to Excel

Question

I have some Jruby/Celerity scripts that I would like to run from Excel VBA and then output the script results to an Excel sheet. Is this possible? Can anyone suggest a potential approach or some potential resources.

What I'm hoping to achieve:

Mac Excel 2011 VBA code calls Celerity script and passes parameter that will be used by the script.
Script executes, utilizing parameter passed by VBA.
Script complete its execution and returns results to excel worksheet.

EDIT: I should also mention that I am running Excel 2011 on a Mac. One of my areas of confusion is that everything I have found on automating Excel with ruby seems to assume a Windows operating system. (# Require the WIN32OLE library). Would I use the same library running excel under OS X.

Many thanks.

Have you been able to run a random jruby script and getting the response back in a cell? — bbozo, Jul 09 '14 at 08:53
No, I have not. I have been working with the shell command in VB and based on some things I've seen on the web I thought it would work. — Mutuelinvestor, Jul 09 '14 at 11:28

score 2 · Accepted Answer · edited May 23 '17 at 12:19

Solution by example

simpletest.rb

require "rubygems"
require "celerity"

word_to_search = ARGV[0]
word_to_check = ARGV[1]
#puts "DEBUG - word_to_search: #{word_to_search}"
#puts "DEBUG - word_to_check: #{word_to_check}"
browser = Celerity::Browser.new
browser.goto('http://www.google.com')
browser.text_field(:name, 'q').value = word_to_search
browser.button(:name, 'btnG').click
puts browser.text.include? word_to_check

Suppose we have to interface Excel 2011 on a Mac with this simple script that takes a couple of input parameters and returns a result

test.sh

#!/bin/bash --login
rvm use jruby > NULL
ruby simpletest.rb $1 $2

We write a shell you can invoke via VBA code; Please be aware to set proper permission chmod u+x test.sh The shebang: #!/bin/bash --login this is required in order to run rvm to select jruby ( that is the only ruby supported by celerity

VBA Module ( User Defined Function )

Public Function SimpleTest(sWordToSearch, sWordToCheck)
    Dim sCommand As String
    sCommand = "do shell script ""cd /Users/ronda/projects/so-test1; ./test.sh "
    sCommand = sCommand & sWordToSearch & " "
    sCommand = sCommand & sWordToCheck & """"
    SimpleTest = MacScript(sCommand)
End Function

Note in this code: /Users/ronda/projects/so-test1 is the path to our scripts

Usage & Testing

Testing the User Defined Function in a formula

SimpleTest User Defined Function Usage

Btw the diagnosis of problems may be streamlined by running the script to a lower level:

Testing in the AppleScript edtor

Testing the shell script in terminal

$ cd ~/projects/so-test1  
$ ./test.sh Ruby Language
true
$ ./test.sh Fish Language
false

Testing the jruby script standalone

$ rvm use jruby
Using /Users/ronda/.rvm/gems/jruby-1.7.11
$ ruby simpletest.rb Ruby Language
true
$ ruby simpletest.rb Ruby Fish    
false