VBAで複数サブフォルダ内に存在する閉じた複数ファイルを集計

本記事では、VBA集計業務では比較的よくある「複数のサブフォルダ内に整理された複数のファイルのデータを、1つのファイルに集計する作業」に応用できるVBAサンプルコードをご紹介します。

集計対象ファイルの数が2, 3個であれば、すべてのファイルを開いてからVBAで集計するやり方もありますが、集計対象のファイルが多くなほど、集計作業前に集計対象のファイルを開いておくのは現実的ではありませんよね。

Tips
集計対象のすべてのファイルを開いてから1つのファイルに集計するVBAサンプルコード

今回は、集計対象となるすべてのファイルを、VBAで1つずつ開いてデータを集計し、集計が終わったらファイルを閉じます。そして、また次の集計対象ファイルを開いてデータを集計することを繰り返し実行するVBAサンプルコードについてご紹介します。

まずは、本記事の概要が2分で確認できる動画（音声無し）を用意しましたので、よろしければ先にご覧ください。

本サイトの概要を2分動画でご紹介

サンプルファイル

本記事でご紹介するサンプルファイルの解説です。全部で10個のファイルがありますが、すべてダウンロードできます。

集計元サンプルファイルを保管するフォルダ構成

今回のVBAサンプルコードは、複数のサブフォルダに格納されている複数のファイルを集計できる仕様ですので、ここではフォルダ構成とファイルの保管場所の前提条件を確認しておきます。

下図がフォルダ構成と各ファイルの保管場所を示したものです。

まず年度別に分けられた親フォルダがあります。今回は、2019年度について集計するとします。サブフォルダは、年度フォルダ配下の月別フォルダで、今回は1～3月までをサンプルとして用意しました。

さらに月別のサブフォルダの配下には、担当者別の営業報告書ファイルがあります。今回は3人の営業担当者としていますので、合計9つの集計元ファイルがあります。

集計元サンプルファイルのシート構成

次に集計元になるサンプルファイルのご紹介とシート構成について解説します。下図は、サンプルとして「1月営業報告書_木村.xlsx」を開いた状態です。

シートは、取引先別に分かれたワークシートが3社分あります。シートの項目は左から順に「取引No.」、「日付」、「商品」、「数量」、「金額」、「納期」、「備考」です。この集計元ファイルが、1～3月分、3名の担当者別で計9ファイルあります。

また今回は、担当者別に取引先を以下の通りに分けています。

担当者「木村」・・・A社、B社、C社
担当者「鈴木」・・・D社、E社、F社
担当者「佐藤」・・・G者、H社、I社

さらに集計元ファイルのデータ数ですが、各担当で1月は31日分、2月は28日分、3月は31日分の合計90日分のレコードがあり、それが9社分ありますので総レコード数は90 × 9 = 810あります。

集計先サンプルファイル

9つの集計元ファイルの集計先になるサンプルファイルのご紹介です（下図）。

シート構成は、「2019年営業報告」シート1枚のみです。このシートに、9つの集計元ファイルのすべてのレコードが集計されます。

シートの項目は、集計元ファイルと同じ「取引No.」、「日付」、「商品」、「数量」、「金額」、「納期」、「備考」の7項目に加え、「取引先」と「担当者」が追加されています。

「取引先」と「担当者」の項目が追加されている理由は、集計元の7つの項目だけでは、集計されたレコードがどの取引先で、担当者が誰なのかを区別することができなくなるからです。

そうなると、集計されたレコードからクロス集計表やグラフを作る際にも、情報が不足して意図したクロス集計表やグラフを作成できなくなります。

この点については、1つの集計先に集約する際に見落としがちなポイントですので注意してくださいね。

ダウンロード用サンプルファイル

これまでにご紹介したサンプルファイルは、以下からダウンロードしてご利用いただけます。

集計元サンプルファイル

ダウンロードファイルは全部で9つあります。ダウンロード後は、既にご紹介したフォルダ構成を参考に、親フォルダ「2019年営業報告」をデスクトップなどに作成した上で、月別のサブフォルダを作成し、それらの中にダウンロードファイルを保存してください。

