rubyによるExcelのマクロの取扱い

以下に掲げるrubyスクリプトのサンプルは、Excel操縦用ライブラリ exlap.rb 最新版: exlap_latest.zipに同梱のものを利用するとの前提で書かれています。

目次

mcr01.rb

マクロの組み込みと実行

mcr02.rb

マクロソースコードの取り出し

mcr03.rb

クラスモジュールの組み込みと実行

mcr04.rb

コンポーネントの情報を取得

mcr05.rb

ファイルを介さずにマクロを別のワークブックにコピー

mcr06a.rb

マクロファイルの書き出し

mcr06b.rb

マクロファイルの読み込み

mcr07.rb

異なるモジュールに同名のマクロが登録されている時の扱い方

mcr08.rb

マクロにショートカットキーを設定する

mcr09.rb

マクロの組み込みと実行。xls | xlsm 両方に対応

mcr10.rb

「マクロの記録」と「記録終了」の効力を確かめる

mcr10b.rb

RecordMacro()を使ってみる

cmdbars.rb

CommandBarsのId, Captionを取得

mcr11.rb

UserFormのサンプル

mcr01.rb

#! ruby -Ks
  # マクロの組み込みと実行
require "exlap"
str = DATA.read  # マクロソースコードをstrに代入
filename = "mcr01.xls"
Exlap.new(filename) do |wb|
  wb.macro_add(str)  # マクロの組込み
  ss = wb.fes  # 空のワークシートを選択
  ss.Activate
  wb.run("Macro1")  # Macro1を実行
  wb.save
end

__END__
Sub Macro1()
    Cells(1,1).Value = "test"
End Sub

mcr02.rb

#! ruby -Ks
  # マクロソースコードの取り出し
require "exlap"
str = DATA.read  # マクロ記述をstrに代入
filename = "mcr02.xls"
Exlap.new(filename) do |wb|
  wb.macro_add(str)  # マクロの組込み
  ss = wb.fes  # 空のワークシートを選択
  ss.Activate
  wb.run("MacroSetValue")
  wb.run("MacroSort")
  wb.save
  str2 = wb.macro_code()
  File.open("mcr02.txt", "w") {|ff|  ff.write str2}
end

__END__
Sub MacroSetValue()
    Dim i As Integer
    Dim fruit As Variant
    fruit = Array("りんご", "メロン", "ぶどう", "パニナップル")
    For i = LBound(fruit) To UBound(fruit)
        Cells(i+1, 1).Value = i+1
        Cells(i+1, 2).Value = fruit(i)
    Next i
End Sub

Sub MacroSort()
    Application.CommandBars.FindControl(ID:=211).Execute  ' 降順で並べ替え
End Sub

mcr03.rb

#! ruby -Ks
  # クラスモジュールの組み込みと実行
