Ruby: Win32OLEでの Excelマクロの操作

ExcelのインストールされているWindows環境ではExcelを Win32OLE という仕組みで 利用することが可能です。この仕組みを使うとExcelのVBAで出来ることはRubyからも 同様に出来るようになります。このページを読む前にOLEでの操作のページ「 Excel操作(OLE) 」を読んで下さい。

このページではExcel内のマクロを修正したり、置き換えたりする方法を紹介します。

このページの元と成るコードは Ruby 1.8.7 で利用した物です。

まず、ExcelのマクロはVBProjectモジュール内のVBComponentsの中にあります。 このVBProjectモジュールを外部から操作するにはExcel2003以後のバージョンでは セキュリティ設定が必要と成ります。 具体的には各バージョンのExcelのセキュリティ設定ダイアログで下記の項目にチェックを入れて アクセスを許可してください。
「Visual Basic プロジェクトへのアクセスを信頼する」

上記の設定が無い場合、VBProjectを操作しようとするとエラーに成ります。

ExcelのマクロはVBProjectモジュール内のVBComponentsの中にありますので VBComponentsを順に調べると見つけられます。

下記のコードと同じ場所にbook1.xlsを作り適当にマクロの記録でマクロを作って置いて下さい。 その後このコードを実行するとbook1.xls内に記録されたマクロが表示されます。

#! ruby -Ks
# -*- mode:ruby; coding:shift_jis -*-
$KCODE='s'
require 'excel'

openExcelWorkbook('book1.xls') do |book|
  book.VBProject.VBComponents.each do |compo|
    compo_name = compo.Name
    print "\n---------- book1.xls has component : #{compo_name}  -------\n"
    n = compo.CodeModule.CountOfLines
    if n > 0
      print compo.CodeModule.Lines(1,n)
    end
  end
end

VBProjectのVBComponentsをeachで順に見て、CodeModuleのライン数(CountOfLines)が ゼロでないものについてマクロのコードの1行目から最後の行まで(Lines(1,n))を 表示させています。

マクロのコードを修正したい場合にはモジュールの名前で対象を絞り、 各行毎に見て修正していけばＯＫです。

下記の例はモジュール名がModuleで始まるものの中の 「マクロの記録」と言う文字列を「マクロの修正」に書き換えます。

#! ruby -Ks
# -*- mode:ruby; coding:shift_jis -*-
$KCODE='s'
require 'excel'

openExcelWorkbook('book1.xls') do |book|
  book.VBProject.VBComponents.each do |compo|
    if compo.Name =~ /^Module/
      n = compo.CodeModule.CountOfLines
      if n > 0
        compo.CodeModule.Lines(1,n).each_with_index do |line,i|
          line.chomp!
          if line =~ /マクロ記録/
            line.sub!(/マクロ記録/,'マクロの修正')
            compo.CodeModule.ReplaceLine(i+1,line)
          end
        end
      end
    end
  end
  book.save
end

注意点はReplaceLineに渡す文字列に改行文字を入れない事です。 改行がある文字列を渡すと行の追加のような動作になり、 想定外の変更になってしまいます。

この例では line.chomp! で改行を削除してから処理しています。

また行数が１始まりですからeach_with_indexを使うときには+1して下さい。

変更後は book.save で保存することを忘れないで下さい。

操作するモジュールが固定の場合、下記の様に book.VBProject.VBComponents('Module1').CodeModule と 直接指定して操作することが出来ます。

#! ruby -Ks
# -*- mode:ruby; coding:shift_jis -*-
$KCODE='s'
require 'excel'

openExcelWorkbook('book1.xls') do |book|
  code = book.VBProject.VBComponents('Module1').CodeModule
  n = code.CountOfLines
  if n > 0
    code.Lines(1,n).each_with_index do |line,i|
      line.chomp!
      if line =~ /マクロの記録/
        line.sub!(/マクロの記録/,'マクロの修正')
        code.ReplaceLine(i+1,line)
      end
    end
  end
  book.save
end

行ごとのチェックが必要ない場合には一旦文字列として全体のコードを 取出して、修正し、丸ごと書き換える方法も使えます。

#! ruby -Ks
# -*- mode:ruby; coding:shift_jis -*-
$KCODE='s'
require 'excel'

openExcelWorkbook('book1.xls') do |book|
  code = book.VBProject.VBComponents('Module1').CodeModule
  n = code.CountOfLines
  if n > 0
    macro = code.Lines(1,n)          # 内容取出し
    macro.gsub!(/マクロ/,'Macro')    # 修正
    code.DeleteLines(1,n)            # 全行消去
    code.AddFromString(macro)        # 修正内容に置換え
  end
  book.save
end

利用しそうな関数を列記しておきます。修正したら book.save をお忘れなく。

(注意) 行番号は 1 始まりです。

• CodeModule.Name

  モジュール名

• CodeModule.CountOfLines

  マクロの行数

• CodeModule.Lines(開始行,行数)

  開始行から行数分のマクロの内容

• CodeModule.ReplaceLine(行番号, 文字列)

  行番号の行の内容を文字列に置き換える。改行を含ませないこと。

• CodeModule.InsertLines(行番号,文字列)

  指定した行番号の前に挿入されますので、行番号は挿入した行の番号と成ります。 改行を含ませないこと。

• CodeModule.DeleteLines(開始行,削除行数)

  指定した行を削除します。1行目から最終行まで全行削除することで add系の関数で全体を置き換えることが出来ます。

  code = book.VBProject.VBComponents('Module1').CodeModule
  code.DeleteLines(1,code.CountOfLines)   #全行削除

• CodeModule.AddFromFile(ファイル名)

  ファイルの内容を先頭に追加します。ファイル名はフルパスで指定して下さい。

  code = book.VBProject.VBComponents('Module1').CodeModule
  code.DeleteLines(1,code.CountOfLines)   #全行削除
  code.AddFromFile('c:\\code.txt')        #ファイルの内容を追加
  

• CodeModule.AddFromString(文字列)

  文字列を先頭に追加します。文字列は複数行を指定可能です。

  openExcelWorkbook('book1.xls') do |book|
    book.VBProject.VBComponents('Module1').CodeModule.AddFromString("'追加コード\n'2行目")
    book.save
  end

下記の様なスクリプトでExcelのマクロをファイルに取出しておいて、gitやsubversions等で管理することで マクロのバージョン管理が出来ます。

取出す時のファイル名の拡張子や取出し先 等はお好みで変更してください。

終了ボタンは作っていませんので、 ウインドウ右上の「Ｘ」の閉じるボタンで終了してください。

#! ruby -Ks
# -*- mode:ruby; coding:shift_jis -*-
$KCODE='s'

require 'excel'
require 'tk'

STDOUT.sync = true

def save_macros file
  if file =~ /\.xls/i
    b_name = File.basename(file,".*")
    dir = File.dirname(file)
    
    openExcelWorkbook(file) do |book|
      book.VBProject.VBComponents.each do |compo|
        compo_name = compo.Name
        n = compo.CodeModule.CountOfLines
        if n > 0
          path = dir + '/' + b_name + '_' + compo_name + '.txt'
          print "Output : #{path}\n"
          File.open(path,'w').write(compo.CodeModule.Lines(1,n))
        end
      end
    end
  end
end

Tk.root.title('Save Excel macro')

b1 = TkButton.new('text' => 'Excelファイル選択ボタン').pack('fill'=> 'x')
b1.command {
  file = Tk.getOpenFile({'title' => 'Excelファイル選択','filetypes'=>[['excel','.xls']],})
  if file != ''
    save_macros file
  end
}

Tk.mainloop