1月営業報告_佐藤.xlsx ダウンロード

1月営業報告_木村.xlsx ダウンロード

1月営業報告_鈴木.xlsx ダウンロード

2月営業報告_佐藤.xlsx ダウンロード

2月営業報告_木村.xlsx ダウンロード

2月営業報告_鈴木.xlsx ダウンロード

3月営業報告_佐藤.xlsx ダウンロード

3月営業報告_木村.xlsx ダウンロード

3月営業報告_鈴木.xlsx ダウンロード

集計先サンプルファイル

9つの集計元ファイルを1つのファイルに集計するサンプルファイルがダウンロードできます。このファイルに標準モジュールを挿入した後、このあとご紹介するVBAサンプルコードをコピペしてご利用できます。

VBA_Sample_Seet_for_FSO ダウンロード

Tips
標準モジュールの準備の仕方

VBAサンプルコード

ご紹介したフォルダ構成・フォルダ名の状態で保管された9つの集計元ファイルを、集計先ファイルの取りまとめ用シートへ集計するVBAサンプルコードをご紹介します。

Option Explicit
    Dim wbkSelf As Workbook                                                     '集計先ファイル用オブジェクト変数
    Dim wstSelf As Worksheet                                                    '集計先ワークシート用オブジェクト変数
    
    Dim lngWRow As Long                                                         '集計先ワークシートへの書込み行番号
'■メインプログラム
Sub 年間営業報告を集計する()
    Dim objFSO As Object                                                        'FSOオブジェクト変数
    Dim objFldr As Object                                                       'フォルダーオブジェクト変数
    Dim objFl As Object                                                         'ファイルオブジェクト変数
    
    Dim strPath As String                                                       '「2019年 営業報告フォルダ」へのフルパス格納用
    Dim strFlName As String                                                     '集計対象ファイルのフルパス格納用
    
    Application.ScreenUpdating = False
    
    '集計先ファイル、シート変数セット
    Set wbkSelf = ThisWorkbook
    Set wstSelf = wbkSelf.Worksheets("2019年営業報告")
    
    '集計先シート初期化
    With wstSelf
        .Rows("2:" & .Rows.Count).ClearContents
        lngWRow = 2
    End With
    
    '※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※
    '親フォルダパスセット
    strPath = "C:\ ・・・・\2019年 営業報告"
    ''※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※
    
    'FSO設定
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    '各月営業報告のフォルダを順次処理する
    With objFSO.GetFolder(strPath)                                              '「2019年 営業報告フォルダ」フォルダ
        For Each objFldr In .subfolders                                         '「2019年 営業報告フォルダ」内のすべてのサブフォルダを繰り返し処理
            For Each objFl In objFldr.Files                                     'サブフォルダ内のすべてのファイルを繰り返し処理
                strFlName = strPath & "\" & objFldr.Name & "\" & objFl.Name     'ファイル名取得（フルパス）
                
                Call データ集計(strFlName)
                
            Next
        Next
    End With
End Sub

'■集計先フォルダと集計元フォルダのデータ転送
Private Sub データ集計(ByVal vFlName As String)
    Dim wbkData As Workbook             '集計元ファイル用オブジェクト変数
    Dim wstData As Worksheet            '集計元シート用オブジェクト変数
    
    Dim lngERow As Long                 '集計元シートの最終レコード行番号
    
    Dim r As Integer
    Dim c As Integer
    
    Set wbkData = Workbooks.Open(vFlName, ReadOnly:=True)           '集計元のデータファイルを開いて、オブジェクト変数にセット
    
    For Each wstData In wbkData.Worksheets                          '集計元データファイルのすべてのシートについて繰り返し処理
        With wstData
            lngERow = .Range("A" & .Rows.Count).End(xlUp).Row       '各シートの最終行番号取得
            
            'すべてのレコードに対する繰り返し処理
            For r = 2 To lngERow
                For c = 1 To 7                                      '「取引No.」～「備考」までのデータを集計
                    wstSelf.Cells(lngWRow, c) = .Cells(r, c)
                Next
                
                wstSelf.Cells(lngWRow, 8) = wstData.Name            '取引先社名転記
                wstSelf.Cells(lngWRow, 9) = 担当者名(vFlName)       '担当者名転記
                
                lngWRow = lngWRow + 1
            Next
        End With
    Next
    
    wbkData.Close
