如何从Ruby运行Excel宏？ [英] How do I run an Excel macro from Ruby?

问题描述

问题

我有十年前的Excel工作簿与一系列VBA代码，其中一些我必须更新。所以我有这个疯狂的想法，在Ruby中编写单元测试...

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...

问题

如何从Ruby调用Excel宏？

How can I call an Excel macro from Ruby?

我到目前为止

我有

• 一个名为C：\temp\Test.xlsm的Excel工作簿


• ，一张名为Sheet1的表格和


• 一个单元格A1。

此外，此Excel工作簿

Furthermore, this Excel workbook

• 包含一个名为Module1的模块


• 称为的宏称为WriteToA1（）和


• 另一个名为的宏ClearA1（）

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

另外，我有一个如下的Ruby脚本：

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

异常

我得到这个例外

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.

我已经在Excel中启用了所有的宏，如这里。

I have enabled all macros in Excel as described here.

Excel正在开始，Test.xlsm被打开。某行必须出错：

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

excel.run "Sheet1!WriteToA1"

我也尝试过：

excel.run "Sheet1!Module1.WriteToA1"

推荐答案

需要提供OLE模块是宏名称

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

这篇关于如何从Ruby运行Excel宏？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！