【AIマクロ探究シリーズ#4】複数ファイルからシートを自動収集！ブック間コピーマクロ

AI
2025.04.16

皆さん、こんにちは！下町AI探究者のガジュマロです。
いつもブログにお付き合いくださり、本当にありがとうございます。

さて、「AIマクロ探究シリーズ」、今回は第4弾です！これまで、#1「結合セル解除」、#2「リストからシート作成」、#3「データ整形（スペース・文字幅）」と、一つのExcelブック内で完結する作業の自動化を探求してきました。

今回は少しステップアップして、複数のExcelファイル（ブック）に散らばっているシートを、一つのブックに自動で集めてくるマクロに挑戦してみたいと思います！ファイル操作が絡むので少し複雑になるかもしれませんが、AIに頼めば意外となんとかなるかもしれませんよ！？

ファイルを開いて、シートをコピーして…永遠に続く拷問？

皆さんの周りでは、こんな状況はありませんか？

  • 各支店から送られてくる月次報告Excelファイル（多数）の中にある、「2025年〇〇月」のシートだけを、自分の管理用ブックにまとめたい。
  • 商品カテゴリごとに分かれている在庫管理ファイル（多数）から、「〇〇在庫僅少リスト」のシートだけを抜き出して、一つのファイルで確認したい。
  • 過去の月報ファイル（多数）の中から、特定のグラフが載っている「〇〇分析結果〇〇」シートを集めて、比較検討したい。

手作業でこれをやろうとすると、フォルダとExcelの間を行ったり来たりしながら、

ファイルを開く → シートを探す → シートをコピー → ファイルを閉じる

という作業を、ファイルの数だけ延々と繰り返すことになります。自分はその先のデータ分析等をやりたいのに、それ以前の単純な作業に時間を取られるうえに、コピー漏れや操作ミス、間違ったシートをコピーする等のあるあるミスを防ぐために集中力も使ってしまう…。
もし残業時間帯にその作業をやることになってしまったら、思わず自販機の前で溜息の一つもこぼしたくなるのではないでしょうか。

「AIに”シート集め”を指令！」 ファイルを扱うマクロを作成依頼

「こんな作業こそ、マクロの得意技のはず！」

ということで、今回もAIに具体的なお願いをしてみましょう。フォルダ内のファイルを扱う操作も含めて指示します。

【AIへのお願い（プロンプト）例】

Excel VBA マクロを作成してください。
以下の処理を実行するコードをお願いします。

1. マクロ内で指定したフォルダ内にある、全てのExcelファイル（拡張子が .xlsx または .xlsm）を順番に開きます。
2. 各ファイルの中に「サマリー」という名前のシートが存在するか確認します。※ただし、”サマリー〇〇”など、”サマリー”で”始まるシート名も対象としたいです。
3. もし該当するシートがあれば、そのシート全体を、このマクロを実行しているExcelブックの末尾にコピーします。
4. 各ファイルは、シートをコピーした後（または対象シートがなかった場合）、変更を保存せずにすぐに閉じてください。
5. 処理対象のフォルダパスやファイル名は、マクロのコード内で簡単に指定できるようにしてください。
6. ファイルが見つからない、開けない等の簡単なエラー処理もお願いします。
7. できればシンプルなコードにしてください。

今回のような作業の場合、シートの名前が「〇〇支店」・「✕✕支店」であったり、「サマリー(〇年〇月)」であったりと、定型の名前の前後に識別のための文字が入っている事が多いです。そこで今回は、「サマリー」で始まる名前のシートを対象とするようにお願いをしてみました。

AIが作ってくれたマクロコードはこちら！

