ワークブックのオープンとクローズ – ExcelVBAの散策とruby

2016/05/14

参考サイト：

• Excel VBA 入門講座 ワークブックを開く
• Excel VBA 入門講座 ワークブックを閉じる

《目次》

• 1.　概要
  • (1)　ワークブックのオープン
  • (2)　ワークブックのクローズ
• 2.　マクロを登録するスクリプト
• 3.　win32oleのスクリプト
• 4.　exlapのスクリプト
• 5.　その他(ファイル選択ダイアログ、ショートカットキー割当ての際の注意)
  • (1) ファイル選択ダイアログ
  • (2) マクロへのショートカットキー割り当てとワークブックのオープン

1.　概要

　ワークブックのオープン(既にあるワークブックを開く)は、Workbooks.Openメソッドで行います。

　Workbooks.Open "C:\test.xls" とすれば test.xls が開かれます。

　ワークブックのクローズは、Closeメソッドで行います。

　ActiveWorkbook.Close のように記述します。

(1)　ワークブックのオープン

　Workbooks.Open "C:\test.xls" とすれば、test.xls が開かれて、それがアクティブワークブックになります。

　C:\test.xls が存在しないときはエラーになります。

　ワークブックを開いて、その第１ワークシートのA1に test と書き込むマクロを掲げます。

Sub Macro1()
    Workbooks.Open "C:\test.xls"
    Worksheets(1).Range("A1").Value = "test"
End Sub

　エラーになるケースがもう一つあります。「既に開いているワークブックと同じ名前のブックを開こうとした場合」にエラーになります。

　ここの事情には少しややこしい面があります。

　既にC:\test.xls を開いているものとします。その上で、D:\MyFiles\test.xls を開こうとした場合、これはエラーになります。test.xls というワークブック名が同じだからです。ドライブ名やフォルダ名が違っていても、ワークブック名が同じだと駄目です。

　Openメソッドには多くのパラメータがあります。名前だけあげると下のとおり。

FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword,
IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify,
Converter, AddToMru, Local, CorruptLoad

　ワークブックを保存するための SaveAsメソッドと共通するパラメータもいくつかありますが、いろいろ違いがあります。

　SaveAsではワークブックのファイル形式(FileFormat)をかなり意識する必要がありました。xls, xlsx, xlsm などの違いに注意しなければなりませんでした。

　それに対して、Openの方ではファイル名を test.xls, test.xlsx, test.xlsm などとしてやれば、ファイル名の拡張子に合わせたファイル形式でオープンされるようです。

　主なパラメータに関して簡単な説明を掲げておきます。

FileName

開くブックのファイル名。ドライブ＋フォルダの指定がないと、通常はユーザーのドキュメントフォルダ(マイドキュメント)のファイルとみなされます。

ReadOnly

Trueにすると、ブックを読み取り専用モードで開きます。

Password

パスワード保護されたブックについて、それを開くのに必要なパスワードを指定します。省略すると、パスワードの入力を求めるダイアログが出ます。

WriteResPassword

書き込み保護されたブックについて、書き込み可能にするためのパスワードを指定します。省略すると、パスワードの入力を求めるダイアログが出ます。

IgnoreReadOnlyRecommended

「読み取り専用を推奨」のブックであっても、このパラメータをTrueにすると、「推奨」のメッセージが出なくなります。ReadOnlyRecommendedを無視する訳です。

　読み取りパスワードを指定してオープンするケースを掲げておきます。

Sub Macro1()
    Workbooks.Open FileName:="C:\test.xls", Password:="abc"
End Sub

目次に戻る

(2)　ワークブックのクローズ

　ワークブックのクローズは Closeメソッドで行います。

　ActiveWorkbook.Close とすれば、アクティブワークブックを閉じることになります。

　ただし、そのワークブックに変更が加えられていると、保存するかどうかを問い合わせるダイアログが出ます。

　変更されているかどうかにかかわらず直ちにクローズしたい時は
ActiveWorkbook.Close False とします。パラメータとして False を指定します。変更されている場合、その結果は失われます。

　ワークブックを閉じる時に、合わせて保存もしたいという場合は、
ActiveWorkbook.Close True のように True を指定します。すると、そのワークブックの名前(FullNameプロパティ)で保存されます。

　別のファイル名で保存するなら、 ActiveWorkbook.Close True, "C:\test.xls" のように第２パラメータでファイル名を指定します。

　このとき、ファイル名の拡張子に注意して下さい。扱っているワークブックが xlsx形式(つまり最大列数が256でなく16384)の場合、拡張こを xls にすると、正しく読み込めないファイルになります。扱っている形式に合った拡張子にする必要があります。

目次に戻る

2.　マクロを登録するスクリプト

　ワークブックのオープンとクローズを行うマクロを登録します。

　これまでと同様に、マクロには Control + j というショートカットキーを割り当てます。Book1.xlsを開いてから、このショートカットキーを入力すると、次の処理が行われます。

