【Excel VBA入門 第22回】複数のブックを1つに集計しよう！ブック・ファイル操作編のまとめ

イントロダクション

ブック・ファイル操作編も、今回が最終回である。ここまでの2回で、次のツールがそろった。

• 第20回：ブックを開く・保存する・閉じる（Workbooks.Open、Save、SaveAs、Close）
• 第21回：フォルダ内のファイルを調べる（Dir 関数、FileDialog）

今回は、これらを組み合わせて、「フォルダ内のブックを順番に開き、決まったセルの値を、1つの集計シートにまとめる」マクロを作る。月別・支店別に分かれたブックを、手作業で1つずつ開いて転記する作業は、実務でもよくある。それを、ボタン1つで終わらせてみよう。

第12回で学んだ Function、エラー処理編の事前チェックや後始末も、ここで活躍する。

作るマクロの全体像

作るマクロの流れは、次のとおりである。

1. フォルダを選んでもらう（FileDialog）
2. フォルダ内の .xlsx のファイル名を、先に全部取り出す（Dir）
3. 集計用のシートを用意する
4. ブックを1つずつ開き、A1セルとB1セルの値を、集計シートに転記して閉じる
5. 売上の合計を計算する
6. 集計シートを、新しいブックにコピーして保存する
7. 画面の更新などの設定を元に戻す（後始末）

準備

サンプルは、第21回で作った次のものをそのまま使う。

場所は C:\test\月別売上 である。実行前に、これらのブックは閉じておこう。

また、今回のマクロは、これまでに作った Function を使う。
次のものが、同じ標準モジュールに入っていることを確認してほしい。

どれかが欠けていると、実行時に「Sub または Function が定義されていません」というコンパイルエラーになる。

部品：集計シートを用意するFunction

集計結果を書き込むシートを用意する Function を、新しく作る。「集計」という名前のシートがあれば中身を消し、なければ新しく作る、という動きである。第17回で学んだ On Error Resume Next による、シートの存在確認を応用している。

Function 集計シートを用意する() As Worksheet
    Dim ws As Worksheet

    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("集計")
    On Error GoTo 0

    If ws Is Nothing Then
        Set ws = ThisWorkbook.Worksheets.Add
        ws.Name = "集計"
    Else
        ws.Cells.ClearContents
    End If

    Set 集計シートを用意する = ws
End Function

メインのマクロ

部品がそろったので、メインのマクロを作ろう。少し長いが、内容は、これまでに学んだことの組み合わせである。

Sub 月別売上を集計する()
    Const 出力先 As String = "C:\test\月別売上_集計.xlsx"

    Dim フォルダ As String
    Dim 一覧 As Collection
    Dim ファイル名 As Variant
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim 結果 As Workbook
    Dim 行 As Long
    Dim 件数 As Long
    Dim 失敗 As String
    Dim メッセージ As String

    ' --- 事前チェック：フォルダの選択と、ファイルの有無 ---
    フォルダ = フォルダを選ぶ()
    If フォルダ = "" Then Exit Sub
    If Right(フォルダ, 1) <> "\" Then フォルダ = フォルダ & "\"

    Set 一覧 = ファイル一覧を取得する(フォルダ, "*.xlsx")
    If 一覧.Count = 0 Then
        MsgBox "集計するブックが見つかりませんでした。", vbExclamation
        Exit Sub
    End If

    On Error GoTo エラー処理
    Application.ScreenUpdating = False

    ' --- 集計シートの準備 ---
    Set ws = 集計シートを用意する()
    ws.Range("A1:C1").Value = Array("ファイル名", "月", "売上")
    行 = 2

    ' --- ブックを1つずつ開いて、値を転記する ---
    For Each ファイル名 In 一覧
        Set wb = ブックを安全に開く(フォルダ & ファイル名)
        If wb Is Nothing Then
            失敗 = 失敗 & vbCrLf & ファイル名
        Else
            ws.Cells(行, 1).Value = ファイル名
            ws.Cells(行, 2).Value = wb.Worksheets(1).Range("A1").Value
            ws.Cells(行, 3).Value = wb.Worksheets(1).Range("B1").Value
            行 = 行 + 1

            wb.Close SaveChanges:=False
            Set wb = Nothing
        End If
    Next ファイル名

    件数 = 行 - 2
    If 件数 = 0 Then
        MsgBox "集計できたブックがありませんでした。" & 失敗, vbExclamation
        GoTo 後始末
    End If

    ' --- 合計を求める ---
    ws.Cells(行, 1).Value = "合計"
    ws.Cells(行, 3).Formula = "=SUM(C2:C" & (行 - 1) & ")"

    ' --- 集計シートを新しいブックにコピーして、保存する ---
    ws.Copy
    Set 結果 = ActiveWorkbook
    Application.DisplayAlerts = False
    結果.SaveAs Filename:=出力先
    結果.Close SaveChanges:=False
    Set 結果 = Nothing

    ' --- 完了メッセージ ---
    メッセージ = 件数 & " 件のブックを集計しました。" & vbCrLf & "保存先：" & 出力先
    If 失敗 <> "" Then
        メッセージ = メッセージ & vbCrLf & vbCrLf & "開けなかったブック：" & 失敗
    End If
    MsgBox メッセージ