End Sub

'■ファイル名から担当者名を抜き出す関数
Private Function 担当者名(ByVal vFlName As String) As String
    Dim objFSO As Object                                            'FSO用オブジェクト変数
    
    Dim strBNam As String                                           'ファイル名

    Dim intPos As Integer                                           '「_」の位置
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    strBNam = objFSO.GetBaseName(vFlName)                           'フルパスからファイル名を取得
    
    intPos = InStr(strBNam, "_")                                    '「_」の位置を取得
    
    担当者名 = Mid(strBNam, intPos + 1)                             '担当者名を抜き出す
End Function

Option Explicit

Dim wbkSelf As Workbook '集計先ファイル用オブジェクト変数

Dim wstSelf As Worksheet '集計先ワークシート用オブジェクト変数

Dim lngWRow As Long '集計先ワークシートへの書込み行番号

'■メインプログラム

Sub 年間営業報告を集計する()

Dim objFSO As Object 'FSOオブジェクト変数

Dim objFldr As Object 'フォルダーオブジェクト変数

Dim objFl As Object 'ファイルオブジェクト変数

Dim strPath As String '「2019年営業報告フォルダ」へのフルパス格納用

Dim strFlName As String '集計対象ファイルのフルパス格納用

Application.ScreenUpdating = False

'集計先ファイル、シート変数セット

Set wbkSelf = ThisWorkbook

Set wstSelf = wbkSelf.Worksheets("2019年営業報告")

'集計先シート初期化

With wstSelf

.Rows("2:" & .Rows.Count).ClearContents

lngWRow = 2

End With

'※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※

'親フォルダパスセット

strPath = "C:\ ・・・・\2019年営業報告"

''※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※

'FSO設定

Set objFSO = CreateObject("Scripting.FileSystemObject")

'各月営業報告のフォルダを順次処理する

With objFSO.GetFolder(strPath) '「2019年営業報告フォルダ」フォルダ

For Each objFldr In .subfolders '「2019年営業報告フォルダ」内のすべてのサブフォルダを繰り返し処理

For Each objFl In objFldr.Files 'サブフォルダ内のすべてのファイルを繰り返し処理

strFlName = strPath & "\" & objFldr.Name & "\" & objFl.Name 'ファイル名取得（フルパス）

Call データ集計(strFlName)

End With

End Sub

'■集計先フォルダと集計元フォルダのデータ転送

Private Sub データ集計(ByVal vFlName As String)

Dim wbkData As Workbook '集計元ファイル用オブジェクト変数

Dim wstData As Worksheet '集計元シート用オブジェクト変数

Dim lngERow As Long '集計元シートの最終レコード行番号

Dim r As Integer

Dim c As Integer

Set wbkData = Workbooks.Open(vFlName, ReadOnly:=True) '集計元のデータファイルを開いて、オブジェクト変数にセット

For Each wstData In wbkData.Worksheets '集計元データファイルのすべてのシートについて繰り返し処理

With wstData

lngERow = .Range("A" & .Rows.Count).End(xlUp).Row '各シートの最終行番号取得

'すべてのレコードに対する繰り返し処理

For r = 2 To lngERow

For c = 1 To 7 '「取引No.」～「備考」までのデータを集計

wstSelf.Cells(lngWRow, c) = .Cells(r, c)

wstSelf.Cells(lngWRow, 8) = wstData.Name '取引先社名転記

wstSelf.Cells(lngWRow, 9) = 担当者名(vFlName) '担当者名転記

lngWRow = lngWRow + 1

End With

wbkData.Close

End Sub

'■ファイル名から担当者名を抜き出す関数

Private Function 担当者名(ByVal vFlName As String) As String