• あらかじめ作成してある Book2.xls をオープンします。
• Book2.xlsの第１ワークシートの2行目と3行目に書き込みを行います。
• ワークブックをクローズしますが、その際、ワークブックを保存します。

　今回、Book1.xlsは、マクロを登録するためだけに作成するものです。Book1.xlsそのものには何ら処理を加えません。

 # encoding: Windows-31J
 require "./exlap"
 
 macro_str = <<'EOS'
 Sub Macro1()
     Dim TestFile As String
     TestFile = ActiveWorkbook.Path & "\Book2.xls"  ' テスト用ブックの名前
     Workbooks.Open TestFile
     Range("A2:B2").Value = Array("This is", ActiveWorkbook.FullName)
    Range("A3").Value = "=NOW()"
    Range("A3").NumberFormatLocal = "HH:MM:SS"
    ActiveWorkbook.Close True
End Sub
EOS

macro_name = "Macro1"
Dir.glob("Book*.xls") {|fname|  File.delete(fname)}
xl = Exlap.new
xl.opens("Book1.xls") do |wb|  # Book1.xlsにマクロを登録
    wb.macro_add(macro_str)
    wb.Application.MacroOptions("Macro"=>macro_name, "ShortcutKey"=>"j")
    wb.save
end
xl.opens("Book2.xls") do |wb|  # テスト用のBook2.xlsを作成
    wb.ss(1).Range("A1:C1").Value = "Test for", "Workbook", "Open"
    wb.save
end
xl.quit

　上のrubyスクリプトでは、Book1.xlsにマクロを登録する以外に、Book2.xls を作成しています。

　今回のテーマがワークブックのオープンなので、オープンするためのワークブックを用意します。

目次に戻る

3.　win32oleのスクリプト

　前述のマクロと同じ処理を行うwin32oleのスクリプトは下のようになります。

　先のマクロ登録用スクリプトでは、マクロを収納する入れ物として Book1.xls を設けましたが、今回はマクロを登録する訳ではないので Book1.xls のみを作成します。

　スクリプトの前半で Book1.xls を新規に作成し(1行目に書き込む)、一度それをクローズします。

　スクリプトの後半では、Book1.xlsを改めてオープンし、2行目と3行目に書き込んでからクローズします。その際、Book1.xlsを保存します。

 # encoding: Windows-31J
 require "win32ole"
 
 def fullpath(filename)
     fso = WIN32OLE.new("Scripting.FileSystemObject")
     return fso.GetAbsolutePathName(filename)
 end
 
 Dir.glob("Book*.xls") {|fname|  File.delete(fname)}
app = WIN32OLE.new("Excel.Application")
wb = app.Workbooks.Add()
ws = wb.Worksheets("Sheet1")
ws.Range("A1:C1").Value = "Test for", "Workbook", "Open"
wb.SaveAs(fullpath("Book1"), -4143)
wb.Close

test_file = fullpath("Book1.xls")
wb = app.Workbooks.Open(test_file)
ws = wb.ActiveSheet
ws.Range("A2:B2").Value = "This is", wb.FullName
ws.Range("A3").Value = "=NOW()"
ws.Range("A3").NumberFormatLocal = "HH:MM:SS"
wb.Close(true)
app.Quit

目次に戻る

4.　exlapのスクリプト

　同じ処理を行うexlapのスクリプトを掲げます。

　Workbooks.Open の代わりに exlap独自のメソッド book_open を使っています。これは、新規のワークブックと既存のブックの両法を扱うことができます。

　book_openのパラメータの与え方は、Workbooks.Open, Workbooks.Add と同じでかまいません。

　指定したファイル名が既存のものであれば Workbooks.Open を実行し、存在しないファイルなら Workbooks.Add を実行します。

　ワークブックのクローズは、wb.close を用いています。このメソッドのパラメータの与え方も VBAのCloseと同じです。

　なお、2016年５月12日に exlapを更新してアップロードしました。新しいexlapでないと下のスクリプトがうまく動作しないので留意して下さい。

　当シリーズのzip圧縮ファイルには、新しい exlap.rb, exl21.exe を同梱しました。

 # encoding: Windows-31J
 require "./exlap"
 
 Dir.glob("Book*.xls") {|fname|  File.delete(fname)}
 xl = Exlap.new
 wb = xl.book_open("Book1.xls")
 wb.ss(1).Range("A1:C1").Value = "Test for", "Workbook", "Open"
 wb.save
 wb.close

wb = xl.book_open("Book1.xls")
ws = wb.ss
ws.Range("A2:B2").Value = "This is", wb.FullName
ws.Range("A3").Value = "=NOW()"
ws.Range("A3").NumberFormatLocal = "HH:MM:SS"
wb.close true
xl.quit

目次に戻る

5.　その他(ファイル選択ダイアログ、ショートカットキー割当ての際の注意)

　ファイル選択ダイアログボックスを出して、オープンするワークブックをユーザーに選んでもらう方法、

それから、マクロにショートカットキーを割り当てる際に、ワークブックのオープンとの関連で経験したトラブルについて記します。

