How do I run an Excel macro from Ruby?

Question

Problem

I have this ten something year old Excel workbook with a gazillion lines of VBA code in it some of which I have to update. So I had this crazy idea of writing unit tests in Ruby...

Question

How can I call an Excel macro from Ruby?

What I have so far

I have

• an Excel workbook called "C:\temp\Test.xlsm"
• with an sheet called "Sheet1" and
• a cell "A1".

Furthermore, this Excel workbook

• contains a module called "Module1"
• with a macro called WriteToA1() and
• another macro called ClearA1()

Plus, I have a Ruby script looking like this:

require 'test/unit'
require 'win32ole'

class TestDemo < Test::Unit::TestCase
   def testExcelMacro
    # Arrange
    excel = WIN32OLE.new("Excel.Application")
    excel.Visible = true
    excel.Workbooks.Open('C:\temp\Test.xlsm')

    # Act
    excel.run "Sheet1!WriteToA1"

    # Assert
    worksheet = excel.Workbooks.ActiveWorkbook
    assert_equal("blah", worksheet.Range("A1").Value)

    excel.Quit  
   end
end

Exception

I get this exception

WIN32OLERuntimeError: (in OLE method `run': )
    OLE error code:800A03EC in Microsoft Excel
      Cannot run the macro 'Sheet1!WriteToA1'. The macro may not be available in this workbook or all macros may be disabled.
    HRESULT error code:0x80020009
      Exception occurred.

I have enabled all macros in Excel as described here.

Excel is being started, "Test.xlsm" is opened. Something must be wrong with the line:

excel.run "Sheet1!WriteToA1"

I have also tried this:

excel.run "Sheet1!Module1.WriteToA1"

Answer 1

All you need to provide the OLE module is the macro name

excel.run('WriteToA1')

Also note, if you want to run a macro with an arguement, you use:

excel.run('MarcoWithArgs', "Arg")