【本当に役立つマクロ】フォルダにある複数のエクセルファイルの値を集計するマクロ

Sub 複数ファイルを集計するマクロ()
Dim i As Long
Dim aryDir() As String
Dim strName As String

‘iは0から始める
i = 0
‘ReDim：要素数を定義するステートメント　動的配列に使用する
ReDim aryDir(i)

‘フォルダをここで指定
‘当マクロファイルが存在しているフォルダを指定する
aryDir(i) = ThisWorkbook.Path

‘まずは、指定フォルダ以下の全サブフォルダを取得し、配列aryDirに入れる
‘ここでDo Loopを回して繰り返しを行う

Do
‘指定したフォルダ内のサブフォルダをstrNameに入れる
strName = Dir(aryDir(i) & “\”, vbDirectory)
Do While strName <> “”
‘GetAttrとはフォルダ名の属性を調べる関数
‘ここでは取得したフォルダ名がフォルダ（vbDirectory）であることを確認している
‘フォルダを検索する際に「.」「..」がフォルダ名に含まれるため、除外する

If GetAttr(aryDir(i) & “\” & strName) And vbDirectory Then
If strName <> “.” And strName <> “..” Then
‘ReDimステートメントで要素数を再定義する。Preserveですでに格納されているデータを消さずに配列の要素数を再定義
‘UBound：引数で指定した配列で使用できる最も大きいインデックス番号を返す
‘
ReDim Preserve aryDir(UBound(aryDir) + 1)
aryDir(UBound(aryDir)) = aryDir(i) & “\” & strName
End If
End If
strName = Dir()
Loop
i = i + 1
‘存在するサブフォルダの数よりiの方が大きくなったらLoopを抜けて次の処理に移る
If i > UBound(aryDir) Then
Exit Do
End If
Loop

‘————–サブフォルダとエクセルファイルを探すマクロはここまで—————————————-
‘ここから上のマクロはあまり理解できなくとも大丈夫。
‘マクロファイルがあるフォルダにあるサブフォルダをすべて検索するということだけ理解できれば大丈夫
‘ステップイン（F8）を行いながら確認をするとよりイメージがわきやすい

‘———————————————————————————————————–
‘ここから以下は探し出したエクセルファイルに対してサブフォルダ単位で処理を行っていく

‘のちのち使いやすくするために変数の設定を行う

Dim 親フォルダパス As String
Dim フォルダパス As String
Dim パス確認 As String
Dim フォルダ名称 As String
Dim Pos As Long
Dim フォルダ部門コード As String

‘集計先のシートの1行からスタート
Dim n
n = 1

‘配列aryD1R(i)に対する処理を実行
‘For　Next構文でループを回す
‘LBound:要素数の最低値＋1～Ubound：要素数の最大値までループを回す
For v = LBound(aryDir) + 1 To UBound(aryDir)

‘このあたりが事前に設定してあると後々使いやすい
‘フォルダの名称やファイルの名称について個別に変数で設定をしておく

親フォルダパス = ThisWorkbook.Path
サブフォルダパス = aryDir(v)
‘検索したサブフォルダパスに関して後ろから探したときに”\”が先頭から何番目にあるかを探す
Pos = InStrRev(aryDir(v), “\”)
‘フォルダ名称を取り出すために “\”から何番目の文字からフォルダ名称となっているのかを確認し
‘文字列を取り出す
‘例）\\Dektop\55555555\123取り出したいファイル名→この場合は「取り出したいファイル名」が
‘フォルダ名称として格納される
フォルダ名称 = Mid(aryDir(v), Pos + 4)

‘String[変数] 任意の長さの文字列
Dim buf As String
Dim tmp1 As String
Dim tmp2 As String
Dim tmp3 As String
Dim tmp4 As String

‘集計先のシートを指定し、変数に入れる
Dim w
Set w = Worksheets(“集計先シート名称”)

‘サブフォルダに格納されているエクセルファイルを変数[buf]に入れる
buf = Dir(サブフォルダパス & “\*_*.xls*”)

‘Do While構文
‘フォルダに入っているエクセルファイルが見つからなくなるまで同じ作業を行う
Do While buf <> “”

‘サブフォルダ内に除外したいエクセルファイルがある場合は以下のIf構文で除外することも可能
‘不要な場合は削除してもOK
‘＜＞←〇〇ではないという意味の記号
If buf <> “【除外したいエクセルファイル名】.xls*” Then

‘【重要】今回は数値の集計の方法として、数式でつなげていくという方法をとっている
‘集計元のエクセルファイルの数値が変更された場合に自動で更新できるようにするためだが、別の方法でも可能
‘単純に数値を足すよりかは応用的な集計方法のため紹介をします。

‘変数[tmp]に探し出したエクセルファイルの名称をもとに、算式を代入
‘変数[tmp]の数式の中にさらにtmpが含まれているのは、これから繰り返し探すエクセルファイルをつなげていくため
tmp1 = tmp1 & “+'” & サブフォルダパス & “\[” & buf & “]【集計元シート名】’!H12”
tmp2 = tmp2 & “+'” & サブフォルダパス & “\[” & buf & “]【集計元シート名】’!H16”
tmp3 = tmp3 & “+'” & サブフォルダパス & “\[” & buf & “]【集計元シート名】’!H20”
tmp4 = tmp4 & “+'” & サブフォルダパス & “\[” & buf & “]【集計元シート名】’!H24”

End If
buf = Dir()
‘探すべきエクセルファイルがなくなるまで繰り返すLoop
Loop

‘集計先のシートに先ほど作成した数式（tmp)を代入する
‘数式を代入するセルは変数ｎを使用し、動的に動かせるようにしておくとよい
ThisWorkbook.Worksheets(“集計先シート名称”).Range(w.Cells(n + 1, 2), w.Cells(n + 1, 2)).Value = “=” & Mid(tmp1, 2)
ThisWorkbook.Worksheets(“集計先シート名称”).Range(w.Cells(n + 2, 2), w.Cells(n + 2, 2)).Value = “=” & Mid(tmp2, 2)
ThisWorkbook.Worksheets(“集計先シート名称”).Range(w.Cells(n + 3, 2), w.Cells(n + 3, 2)).Value = “=” & Mid(tmp3, 2)
ThisWorkbook.Worksheets(“集計先シート名称”).Range(w.Cells(n + 4, 2), w.Cells(n + 4, 2)).Value = “=” & Mid(tmp4, 2)

‘数値だけではどのフォルダの値なのかが不明なため、フォルダ名称を入力しておく
Range(w.Cells(n, 1), w.Cells(n, 1)) = フォルダ名称

‘tmpに格納されている数式を初期化する
‘これを行わないと次のループをしたときに前回の数式が残ったままになってしまう
tmp = “”
tmp2 = “”
tmp3 = “”
tmp4 = “”

‘次の行へ
n = n + 6
Next v
End Sub