问题描述
问题
我有十年前的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宏?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!