Excel ブックを操作するマクロの基礎
Excel VBA マクロで、Excel ブックを操作することが良くあります。
例えば、テンプレートとなる Excel ファイルをコピーしたり、Excel ファイルを開いたり、開いた Excel ファイルを操作したり、などです。
これらの操作をマクロで行う方法について記載します。
テンプレートとなる Excel ファイルをコピーするマクロ
マクロでファイルをコピーするためには以下の構文を使用します。
例えば、以下の場合を考えます。
- コピー元ファイル
- マクロファイルと同じフォルダにあるファイル「tmp.xlsx」
- コピーされるファイル
- マクロファイルと同じフォルダ内の「test.xlsx」
この場合は以下のように記述します。
FileCopy ThisWorkbook.Path & "\tmp.xlsx", ThisWorkbook.Path & "\test.xlsx"
既存の Excel ファイルを開くマクロ
Excel ファイルを開くためには以下の構文を使用します。
例えば、マクロファイルと同じフォルダに保存されているファイル「test.xlsx」を開くためには以下のように記述します。
Workbooks.Open ThisWorkbook.Path & "\test.xlsx"
開いたエクセルファイルを操作する方法
Excel ファイルを開いた後、続けてそのファイルを操作するためにはどうしたら良いでしょうか。
このためには、開いたファイルの Workbook オブジェクトを指定する必要があります。
ファイル名で指定する方法
Workbook オブジェクトの指定は以下の構文で行います。
例えば、Excel ファイル「test.xlsx」を指定したい場合は以下のように記述します。
Workbooks("test.xlsx")
さらに、test.xlsx のシート「Sheet1」を指定したい場合は以下のように記述します。
Workbooks("test.xlsx").Sheets("Sheet1")
ActiveWorkbook を使用する方法
新規に開いた Excel ファイル(Workbook オブジェクト)を操作する方法がもう一つあります。
現在アクティブな Workbook オブジェクトを示す ActiveWorkbook
を使用します。
例えば以下のように test.xlsx を開いたとします。
Workbooks.Open ThisWorkbook.Path & "\test.xlsx"
これを実行した直後は test.xlsx がアクティブな状態になるため、ActiveWorkbook
と指定すると Workbooks("test.xlsx")
と指定したのと同じ意味になります。
つまり、例えば以下の2行は同じ意味になります。
Workbooks("test.xlsx").Sheets("Sheet1")
ActiveWorkbook.Sheets("Sheet1")
Workbook 型変数に Workbook オブジェクトを格納してから操作する
Workbook オブジェクトは、Workbook 型変数に格納することができます。
Workbook オブジェクトを操作する際に、毎回 Workbooks("<ファイル名>")
の形式で対象 Workbook を指定するのは手間になることがあります。
Workbook 型の変数を使用する方がマクロが見やすくなる場合があります。
以下は、Excel ファイル「test.xlsx」を開いた後、その Workbook オブジェクトを Workbook 型変数 wb
に格納し、その後メッセージボックスで wb
の名前を表示するマクロです。
Sub test()
Dim wb As Workbook
Workbooks.Open ThisWorkbook.Path & "\test.xlsx"
Set wb = ActiveWorkbook
MsgBox wb.Name
End Sub
このマクロを実行すると「test.xlsx」とメッセージが表示され、wb
に test.xlsx の Workbook オブジェクトが格納できていることが分かります。
Excel ファイルを保存する
Excel ファイルを上書き保存する
既存の Excel ファイルを上書き保存する場合は以下の構文です。
以下のマクロは、test.xlsx を開いて編集した後、上書き保存するマクロです。
Sub test()
Dim wb As Workbook
Workbooks.Open ThisWorkbook.Path & "\test.xlsx"
Set wb = ActiveWorkbook
wb.ActiveSheet.Cells(1, 1).Value = "hoge"
wb.Save
End Sub
Excel ファイルを名前を付けて保存する
Excel ファイルを名前を付けて保存する構文は以下です。
以下のマクロは、新規に開いた Excel ファイルを sample.xlsx という名前で、マクロファイルと同じフォルダ内に保存するマクロです。
Sub test()
Dim wb As Workbook
Set wb = Workbooks.Add
wb.SaveAs ThisWorkbook.Path & "\sample.xlsx"
End Sub
新規の Excel ファイルを開く
新規の Excel ファイルを開く
以下の構文で新規の Excel ファイルを開くことができます。
以下のマクロを実行すると新規の Excel ファイルが開かれます。
Sub test()
Workbooks.Add
End Sub
Workbooks.Add
は戻り値として開かれた Excel ファイルの Workbook オブジェクトを戻すため、以下のように記述することで新規の Excel ファイルを開くと同時に変数に Workbook オブジェクトを格納することができます。
Sub test()
Dim wb As Workbook
Set wb = Workbooks.Add
End Sub
Excel ファイルを開いた後、開いた Excel ファイルに対する操作を行うことを考えると、上記のように変数に Workbook オブジェクトを格納しておく方が便利でしょう。
Excel ファイルを閉じる
Excel ファイルを閉じる構文は以下です。
以下のマクロは、新規に開いた Excel ファイルを sample.xlsx という名前で保存した後、そのファイルを閉じるマクロです。
Sub test()
Dim wb As Workbook
Set wb = Workbooks.Add
wb.SaveAs ThisWorkbook.Path & "\sample.xlsx"
wb.Close
End Sub
編集後、未保存の Excel ファイルを閉じる場合
編集後、未保存の Excel ファイルを Close で閉じようとすると、以下のような確認メッセージが表示され、マクロの処理が止まってしまいます。
このメッセージを表示せずに Excel ファイルを閉じたい場合(保存しないで閉じる場合)は、以下のように SaveChanges
オプションを指定します。
逆に、保存して閉じたい場合は以下の構文です。
確認メッセージの非表示化・表示化
また別の方法として以下のように Close 処理の前後に、確認メッセージの非表示化・表示化の処理を追加することでも確認メッセージを表示せずに閉じることができます。(この場合保存しないで閉じます。)
Application.DisplayAlerts = False
wb.Close
Application.DisplayAlerts = True