require "exlap"
cls_str, mdl_str = DATA.read.split(/\n##\n/)
filename = "mcr03.xls"
Exlap.new(filename) do |wb|
  wb.macro_add(cls_str, 2, "Persons")  # クラスモジュールの組み込み
  wb.macro_add(mdl_str)  # 標準モジュールの組み込み
  ss = wb.fes  # 空のワークシートを選択
  ss.Activate
  wb.run("TestPersons")
  ss.range_autofit  # 行の高さ・列の幅の自動調整
  wb.save
end

__END__
' Class Module: Persons
Sub Title(row As Integer)
    Range(Cells(row,1), Cells(row,5)).Value = Array("氏名", "生年月日", _
        "年齢", "月齢", "日齢")
End Sub

Sub Contents(row As Integer, name As String, birthday As String)
    Cells(row,1).Value = name
    Cells(row,2).Value = birthday
    Cells(row,3).Formula = "=DATEDIF(RC[-1],TODAY(),""Y"")"
    Cells(row,4).Formula = "=DATEDIF(RC[-2],TODAY(),""M"")"
    Cells(row,5).Formula = "=DATEDIF(RC[-3],TODAY(),""D"")"
End Sub

##
' standard module
Sub TestPersons()
    Dim psn As Persons
    Set psn = New Persons
    psn.Title 1
    psn.Contents 2, "加藤", "1970/01/01"
    psn.Contents 3, "鈴木", "1980/02/02"
    psn.Contents 4, "戸山", "1990/03/03"
End Sub

mcr04.rb

#! ruby -Ks
  # コンポーネントの情報を取得
require "exlap"
filename = "mcr03.xls"
Exlap.new(filename) do |wb|
      # コンポーネントの type, name を出力
  puts "コンポーネント一覧"
  mlist = wb.macro_list()
  mlist.each do |type, name|
    printf("type:%d  name:%s\n", type, name)
  end
  printf("\n")
      # プロシージャ名一覧の出力
  puts "プロシージャ名一覧"
  hs = wb.macro_name
  hs.each do |key, proc_names|
    type, name = key
    printf("%d %s: ", type, name)
    puts proc_names.join(", ")
  end
      # ソースコードをファイルに出力
  mlist.each do |type, name|
    str = wb.macro_code(type, name)
    fname = "#{name}_code.txt"
    File.open(fname, "w") {|ff|  ff.write str}
  end
end

mcr05.rb

#! ruby -Ks
  # ファイルを介さずにマクロを別のワークブックにコピー
require "exlap"
xl = Exlap.new
xl.opens_once("mcr03.xls", "mcr05.xls") do |wb1, wb2|
  wb2.macro_remove  # 念のためwb2のマクロを削除
  mlist = wb1.macro_list
  mlist.each do |type, name|
    str = wb1.macro_code(type, name)
    wb2.macro_add(str, type, name)
  end
  ss = wb2.fes  # 空のワークシートを選択
  ss.Activate
  wb2.run("TestPersons")
  ss.range_autofit  # 行の高さ・列の幅の自動調整
  wb2.save
end
xl.quit

mcr06a.rb

#! ruby -Ks
  # マクロファイルの書き出し
require "exlap"
Exlap.new("mcr03.xls") do |wb|
  wb.macro_export
end

mcr06b.rb

#! ruby -Ks
  # マクロファイルの読み込み
require "exlap"
Exlap.new("mcr06.xls") do |wb|
  wb.macro_remove  # 念のためマクロを削除
  Dir.glob("*.bas\0*.cls\0*.frm") do |filename|
    wb.macro_import(filename)
  end
      # 読み込んだマクロを試しに実行してみる
  ss = wb.fes  # 空のワークシートを選択
  ss.Activate
  wb.run("TestPersons")
  ss.range_autofit  # 行の高さ・列の幅の自動調整
  wb.save
end

mcr07.rb

#! ruby -Ks
  # 異なるモジュールに同名のマクロが登録されている時の扱い方
require "exlap"
str1, str2 = DATA.read.split(/\n##\n/)  # マクロのソースコードを代入
filename = "mcr07.xls"
Exlap.new(filename) do |wb|
  wb.macro_add(str1, 1, "Module1")  # マクロの組込み
  wb.macro_add(str2, 1, "Module2")  # マクロの組込み
  ss = wb.fes  # 空のワークシートを選択
  ss.Activate
  wb.run("Module1.Macro1")
  wb.run("Module2.Macro1")
  wb.save
end

__END__
Sub Macro1()
    Dim i As Integer
    Dim fruit As Variant
    fruit = Array("りんご", "メロン", "ぶどう", "パニナップル")
    For i = LBound(fruit) To UBound(fruit)
        Cells(i+1, 1).Value = i+1
        Cells(i+1, 2).Value = fruit(i)
    Next i
End Sub

##
Sub Macro1()
    Application.CommandBars.FindControl(ID:=211).Execute  ' 降順で並べ替え
End Sub

mcr08.rb

#! ruby -Ks
  # マクロにショートカットキーを設定する
require "exlap"
str = DATA.read  # マクロ記述をstrに代入
filename = "mcr08.xls"
Exlap.new(filename) do |wb|
  wb.macro_add(str)  # マクロの組込み
  app = wb.app.obj
  app.MacroOptions({'Macro'=>"MacroSetValue", 'ShortcutKey'=>"U"})
  app.MacroOptions({'Macro'=>"MacroSort", 'ShortcutKey'=>"Y"})
  wb.save
end

__END__
Sub MacroSetValue()
    Dim i As Integer
    Dim fruit As Variant
    fruit = Array("りんご", "メロン", "ぶどう", "パニナップル")
    For i = LBound(fruit) To UBound(fruit)
        Cells(i+1, 1).Value = i+1
        Cells(i+1, 2).Value = fruit(i)
    Next i
End Sub

Sub MacroSort()
    Application.CommandBars.FindControl(ID:=211).Execute  ' 降順で並べ替え
End Sub

mcr09.rb

#! ruby -Ks
  # マクロの組み込みと実行。xls | xlsm 両方に対応
require "exlap"
str = DATA.read  # マクロ記述をstrに代入
xl = Exlap.new
filename = (xl.excel_version < 12.0) ? "mcr09.xls" : "mcr09.xlsm"
xl.opens(filename) do |wb|
  wb.macro_add(str)  # マクロの組込み
  ss = wb.fes  # 空のワークシートを選択
  ss.Activate
  wb.run("Macro1")  # Macro1を実行
  wb.save
end
xl.quit

__END__
Sub Macro1()
    Cells(1,1).Value = "test"
End Sub

mcr10.rb

#! ruby -Ks
  # 「マクロの記録」と「記録終了」の効力を確かめる
require "exlap"
str = nil
filename = "mcr10.xls"
Exlap.new(filename) do |wb|
  app = wb.app.obj
  ss = wb.fes
  ss.Activate
  app.CommandBars.FindControl({'ID'=>184}).Execute  # マクロの記録
      # ↑ ダイアログが出るのでGUI操作で応答
  rng = ss.Range("A1:B3")
  rng.Value = [%w(3 rat), %w(1 cat), %w(2 dog)]
  app.CommandBars.FindControl({'ID'=>210}).Execute  # 昇順で並べ替え
  app.CommandBars.FindControl({'ID'=>2186}).Execute  # 記録終了
  str = wb.macro_code
  wb.save
end
File.open("mcr10.txt", "w") {|ff|  ff.print str}

mcr10b.rb

#! ruby -Ks
  # 「マクロの記録」と「記録終了」の効力を確かめる
  # RecordMacro() も使ってみる
require "exlap"
xl = Exlap.new
m_start = xl.obj.CommandBars.FindControl('ID'=>184)
m_stop = xl.obj.CommandBars.FindControl('ID'=>2186)
m_sort = xl.obj.CommandBars.FindControl('ID'=>210)
vba_code = DATA.read
str = nil
filename = "mcr10b.xls"
xl.opens(filename) do |wb|
  ss = wb.fes
  ss.Activate
  m_start.Execute  # マクロの記録
      # ↑ ダイアログが出るのでGUI操作で応答
  rng = ss.Range("A1:B3")
  rng.Value = [%w(3 rat), %w(1 cat), %w(2 dog)]
  xl.obj.RecordMacro(vba_code)
  m_sort.Execute  # 昇順で並べ替え
  m_stop.Execute  # 記録終了
  str = wb.macro_code
  wb.save
end
xl.quit
File.open("mcr10b.txt", "w") {|ff|  ff.print str}

__END__
Range("A1:B1").Value = Array(3, "rat")
Range("A2:B2").Value = Array(1, "cat")
Range("A3:B3").Value = Array(2, "dog")

cmdbars.rb

#! ruby -Ks
  # CommandBarsの Id, Caption をワークシートに書き出す
require "exlap"
ary = []
filename = "CommandBars.xls"
Exlap.new(filename) do |wb|
  app = wb.app.obj
  controls = {}
  app.CommandBars.each do |bars|
    key = [bars.Index, bars.Name]
    controls[key] = []
    app.CommandBars(bars.Name).Controls.each do |target|
      controls[key] << [target.Index, target.Id, target.Caption]
    end
  end
  ary << [nil, "Index", "Id", "Caption"]
  controls.sort.each do |key, val|
    i, name = key
    ary << ["*#{i} #{name}"]
    val.sort.each do |idx, id, caption|
      ary << [nil, idx, id, caption]
    end
  end
  ly,lx = [ary.size, 4]
  ss = wb.fes
  ss.rr(1,1, ly,lx).Value = ary
  for y in 2..ly
    if ary[y-1][0]
      ss.rr(y,1, y,4).MergeCells = true
    end
  end
  ss.range_autofit
  wb.save
end
    # テキストファイルとして書き出す
body_str = ''
ary.each do |row|
  str = row.join("\t")
  body_str += str.gsub(/(\d)\.0+\t/, "\\1\t") + "\n"
end
File.open("CommandBars.txt", "w") {|ff|  ff.write body_str}

mcr11.rb

#! ruby -Ks
  # the sample for UserForm
require "exlap"
uf_str, mdl_str = DATA.read.split(/\n##\n/)
filename = "mcr11.xls"
Exlap.new(filename) do |wb|
  uf = wb.macro_add(uf_str, 3, "SampleForm")  # add UserForm component
  uf.Designer.Controls.Add("Forms.TextBox.1")  # add TextBox
  uf.Designer.Controls.Add("Forms.ListBox.1")  # add ListBox
  uf.Designer.Controls.Add("Forms.CommandButton.1")  # add CommandButton
  wb.macro_add(mdl_str)  # add standard module component
  wb.app.obj.MacroOptions('Macro'=>"TestSampleForm", 'ShortcutKey'=>"U")
  wb.save
end

__END__
' UserForm module
Private Sub CommandButton1_Click()
    Range("A1").Value = Me.TextBox1.Value
    Range("B1").Value = Me.ListBox1.Value
    Unload Me
End Sub

Private Sub UserForm_Initialize()
    With TextBox1
        .AutoSize = True
    End With
    With ListBox1
        .AddItem "cat"
        .AddItem "dog"
        .AddItem "fox"
    End With
    With CommandButton1
        .Caption = "Finish"
    End With
End Sub

##
' standard module
Sub TestSampleForm()
    SampleForm.Show
End Sub

exlapのページへ戻る