Dim objFSO As Object 'FSO用オブジェクト変数

Dim strBNam As String 'ファイル名

Dim intPos As Integer '「_」の位置

Set objFSO = CreateObject("Scripting.FileSystemObject")

strBNam = objFSO.GetBaseName(vFlName) 'フルパスからファイル名を取得

intPos = InStr(strBNam, "_") '「_」の位置を取得

担当者名 = Mid(strBNam, intPos + 1) '担当者名を抜き出す

End Function

VBAサンプルコード実行前準備と実行結果

ここでVBAサンプルコードの実行前の準備と実行結果についてご紹介します。

VBAサンプルコードの実行前準備

今回は実行する前に、VBAコード上に記載の「2019年営業報告」のパスを書き換える必要があります。別の言い方をしますと、パスを書き換えないで実行すると、今回のサンプルコードはエラーになります。

具体的には、VBAコード29行目で「2019年営業報告」フォルダのパスを指定していますのでここを書き換えてから実行してください。

上図は参考までに、VBAコード上のパスを書き換える位置や、パス情報をコピペして書き換える方法について記載したものです。

VBAサンプルコードの実行結果

VBAサンプルコードの実行結果を確認します。今回は、9つのファイルの情報をすべて集計しているはずですので、下図のように月別、担当者別、取引先別のデータが集計されていることをピボットテーブルで確認できます。

図のピボットの例ですと、きちんとすべての担当者と担当者別の取引先が集計されていることが確認出来ます。また日付についても1月から3月まで集計されていることも確認できます。

複数フォルダ内の複数ファイルを1つのファイルに集計するアルゴリズム

複数フォルダ内の複数ファイルを1つのファイルに集計するVBAサンプルコードのアルゴリズムについて詳しく解説していきます。

今回はメインプロシージャの他にサブプロシージャとファンクションプロシージャが各1つずつあります。VBAコードに記載のコメント文も併せて参照して頂きながら読み進めてください。

Tips
サブプロシージャとファンクションプロシージャの基本

フォルダとファイル操作をするメインプロシージャ

8行目：Dim objFSO As Object
今回は、複数のフォルダと複数のファイルを取り扱うVBAコードになりますので、File System Object(以下FSO)というフォルダやファイルを扱う際に大変便利なWindows機能を使います。

そこで、このVBAコード8行目では、FSOのメソッドやプロパティを使うために、FSOの機能をオブジェクト変数として宣言しています。

9行目：Dim objFldr As Object
10行目：Dim objFl As Object
VBAコード9行目は、フォルダをオブジェクトとして扱うためのオブジェクト変数です。また、VBAコード10行目は、ファイルをオブジェクトとして扱うための変数です。

12行目：Dim strPath As String
「VBAサンプルコードの実行前準備」のところで解説した「2019年営業報告」が格納されているフォルダパスを保持する変数宣言になります。

13行目：Dim strFlName As String
こちらもパス情報を格納する変数宣言ですが、こちらのファイルパス情報はVBAコードにより取得されます。

18行目：Set wbkSelf = ThisWorkbook
集計先ファイルをワークブックオブジェクト変数「wbkSelf」にセットしています。

19行目：Set wstSelf = wbkSelf.Worksheets("2019年営業報告")
集計先ファイルの集計先シート「2019年営業報告」を、ワークシートオブジェクト変数「wstSelf」にセットしています。

Tips
ワークブックオブジェクト変数とワークシートオブジェクト変数の基本

VBAコード22～25行目では、下図の通り集計先範囲のクリア（赤）と、レコードを集計先へ書き込む行番号を保持する変数「lngWRow」を初期値（=2）にリセットしています。

33行目：Set objFSO = CreateObject("Scripting.FileSystemObject")
FSOは、VBAコードで扱うファイルやフォルダに関する様々なメソッドやプロパティが使えるWindowsの機能です。具体的な使用方法は、のちほどサンプルコードの解説で詳しくお話ししますが、ここでは、そのFSOの便利な機能を使うために、オブジェクト変数「objFSO」を割り当てています。