Sub CollectSheets_StartsWithPattern_Simple()
    ' 説明: 指定フォルダ内の全Excelファイルから、指定パターンで始まる名前のシートを
    '       このマクロを実行しているブックにコピーします。(Dir関数使用版)

    ' --- ★設定項目 (ここをご自身の状況に合わせて必ず変更してください) ---
    Const TargetFolderPath As String = "C:\Users\YourName\Documents\支店報告\" '  処理したいファイルが入っているフォルダのパス (★★★重要★★★：パスの末尾には \ を付けてください) 
    Const TargetSheetNamePattern As String = "サマリー"     ' ★コピーしたいシート名のパターン (この文字列で始まるシートが対象)
    ' --------------------------------------------------------------------

    ' 変数宣言
    Dim destWB As Workbook   ' コピー先のブック (このマクロがあるブック)
    Dim sourceWB As Workbook ' 開く対象のブック
    Dim ws As Worksheet      ' ループ用シートオブジェクト
    Dim fileName As String   ' ファイル名取得用
    Dim filePath As String   ' フルファイルパス
    Dim fileCount As Long    ' 処理ファイル数
    Dim copiedCount As Long  ' コピーシート数

    ' --- 初期設定 ---
    Set destWB = ThisWorkbook
    fileCount = 0
    copiedCount = 0

    ' --- フォルダ存在チェック (簡易版) ---
    If Dir(TargetFolderPath, vbDirectory) = "" Then
        MsgBox "指定されたフォルダが見つかりません。パスを確認してください。" & vbCrLf & _
               TargetFolderPath, vbCritical, "フォルダ未検出"
        Exit Sub
    End If

    ' --- 処理開始 ---
    Application.ScreenUpdating = False ' 画面描画停止
    Application.DisplayAlerts = False  ' 警告メッセージ非表示
    Application.EnableEvents = False   ' イベント発生停止

    ' --- ファイルループ処理 (Dir関数を使用) ---
    fileName = Dir(TargetFolderPath & "*.xls*") ' フォルダ内の最初のExcelファイルを取得

    Do While fileName <> "" ' ファイルが見つからなくなるまでループ
        filePath = TargetFolderPath & fileName
        fileCount = fileCount + 1
        Set sourceWB = Nothing ' 初期化

        ' ファイルを開く (読み取り専用)
        On Error Resume Next ' ファイルオープンエラー等を無視
        Set sourceWB = Workbooks.Open(filePath, ReadOnly:=True, UpdateLinks:=0)
        If Err.Number <> 0 Then
            Debug.Print "ファイルが開けません: " & filePath & " Error: " & Err.Description ' ログ出力
            GoTo NextIteration ' 開けなければ次のファイルへ
        End If
        On Error GoTo 0 ' エラー処理を戻す

        ' 開いたブック内のシートをループしてパターンに一致するかチェック
        For Each ws In sourceWB.Worksheets
            ' ★シート名が指定パターンで始まるかチェック (Like演算子)
            If LCase(ws.Name) Like LCase(TargetSheetNamePattern) & "*" Then
                ' 一致したらコピー先ブックの末尾にコピー
                ws.Copy After:=destWB.Sheets(destWB.Sheets.Count)
                copiedCount = copiedCount + 1
                ' Exit For ' ★もし各ファイルから最初に見つかった1枚だけで良い場合はコメント解除
            End If
        Next ws

        ' ソースブックを閉じる (保存しない)
        sourceWB.Close SaveChanges:=False
        Set sourceWB = Nothing

NextIteration: ' 次のファイルへ
        fileName = Dir() ' 次のExcelファイルを取得
    Loop

    ' --- 終了処理 ---
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

    ' 完了メッセージ
    MsgBox fileCount & " 個のExcelファイルをチェックし、" & vbCrLf & _
           copiedCount & " 枚の「" & TargetSheetNamePattern & "*」に一致するシートをコピーしました。", vbInformation, "処理完了"

    Set ws = Nothing
    Set destWB = Nothing

End Sub

ファイルを開いたり閉じたりする処理が入ってはいるものの、基本的な構造はこれまでのマクロと似ているようです。Dir関数でファイルを一つずつ見つけ、ループの中でシートを探してコピーしています。

このマクロのポイント（柔軟なフォルダ指定とシート名の対応、ファイルの保全）

今回のマクロは、設定を少し変えるだけで色々な場面に応用できる「汎用性」を持っています。ポイントは以下の2点です。また、ファイルを保全するための保険も入っています。

  1. 柔軟なフォルダパスの指定 (TargetFolderPath):
    処理したいファイルの入ったフォルダのパスは、コード冒頭の Const TargetFolderPath As String = “…” の部分で指定しています。この”…”の中に皆さんが処理したいファイルを入れたフォルダのパスを入力してください。
    • ポイント：フォルダのパスは、エクスプローラーのアドレスバーからコピー＆ペーストするのが簡単で確実です（注意！下記参照）。
    • 要注意：フォルダパスをコピー＆ペーストしたら、末尾に必ず”\”をつけてください。そうしないと、「0このファイルを見つけました」となって、処理が進みません。
  2. 柔軟なシート名の指定 (TargetSheetNamePattern と Like演算子、Lcase関数):
    集めたいシートの名前は、Const TargetSheetNamePattern As String = “サマリー” の部分で指定しています。”サマリー”に集めたいシートの名前（またはその一部）を入力してください。上記のコードでは、「サマリー」や「サマリー〇〇」など、入力したシート名の後ろに何が来ても対応します（前方一致）。
    • 「〇〇サマリー」など、後方一致に対応したい場合
      コードの変更が必要になりますが、後方一致に対応させることもできます。下記のコードの判定部分を、

      ‘ ★シート名が指定パターンで始まるかチェック (Like演算子)
      If LCase(ws.Name) Like LCase(TargetSheetNamePattern) & “*” Then
      を
      ‘ ★シート名が指定パターンで始まるかチェック (Like演算子)
      If LCase(ws.Name) Like “*” & LCase(TargetSheetNamePattern) Then

      のように * を前に付ける形に修正してください。
      もちろん、AIにお願いして、対応してもらうのもありです。
    • 「〇〇サマリー〇〇」など、中間一致に対応したい場合
      これもコードの変更が必要になります。下記のコードの判定部分を、

      ‘ ★シート名が指定パターンで始まるかチェック (Like演算子)
      If LCase(ws.Name) Like LCase(TargetSheetNamePattern) & “*” Then
      を
      ‘ ★シート名が指定パターンで始まるかチェック (Like演算子)
      If LCase(ws.Name) Like “*” & LCase(TargetSheetNamePattern) & “*” Then

      のように * を前後に付ける形に修正してください。
      • Like 演算子とワイルドカード（* ）の組み合わせを少し変えるだけで、様々な条件でシートを探し出すことができます。AIにお願いするのも良いですが、こういうちょっとした工夫が自分でできるようになると、マクロの理解が深まって面白いですよね。
      • Lcase関数は、文字列をすべて小文字に変換します。シート名に大文字と小文字が混在している場合、うっかり名称を間違える可能性を、この関数で防止しています。
  3. ファイルの保全機能 (TargetFolderPath):
    このマクロは、ファイルを読み取り専用で開き(ReadOnly:=True)、処理が終わったら変更を保存せずに閉じる(SaveChanges:=False)という動作をします。これは、元のファイルに意図せず変更を加えてしまうリスクを減らすためです。

