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"

Try this `excel.run "Test!WriteToA1"` – Siddharth Rout Jun 09 '12 at 21:28 — Siddharth Rout, Jun 09 '12 at 21:28

score 2 · Accepted Answer · edited Oct 05 '15 at 07:50

2

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

edited Oct 05 '15 at 07:50

Lernkurve

20,203
28
86
118

answered Jun 10 '12 at 04:27

Pynner

1,007
10
22

any chance that we could write / modify a marco using win32ole ?, or directly handle in ruby? some library wanted, which I could only find in "VBA Studio -> Reference", and I prefer the marco to be dynamically generated, any clue appeciated :) , @Lernkurve – Jack Wu Jan 11 '19 at 01:35
Wow, I am looking for this solution too, I have a requirement of writing / modifying macro from ruby. – Jesse JunJing Dec 05 '21 at 00:48

How do I run an Excel macro from Ruby?

1 Answers1

Linked