(1) ファイル選択ダイアログ

　ファイル選択ダイアログボックスを出す方法を二つ紹介します。

Application.GetOpenFilename

　ワークブックをオープンするやり方として、ダイアログボックスを出してユーザーにオープンするファイルを選択してもらうというのがあります。

　Application.GetOpenFilename を用いると、それが実現できます。

　このメソッドは、選択されたファイルの名前(文字列)をフルパスで返します。その返されたファイル名を Workbooks.Open に渡してやれば、ワークブックがオープンされます。

　サンプルのマクロは下のとおり。

Sub Macro1()
    Dim OpenFile As String
    OpenFile = Application.GetOpenFilename("すべてのファイル(*.*),*.*")
    MsgBox OpenFile & " を開きます"
    Workbooks.Open OpenFile
End Sub

Application.Dialogs

　もう一つの方法は、Application.Dialogs を用いるものです。マクロ記述は下のとおり。

Sub Macro1()
    Application.Dialogs(xlDialogOpen).Show
End Sub

　上のマクロを実行すると、ファイル選択ダイアログボックスが出ます。そこで何らかのファイルを選択すると、それが直ちにExcelでオープンされます。

　Application.GetOpenFilename の時のように Workbooks.Open を使わなくても、ワークブックがオープンされます。

　なお、Application.Dialogs の戻り値は True, False のどちらかです。

　ファイルが選択されてオープンに成功したときは True を返します。

　ファイル選択ダイアログでキャンセルボタンが押され、何も選択されなかったときは False を返します。

ファイル選択時の初期フォルダを設定

　Application.GetOpenFilename, Application.Dialogs のどちらも、Excelにとってのカレントフォルダからファイルを選択することになります。

　Excelにとってのカレントフォルダとは、通常、ユーザーのドキュメントフォルダ(マイドキュメント)です。

　ダイアログボックスの中でフォルダを別のところに移すことはできますが、別のフォルダを初期フォルダにしたい場合は ChDrive(ドライブの変更), ChDir(フォルダの変更)を利用するのが一般的でしょうか。

　ただ、ドライブとフォルダを一気に変更することはできないようだし(つまり別々に変更しなければならない)、ダイアログボックス終了後にExcelのカレントフォルダを元に戻す処理も必要になり、なかなか面倒です。

　そこで、Application.ExecuteExcel4Macro を使って kernel32 の呼び出しを行う方法を試してみます。マクロは下のようになります。

Sub Macro1()
    Dim folder As String
    folder = "D:\MyFile"
    Application.ExecuteExcel4Macro("CALL(""kernel32""," & _
        """SetCurrentDirectoryA"",""JC"",""" & folder & """)")
    Application.Dialogs(xlDialogOpen).Show
End Sub

　上は、D:\MyFile というフォルダの下にExcelファイルがあるとの想定で書かれています。D:\MyFile\Book1.xls などがあるとの想定です。

　Application.ExecuteExcel4Macro や kernel32 については、簡単に説明できないので省略しますが、あやしげな方法という訳ではないので「使えそうだ」と思われたら試してみて下さい。

　関連のスクリプトをzip圧縮ファイルに同梱しておきます。Book1.xlsにマクロを組み込み、オープンのテスト用に Book2.xls, Book3.xls の二つを作成しておく、というスクリプトです。ユーザーが選択したファイルにちょっと書き込みをしてから、保存・クローズします。

　同様の意味合いの win32ole, exlap のスクリプトも同梱しておきます。こちらは Book1.xls, Book2.xls の二つをオープンのテスト用として作成します。途中でファイル選択ダイアログが出ます。

目次に戻る

(2) マクロへのショートカットキー割り当てとワークブックのオープン

　ワークブックをオープンする際にシフトキーが押されているとマクロが動作しない、というのが仕様のようです。そのためだと思いますが、次のようなトラブルを経験しました。

　当シリーズのスクリプトでは、以前、マクロに Shift + Control + U というショートカットキーを割り当てていました。このショートカットキーを入力すればマクロが実行される、というようにしていた訳です。

　ところが、Workbooks.Open が出てくるマクロがちゃんと実行されません。ワークブックのオープンまでは実行されるのですが、そこで中断されてしまい、先に進みません。

　マクロの書き方がわるいのかとも思いましたが、Excelのメニューからマクロの実行を選んだときは正常に実行されます。

　あれこれ調べたところ、シフトキーの押し下げを伴わないショートカットキーだと大丈夫であることが分かりました。

　合わせて、Excelで使えるショートカットキーを改めて調べたところ、Control + U と Shift + Control + U には機能が割り当てられていることも発見(今更ですが)。

　そこで、ショートカットキーとして Control + j を割り当てるよう改めました。

　これで、Workbooks.Open が出てくるマクロも、ショートカットキーから正常に実行できるようになりました。

　マクロにショートカットキーを割り当てるときは、シフトキーの押し下げを伴わないものにするのが無難です。