このマクロの使い方と注意点（いつもより入念な準備を！）

このマクロは複数のファイルを操作するため、実行前の準備と注意点の確認がいつも以上に大切です。

使う前の準備:

【超重要！】取り扱うファイルは作業用フォルダにコピー: このマクロは複数のファイルを扱います。万が一に備え、元のファイルは直接触らず、作業用のフォルダにファイルをコピーして実施することを強く推奨します。

  1. 作業用フォルダの作成：
    デスクトップなど分かりやすい場所に新しいフォルダを作成します（例: 「マクロ作業用」フォルダ）。
  2. 集約用Excelブックのコピーもしくは作成：
    このマクロを記述・実行する、シート集約用のブックを、作成したフォルダに作成もしくはコピーします。
  3. 集約したいブックを入れたフォルダをコピー
    シートを集めたいファイルが入っているフォルダをフォルダごと、「マクロ作業用」フォルダの中にコピーします。シート集約用のブックのあるフォルダにシートを集めたいファイルを入れてしまうと、エラーが出るので注意です。
  4. 集約用ブックを開く（または新規作成）: 作業用フォルダ内にある、コピーした集約用ブックを開きます。マクロはこのブックから実行します。

マクロの実行手順:

  1. VBEを開きコードを貼り付け: 集約用ブックで Alt + F11 → 「挿入」→「標準モジュール」→ 上記コードを貼り付け。
  2. 【重要】コードの設定項目を修正: 貼り付けたコード冒頭の「★設定項目」(TargetFolderPath と TargetSheetNamePattern) を、ご自身の作業環境（特に、作成した作業用フォルダ内のパス）と目的に合わせて書き換えます。
  3. マクロを実行: コード内にカーソルを置いて F5 キーなどで実行します。
  4. 完了確認: 処理が完了するとメッセージが表示されます。集約用ブックに、指定したパターンに一致するシートがコピーされているか確認しましょう。
    • フォルダ内のファイル数によっては、少し時間がかかる場合があります。

使う上での注意点:

  • 必ず「作業用フォルダ」内のコピーでテスト！: 元のファイルは安全な場所に保管しておきましょう。
  • 処理時間: フォルダ内のファイル数や各ファイルのサイズによっては、マクロの完了まで時間がかかることがあります。Excelが応答しないように見えても、気長に待ってみてください。
  • ファイルが開かない場合: 他の人がファイルを開いていたり、ファイルが破損していたり、セキュリティソフトによってブロックされたりすると、そのファイルはスキップされるかエラーになる可能性があります。
  • シート名のパターン一致: マクロは TargetSheetNamePattern で指定した文字列で「始まる」シート名をコピーします（デフォルトの場合）。意図しないシートまでコピーされないか、逆に必要なシートが漏れていないか、パターン設定と結果をよく確認しましょう。後方一致や中間一致を使いたい場合はコードの修正が必要になる点も留意してください。
  • 環境による制限: 会社のセキュリティポリシーなどによっては、マクロによるファイルシステムへのアクセスが制限されている場合があります。

まとめ：ファイル越えの定型作業も、AIマクロで自動化！

今回は、「AIマクロ探究シリーズ」の第四弾として、複数のExcelファイルから特定のシートを自動で集めてくるマクロに挑戦しました。

フォルダを跨いだ繰り返し作業は、手作業では特に時間と手間がかかり、ミスの温床にもなりがちです。だからと言って、自分でマクロを勉強して組み上げようと思ったら、これは手作業の方が早いな、とも思ってしまうところです。

そんな状況が、AIに適切な指示を与えることで解消するというのは、本当に凄い事ですよね。

「あのフォルダにあるファイルの、あのシート、いっつも集めてるんだよなぁ…」という作業があれば、ぜひ今回のマクロを参考に、AIとの対話を楽しんでみてください。

それでは、また次回の「AIマクロ探究シリーズ」でお会いしましょう！

コメント

タイトルとURLをコピーしました