RubyからExcelのマクロを実行する方法

rubyからwin32oleを使ってExcelを操作できることは良く知られていますが、それだけではrubyから操作するだけで、マクロを使用しなくては実行できない高度な処理を行う事はできません。

この記事は、rubyからexcelのマクロを呼び出して実行する処理をまとめたものです。
これにより、今までのマクロを使いまわしたり、いちいちマクロをrubyで書き直さなくても、excelでマクロを登録してあとはrubyから保存したマクロの名前から実行するだけで処理ができます。


今回の処理内容と実行するためのファイルを紹介します。
【処理内容】
１．txtファイルからデータを読み込む
２．excelにデータを転写
３．excelに予め保存しておいたFFTを行うマクロを実行　　←今回のキモ
４．excelを別名で保存

【ファイル】
data.txt　・・・　読み込むデータファイル
excel.rb　・・・　実行するルビーのファイル
excelMacro.vbs　・・・　エクセルでマクロを実行するためのファイル。これをrubyを通して動かす
FFTtemp.xlsm　・・・　FFTのマクロを記録してあるexcelファイル。


早速、プログラムを見ていきましょう。
(プログラムは以前、株価情報を周波数解析しようとした際のもの)

＿＿　data.txt　＿＿
20150706 494 498 470 478 14,098,300 478
20150703 495 503 490 502 12,114,000 502
20150702 480 517 479 492 25,980,700 492

・

・

・

という内容が永遠と続きます
＿＿　data.txt　ここまで　＿＿

＿＿　excel.rb　＿＿
#
# excelとrubyでFFT
#
require 'date' # Dateクラス読み込み
require 'win32ole' # Win32OLE(COM)の読み込み

# 文字コード指定
Encoding.default_external = 'UTF-8'

# 現在時刻を取得
d = DateTime.now

# データ格納用変数初期化
data = Array.new(4001).map{Array.new(25,0)} # 2次元配列を初期化
i = 0 # 配列管理用の変数を定義
j = 0 # 配列管理用の変数を定義

# データを変数に格納
File.open('data.txt') do |file|
file.each_line do |labmen|
labmen.split("\t").each do |dat|
data[i][j] = dat.chomp
j = j + 1
end
i = i + 1
j = 0
end
end
i = 0
j = 0

# Win32 ネイティブ版、Cygwin版の違いに関わらず絶対パスを取得する
def getAbsolutePath filename
  fso = WIN32OLE.new('Scripting.FileSystemObject')
  return fso.GetAbsolutePathName(filename)
end

excel = WIN32OLE.new('Excel.Application') # Excel.ApplicationのCOMオブジェクトを作成
excel.visible = true # windowの表示の有無

# ファイルを開く
workbook = excel.Workbooks.Open(getAbsolutePath("FFTtemp.xlsm"))

# Excelファイルに転写
for i in 0..127 do
if data[i][0].to_s == "0" then
break
end

workbook.sheets[1].rows[11 + i].columns[2] = data[i][0].to_s
workbook.sheets[1].rows[11 + i].columns[3] = data[i][1].to_s
workbook.sheets[1].rows[11 + i].columns[4] = data[i][2].to_s
workbook.sheets[1].rows[11 + i].columns[5] = data[i][3].to_s
workbook.sheets[1].rows[11 + i].columns[6] = data[i][4].to_s
workbook.sheets[1].rows[11 + i].columns[7] = data[i][5].to_s
workbook.sheets[1].rows[11 + i].columns[8] = data[i][6].to_s
end

# ファイル名を決定
filename = "FFT_" + d.strftime("%Y%m%d").to_s + "_" + d.strftime("%H%M%S").to_s + ".xlsm"

# 保存
workbook.saveAs(getAbsolutePath(filename))

# 終了処理
workbook.Close # ファイルを閉じる
excel.Quit # Excel.Applicationの終了

# コマンドラインからVBSを利用してアドインの読み込みとマクロの実行
puts `start ./excelMacro.vbs #{filename} FFTupdate128` # start ./FFTupdate128.vbs ファイル名 マクロ名
＿＿　excel.rb　ここまで　＿＿

重要なのは一番最後の行のexcelMacro.vbsを実行する部分のみです。



＿＿　excelMacro.vbs　＿＿
'
' コマンドラインからの実行でExcelのマクロを実行・保存
'
' ※他のexcelファイルを開いているとエラーが発生する模様

' コマンドライン引数（パラメータ）の取得
Set oParam = WScript.Arguments
cnsBook = oParam(0)
cnsMacro = oParam(1)


' 本スクリプトファイルのフォルダ名の取得
strPath = Replace(WScript.ScriptFullName, WScript.ScriptName, "")
' Excelのオブジェクトの参照を取得
Set objExcelApp = CreateObject("Excel.Application")
' 自動起動マクロの抑止(イベント停止)
objExcelApp.EnableEvents = False
' Excelウィンドウを表示
objExcelApp.Visible = True
' 指定のワークブックを開く(同一フォルダ)
objExcelApp.Workbooks.Open(strPath & cnsBook)


' Shell関数を使ってATPVBAEN.XLAMを起動
' ※["""]～["""]で囲む
strPath2 = """C:\Program Files (x86)\Microsoft Office\Office14\Library\Analysis\ATPVBAEN.XLAM"""
Set objWShell = CreateObject("WScript.Shell")
objWShell.Run strPath2, 0, False

' 指定のマクロを実行
objExcelApp.Run "'" & strPath & cnsBook & "'!" & cnsMacro


' 各種の確認ダイアログを非表示
objExcelApp.DisplayAlerts = False
'ワークブックの保存
objExcelApp.Workbooks(1).Save


' イベント停止解除
objExcelApp.EnableEvents = True
' Excel終了
objExcelApp.Quit
Set objExcelApp = Nothing

＿＿　excelMacro.vbs　ここまで　＿＿

ここで重要なのは、真ん中あたりの

　' Shell関数を使ってATPVBAEN.XLAMを起動

　' ※["""]～["""]で囲む

　strPath2 = """C:\Program Files (x86)\Microsoft Office\Office14\Library\Analysis\ATPVBAEN.XLAM"""

　Set objWShell = CreateObject("WScript.Shell")

　objWShell.Run strPath2, 0, False

の部分です。

実はrubyからwin32oleを通してマクロを実行しようとしても上手く動かないのですが、その理由はマクロを実行するために必要なアドインである「ATPVBAEN.XLAM」がwin32oleを通してexcelを実行した際には起動されないからです。（バグ・・・なのかな？）

そこで、これを起動しておいてあげる事によってrubyからマクロを実行することが可能になるということです。

＿＿　FFTtemp.xlsm　＿＿
略
＿＿　FFTtemp.xlsm　ここまで　＿＿



ここまできて、なぜ.vbsファイルが必要なのか、という疑問が出てくるかもしれませんが、たしか、rubyからでは何か不都合があったような気がしますが、実はこれを書いたのが1年前なのでわすれました。。。
とりあえず、このvbsファイルとその使い方である↓

# コマンドラインからVBSを利用してアドインの読み込みとマクロの実行
puts `start ./excelMacro.vbs #{filename} FFTupdate128` # start ./FFTupdate128.vbs ファイル名 マクロ名


だけ分かれば使えるはずです。