【Excelマクロ】複数ファイルのブック、シートをマージする

2020年9月15日エクセル

複数ファイルや複数シートに散らばったデータを整理したいとき、まずは全ての情報を一箇所にまとめておくと、後の作業が楽になりますよね。とは言っても、その一箇所に情報を集約するのが結構手間だったりします。

今回は複数のExcelファイルの内容を1シートにマージするマクロを紹介します。

ブック、シートを一括マージ
複数Excelファイルのブック、シートを1シートにマージする

数個のファイルを1回だけまとめればOK、というのであれば手作業でコピペしても良いかもしれません。
でも、ファイル数が多かったり、ファイルが更新されたらもう一回やり直し…。
そういう面倒な作業であれば、即、自動化して効率化しましょう。
(そんな情報はExcelよりもデータベースで管理したいところですが、それは一旦置いておいて)

では早速、内容を紹介します。

ワークシート側

ワークシートの見た目はこんな感じです。

Excelファイルのマージツール ワークシート側
Excelファイルのマージツール ワークシート側

使い方は下記の通りです。

  1. マージ対象のファイルをB4セル以降に列挙する。
  2. F1セルにマージするシート名のフィルタ条件を指定する。
    (Like条件で指定する。「*」なら全てのシートが対象、「Sheet1」ならその名前のシートだけ対象)
  3. F2セルにマージ時の開始行を指定する。
    (1行目はヘッダでマージ不要であれば、「2」を指定すれば各シート2行目以降からマージする、など)
  4. マクロ「データマージ」を実行する。
  5. 「out」シートにマージ結果が入っていく。

ここでは、先日紹介した「フォルダ内のファイル一覧を取得するマクロ」の一覧を使うようなレイアウトにしています。
レイアウトを活用しているだけで、ファイル一覧を取得するマクロは無くても大丈夫です。

マクロ側

※マクロを実行するブックに、あらかじめ「out」というシートを作っておくこと。
※ファイルオープン時のダイアログ(読み取り専用で開くかとか)は抑制していません。必要なら抑制処理を追加してください。

前述のワークシートのレイアウトだとして、マクロは下記のようになります。

Option Explicit

Dim Gマージ結果出力行Index As Long
Dim Gマージ結果出力シート As Worksheet

Sub マージ()
    Dim ファイルリスト行Index As Integer

    Dim 対象ファイルパス As String
    Dim 対象シート名条件 As String
    Dim マージ開始行 As Long
    
    Dim メインシート As Worksheet
    Set メインシート = ActiveSheet
    
    
    
    Set Gマージ結果出力シート = ActiveWorkbook.Sheets("out") '出力先シート「out」が存在する前提
    
    '出力先シートをクリア
    Gマージ結果出力シート.Cells.Clear
    Gマージ結果出力行Index = 1
    Gマージ結果出力シート.Activate
    Gマージ結果出力シート.Rows(Gマージ結果出力行Index).Select
    
    対象シート名条件 = メインシート.Cells(1, 6).Text 'サンプルレイアウトではF1に記載している「シート名」
    マージ開始行 = メインシート.Cells(2, 6) 'サンプルレイアウトではF2に記載している「マージ開始行」
    
    ファイルリスト行Index = 4 'ファイルリストの開始行を指定する。サンプルレイアウトでは4行目から始まる。
    対象ファイルパス = メインシート.Cells(ファイルリスト行Index, 2).Text & "\" & メインシート.Cells(ファイルリスト行Index, 3).Text
    
    Do While 対象ファイルパス <> ""
   
        If ワークブックを開いて結合(対象ファイルパス, 対象シート名条件, マージ開始行) = False Then
            Exit Do
        End If
        
        ファイルリスト行Index = ファイルリスト行Index + 1
        対象ファイルパス = メインシート.Cells(ファイルリスト行Index, 2).Text & "\" & メインシート.Cells(ファイルリスト行Index, 3).Text
    Loop

End Sub

Function ワークブックを開いて結合(ByVal 対象ファイルパス As String, ByVal 対象シート名条件 As String, ByVal マージ開始行 As Long) As Boolean
    ワークブックを開いて結合 = True
    On Error GoTo wError
    
    
    Dim 対象ワークブック As Workbook
    Set 対象ワークブック = Workbooks.Open(対象ファイルパス)
    
    Dim 対象シート As Worksheet
    Set 対象シート = 対象ワークブック.Worksheets(対象シート名条件)
    
    Dim 最終行Index As Long
    
    最終行Index = 対象シート.Range("A1").SpecialCells(xlLastCell).row
    
    対象シート.Activate
    対象シート.Rows(マージ開始行 & ":" & 最終行Index).Select
    Selection.Copy
    
        
    Gマージ結果出力シート.Activate
    ActiveSheet.Paste
    Gマージ結果出力行Index = Gマージ結果出力行Index + (最終行Index - マージ開始行 + 1)
    Gマージ結果出力シート.Rows(Gマージ結果出力行Index).Select
    
    'ブックを閉じる
    Application.DisplayAlerts = False
    対象ワークブック.Close
    Application.DisplayAlerts = True
    
    Exit Function

wError:
    If 対象ワークブック Is Not Null Then
        対象ワークブック.Close
    End If
    MsgBox (対象ファイルパス & "でエラーが発生しました")
    ワークブックを開いて結合 = False
End Function

まとめ

以上で複数のExcelファイルからブック、シートをマージするツールの完成です。後はピボットテーブルやグラフなどを使って整理作業を進めましょう。

今回のコードはひとまず動かすための実装で、色々と改善の余地ありです…

Excel作業を楽にしよう

日頃、面倒だなと思っている作業も、Excelのことを少し知るだけで簡単に片付けられるようになったりします。
どんなことができるのかを知っているだけでも違うので、よくExcelを使うという方は参考書などをざっと見てみることをオススメします!

Kindle Unlimited会員なら無料で読めるExcel関連書籍を紹介しておきます。

エクセルExcel

Posted by moto2g