VBAコード36～45行目では、「2019年営業報告」配下のサブフォルダ「1月営業報告」、「2月営業報告」、「3月営業報告」のそれぞれに保管されている9つの集計元ファイルを1つずつ繰り返し処理しています。

まず詳しいVBAコードの解説の前に、大まかな処理の流れをつかみやすい用に下図を用意しました。

VBAコード36行目(赤）の「objFSO.GetFolder(strPath)」は、親フォルダ「2019年営業報告」になります。続くVBA37行目（青）のFor Each文の中で、親フォルダ内のすべてのサブフォルダを繰り返し処理します。

さらに続くVBAコード38行目（緑）のFor Each文では、各サブフォルダ内にあるすべての集計元ファイルを繰り返し処理します。では具体的な処理内容の話にうつる前に、ここまでのVBAコードを詳しく解説します。

36行目：With objFSO.GetFolder(strPath)
「GetFolder」は、FSOのメソッドの1つで、引数に対象のフォルダパスを渡すことで、フォルダオブジェクトを取得することができます。ここではその引数に、親フォルダ「2019年営業報告」のフォルダパス（フルパス）を渡すことにより、「2019年営業報告」フォルダオブジェクトを取得しています。

Tips
Withの使い方の基本

37行目：For Each objFldr In .subfolders
「subfolders」は、フォルダーオブジェクトのプロパティの1つです。ここでは、前で取得した「2019年営業報告」フォルダオブジェクトのプロパティとして指定していますので、その配下にあるすべてのサブフォルダ（フォルダコレクション）が対象になります。

つまり、ここのFor Each文ではすべてのサブフォルダを1つずつ繰り返し処理することになります。

38行目：For Each objFl In objFldr.Files
「objFldr」は、VBAコード37行目では1つのサブフォルダということでした。そして、「Files」はそのフォルダオブジェクトのプロパティの1つで、ファイルの集まり（コレクション）を取得します。

つまり、ここのFor Each文では、1つのサブフォルダ内にある複数のファイルすべてについて、1つずつ繰り返し処理することになります。

以上、解説してきたように2つのFor Each文をネスティング（入れ子）させることにより、複数のサブフォルダに格納された9つの集計元ファイルすべてを1つずつ処理することができます。

Tips
For Each ～ Next文の基本

では、続きまして具体的な処理内容を行っているVBAコードの解説になりますが、2つの処理に分かれますので順に解説していきます。

39行目：strFlName = strPath & "\" & objFldr.Name & "\" & objFl.Name
1つ目の処理は、集計元ファイルのファイル名（フルパス）を変数「strFlName」に格納する処理です。この後の処理では、実際に集計対象となるファイルを1つずつ開いて処理しますのでファイル名が必要となります。

41行目：Call データ集計(strFlName)
実際に集計元ファイルのレコードを、集計先ファイルの「2019年営業報告」シートに転記処理するサブルーチン「データ集計」を呼び出しています。引数は、直前で取得したファイル名（フルパス）です。

9つの集計元ファイルからデータを集計する「データ集計」プロシージャ

ここではメインプロシージャから呼び出されたサブプロシージャ「データ集計」の解説になります。処理の概要は、引数で受け取ったファイル名の集計元ファイルを開いてから、集計元ファイルにある複数の取引先シートのすべてのレコードを、集計先ファイルの「2019年営業報告」シートへ転記します。

49行目：Private Sub データ集計(ByVal vFlName As String)
サブプロシージャ宣言文になります。引数「vFlName」は、処理する集計元ファイルを指定するための「ファイル名」（フルパス）です。

50行目：Dim wbkData As Workbook
51行目：Dim wstData As Worksheet
集計元ファイルをワークブックオブジェクトとして扱うためのワークブックオブジェクト変数「wbkData」と、その集計元ファイルの処理対象ワークシートをワークシートオブジェクトとして扱うためのワークシートオブジェクト変数「wstData」を宣言しています。

Tips
ワークブックオブジェクト変数とワークシートオブジェクト変数の基本

58行目：Set wbkData = Workbooks.Open(vFlName, ReadOnly:=True)
集計元ファイルを開いて、ワークブックオブジェクト変数「wbkData」にセットします。開かれた集計元ファイルは、レコードを読み取るだけですので、ここではReadOnlyオプションにTrueを指定しています。

60行目：For Each wstData In wbkData.Worksheets
集計元ファイルにあるすべてのワークシートを1つずつ繰り返し処理させるVBAコードです。

VBAコード65～74行目は、集計元ファイルの中の1つのシート上にあるすべてのレコードに対する繰り返し処理（For文）です。

その繰り返し処理の内容が、VBAコード66～73行目になります。

まずVBAコード66～68行目では、共通する7つの項目を転記する処理になります。ここでは、集計先と集計元の1列目から7列目までの項目の並び順が同じであることを活かして、VBAコード66～68行目でFor文を使うことにより転記処理させています。

集計元対象シートのデータのうち、1列目から7列目までを集計先シートに転記する処理

集計先シートの項目「取引先」と「担当者」は、下図青網掛部のVBAコード70～71行目で取得しています。

まず「取引先」ですが、これは処理中の集計元シートのシート名を取得しています。具体的には、VBAコード70行目の「wstData.Name」により、処理中のワークシートの名前を取得し、集計先の8列目の項目「取引先」に代入しています。

一方、集計先シート9列目の「担当者」については、VBAコード71行目でファンクションプロシージャ「担当者名」にファイル名を引数として渡し、返ってきた戻り値を代入することで処理しています。

73行目：lngWRow = lngWRow + 1
集計元シートの1レコードの転記処理が終わった後の処理です。次のレコードを転記するために、集計先シートの書込み行を1つ進める処理（インクリメント）をしています。

78行目：wbkData.Close
処理対象の集計元ファイルの処理が終わった後に、ファイルを閉じる処理をしています。

集計元ファイル名から「担当者名」を抜き出す「担当者名」ファンクションプロシージャ

引数として渡された集計元ファイルのファイル名（フルパス）から、担当者名を抜き出すファンクションプロシージャです。処理の概要は、先にファイル名の「_」の位置を取得した後、Mid関数を使い担当者名だけを抽出するものです。

82行目：Private Function 担当者名(ByVal vFlName As String) As String
ファンクションプロシージャ宣言文です。引数は集計元ファイルのファイル名（フルパス）です。戻り値は、担当者名になります。

85行目：Dim strBNam As String
フルパスで指定されたファイル名から、拡張子なしのExcelファイル名を抽出して保持するための変数を宣言しています。

87行目：Dim intPos As Integer
VBAコード85行目で取得した拡張子なしファイル名の「_」の位置情報を保持する変数です。

90行目：strBNam = objFSO.GetBaseName(vFlName)
ここでもまたFSOの便利な機能を使います。「GetBaseName」メソッドは、フルパスのファイル名を引数に渡すと拡張子なしのExcelファイル名を戻します。

92行目：intPos = InStr(strBNam, "_")
VBAコード90行目で取得した拡張子なしファイル名の中にある「_」の位置情報を取得し変数「intPos」に保持します。

94行目：担当者名 = Mid(strBNam, intPos + 1)
得られた「_」の位置情報から、目的の担当者名を抜き出し、このファンクションプロシージャの戻り値として返しています。

Tips
文字列から任意の文字や文字列を抜き出すMid関数の基本的な使い方

まとめ

本記事では、複数のフォルダに格納された複数ファイルをVBAで効率よく集計するVBAサンプルコードをご紹介しました。特にVBAコードの中でフォルダやファイルを取り扱う方法として、Windowsの機能であるFSO(File System Object）を使いましたが、FSOについては本記事でご紹介した以外にも、多くのメソッドやプロパティがあります。

今回ご紹介した手法を使えば、1つのフォルダに格納された大量のファイルをいちいち手で開いてデータ転送することなく、VBAでファイルの開閉、データ転送を自動でさせることができますので、業務効率改善に大きな効果が期待できます。