後始末:
    On Error Resume Next
    If Not wb Is Nothing Then wb.Close SaveChanges:=False
    If Not 結果 Is Nothing Then 結果.Close SaveChanges:=False
    On Error GoTo 0

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Exit Sub

エラー処理:
    MsgBox "処理中にエラーが発生しました。" & vbCrLf & _
           Err.Number & "：" & Err.Description, vbCritical
    Resume 後始末
End Sub

コードの解説

先にファイル名を全部取り出す

「ファイル一覧を取得する」 で、先にファイル名を Collection に集めている。第21回で学んだとおり、Dir を使った繰り返しの途中で、別の Dir(パス) を使うと、一覧が壊れてしまう。今回は先に一覧を作ってあるので、繰り返しの中で Dir を使う「 ブックを安全に開く」 を呼んでも、問題は起きない。

開けないブックがあっても止まらない

ブックを安全に開く は、開けなかったときに Nothing を返す。その場合は、失敗 という文字列にファイル名を書き留めておき、そのブックは飛ばして次へ進む。最後に、開けなかったブックの一覧をメッセージで知らせる。1つのブックが開けないだけで、集計全体が止まってしまうことを防げる。

ブックは読んだらすぐ閉じる

ブックを開いたら、必要なセルの値を読み取って、すぐに閉じる。開いたままにしておくと、数が多いときにExcelが重くなる。閉じた後は、Set wb = Nothing で、変数も空にしている。これは、後始末で「閉じ忘れたブックがあるか」を判定するためである。

合計は数式で入れる

合計は、値ではなく =SUM(C2:C4) という数式を入れている。Formula プロパティに、数式を文字列で渡せば、セルに数式を入力できる。行数が変わっても、"=SUM(C2:C" & (行 - 1) & ")" のように、文字列をつなげて作れる。

別のブックとして保存する

ws.Copy は、引数を省略すると、シートを新しいブックにコピーする。コピー先の新しいブックは、アクティブになるので、ActiveWorkbook で受け取って変数 結果 に入れている（第20回で学んだとおり、すぐ変数に入れておくのが、取り違えを防ぐコツである）。

保存先は、集計元のフォルダとは別の場所にしている。もし同じフォルダに保存すると、次に集計を実行したとき、月別売上_集計.xlsx まで集計の対象に混ざってしまうからである。

DisplayAlertsと後始末

第20回で、「同じ名前のファイルがあると、SaveAs の際に上書きの確認メッセージが出る」と紹介した。この確認メッセージが出ると、マクロが途中で止まってしまう。そこで、Application.DisplayAlerts = False で、メッセージを表示しない設定にしている。ただし、この設定では、確認なしで上書きされるので、保存先の名前には注意しよう。

大切なのは、この設定を、必ず元に戻すことである。第18回で学んだとおり、後始末: のラベルを、正常終了のときもエラーが起きたときも通る場所に置き、そこで DisplayAlerts と ScreenUpdating を True に戻している。ブックが開いたままになっていれば、ここで閉じる。

実行してみよう

「月別売上を集計する 」を実行して、フォルダ選択のダイアログで C:\test\月別売上 を選んでみよう。

マクロ有効ブックの「集計」シートに、次のように表示されれば成功である。

「3 件のブックを集計しました。」というメッセージが出て、C:\test\月別売上_集計.xlsx も保存される。

なお、もう一度実行すると、確認メッセージは出ずに、上書きされる。

動作確認のコツ：失敗するケースも試してみよう

エラー処理は、「失敗したとき」に動くものなので、わざと失敗させて確かめるのが確実である。たとえば、メモ帳などでテキストファイルを作り、その名前を 「売上_4月.xlsx 」に変えて、C:\test\月別売上 に入れておこう。
Excelのブックとして開けないため、完了メッセージには、「3 件のブックを集計しました。」に続けて、「開けなかったブック：売上_4月.xlsx」と表示される。
開けないファイルがあっても、残りのブックの集計は、最後まで進んでいることが分かる。

確認が終わったら、このファイルは削除しておこう。

まとめ

ブック・ファイル操作編では、3回かけて、フォルダ内のブックを自動で処理するマクロを完成させた。

今回のマクロには、これまでの学びが詰まっている。

• 処理を Function に分けて、部品として使い回す（第12回）
• 開けないブックがあっても止まらないようにする（第17回、第19回）
• 設定を、正常時もエラー時も必ず元に戻す（第18回）
• Dir の一覧は、先に全部取り出してから処理する（第21回）

ここから、読み取るセルを変えたり、複数のシートを集計したりすれば、自分の仕事に合わせたマクロに育てていける。ぜひ、身近な作業で試してみてほしい。

次回からは、新しいテーマに進む予定である。お楽しみに。