フォルダ内に複数あるファイルについて、順番に処理していきたいことありますよね。
例えば、店舗ごとに作成された売上報告書から、売上等のデータをコピーして、一つのファイルにまとめ直す作業。
一個ファイルを開いて、コピペしてー。閉じてー、また次のファイルーなんてやってたら日がくれてしまうこと間違いないので、フォルダにあるファイルを順次に処理していく方法、是非覚えていってください!
それでは、最後までごらんくださいませ!
こんにちは。当ブログの管理人の『くろん』です。
30代サラリーマン、新規事業推進室に所属。ブラック企業勤め時代に身に着けた業務効率化ノウハウをアウトプットしていきます。
読んでくれた方の業務が一秒でも早く終わりますように!と願いながら記事書いていきます!
VBAでフォルダ内のファイルを順番に処理する方法2つ
フォルダ内のファイルを順番を処理する方法には、メインどころで2つの方法があります。
一つは、FileSystemObject (FSO) メソッドを使用する方法。
もうひとつは、Dir関数を使用する方法です。
それぞれ、実際のスクリプトとともに紹介してきます!
説明に際しては、下記のような要件を満たすVBAを作っていきたいと思います。
- フォルダ名『data』内に存在する、売上報告書10ファイル(.xlsxのファイル)を処理。
- 当該マクロは、フォルダ『data』が存在する同じ階層にセット
- 売上報告書はまったく同じフォーマットを使用
- 売上報告書から取得したデータは、まとめのエクセルファイルの2行目から順番に記載し(1行目は見出し)、最後に名前をつけて保存
フォーマットはこんな感じ!
- B3セルに日付
- B4セルに店舗番号
- B5セルに店舗名
- B7セルには、『=SUM(B8:B10)』の数式
- B8セルにカテゴリAの売上
- B9セルにカテゴリBの売上
- B10セルにカテゴリCの売上
を店舗の方にそれぞれ入力していってもらって、そのエクセルファイルを提出してもらうという流れです。
説明のために簡略化しちゃいましたけど、別段に項目数はもっと多くても問題ありません。それより大事なのは、こういうフォーマットでデータを収集するときに、『列を増やさない減らさない、行を増やさない減らさない』と、再三再四、口がすっぱくなるくらい従業員の方に言うってことです。
フォーマット崩されてしまうと、その後のデータの一括抽出ができなくなってしまうので。。。
シートを保護して、特定のセルのみしか入力できなくするとかも、テクニックの一つです!
FileSystemObject (FSO) メソッドって、そもそも何?
FileSystemObject (FSO) メソッドを使用する方法では、ファイルやフォルダをオブジェクトとして扱い、柔軟に操作することができます。
具体的には、最初にFileSystemObjectを作成し、GetFolderメソッドを使用して処理対象のフォルダを取得。このフォルダからFilesコレクションを取得し、For Eachループを使って各ファイルに対する操作を行うという流れです。
例えば、特定のフォルダ内のファイル一覧をExcelシートに出力する場合、GetFolderメソッドでフォルダオブジェクトを取得し、そのFilesコレクションをループ処理します。各ファイルの名前、最終更新日時、パスなどの情報をExcelシートに書き出すことができます。さらに、条件に基づいてファイルを移動したり、名前を変更するなどの処理も行うことができます。
FileSystemObject (FSO) メソッドの使用してフォルダ内のファイルを順次処理
Sub SalesReports_Summarize()
Dim fso As Object
Dim folder As Object
Dim file As Object
Dim wb As Workbook, summaryWb As Workbook
Dim ws As Worksheet, summaryWs As Worksheet
Dim row As Long
Dim folderPath As String
Dim summaryFilePath As String
' プログラム最速化処理
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
' FileSystemObjectの初期化
Set fso = CreateObject("Scripting.FileSystemObject")
' まとめファイルとそのワークシートの初期化
Set summaryWb = Workbooks.Add
Set summaryWs = summaryWb.Sheets(1)
With summaryWs
.Cells(1, 1).Value = "日付"
.Cells(1, 2).Value = "店舗番号"
.Cells(1, 3).Value = "店舗名"
.Cells(1, 4).Value = "売上合計"
.Cells(1, 5).Value = "カテゴリAの売上"
.Cells(1, 6).Value = "カテゴリBの売上"
.Cells(1, 7).Value = "カテゴリCの売上"
End With
row = 2
' "data"フォルダのパス
folderPath = ThisWorkbook.Path & "\data"
' フォルダ内の各ファイルを処理
Set folder = fso.GetFolder(folderPath)
For Each file In folder.Files
If Right(file.Name, 5) = ".xlsx" Then
Set wb = Workbooks.Open(file.Path)
Set ws = wb.Sheets(1)
' データの読み取りとまとめファイルへの記録
With summaryWs
.Cells(row, 1).Value = ws.Range("B3").Value
.Cells(row, 2).Value = ws.Range("B4").Value
.Cells(row, 3).Value = ws.Range("B5").Value
.Cells(row, 4).Value = ws.Range("B7").Value
.Cells(row, 5).Value = ws.Range("B8").Value
.Cells(row, 6).Value = ws.Range("B9").Value
.Cells(row, 7).Value = ws.Range("B10").Value
End With
row = row + 1
wb.Close False
End If
Next file
' まとめファイルの保存
summaryFilePath = ThisWorkbook.Path & "\売上報告まとめ.xlsx"
summaryWb.SaveAs summaryFilePath
' オブジェクトの解放
Set ws = Nothing
Set wb = Nothing
Set summaryWs = Nothing
Set summaryWb = Nothing
Set folder = Nothing
Set fso = Nothing
' プログラム最速処理を元に戻す
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
MsgBox "売上報告のまとめが完了しました。", vbInformation
End Sub
このプログラムを実行すると、こんな感じで売上報告のデータを収集したまとめエクセルファイルが完成します。
これなら集計も簡単にできますし、毎日の全店売上報告書も、自動で作れそうですね!
まとめる情報を変更したいときは、『まとめファイルとそのワークシートの初期化』『データの読み取りとまとめファイルへの記録』このあたりを適宜変更してくれればOKです。
このプログラムは、FileSystemObject (FSO) メソッドなのですが、ファイルをいじるときの別の関数『Dir関数』についてもプログラム書いていきますね!
Dir関数ってそもそも何?
VBA(Visual Basic for Applications)におけるDir関数は、ファイルやフォルダの名前を取得するために使用される関数です。この関数は、指定されたパターンに一致するファイルやフォルダ名を文字列として返します。例えば、特定のディレクトリ内のすべてのExcelファイル(*.xlsx)をリストアップする場合や、特定の名前パターンに一致するファイルを検索する場合に役立ちます。
Dir関数の基本的な使用方法は、Dir(パス名 [, 属性])という形式で、パス名には検索したいファイルやフォルダのパスを、オプションで属性を指定して使用します。属性には、ファイルの種類(例:隠しファイルやシステムファイルなど)を指定することができます。最初にDir関数を呼び出した後、引数なしでDirを再度呼び出すことで、同じ検索パターンに一致する次のファイル名を取得できます。これにより、ループ処理を使用してディレクトリ内のすべてのファイルを順に処理することが可能になります。
Dir関数は、FileSystemObject(FSO)を使用するよりもコードが短く、シンプルに書ける点でメリットがあるのですが、Dir関数は一度に一つの検索パターンしか処理できないため、複数の検索を同時に行いたい場合はFSOの方が適している場合があります。
Dir関数の活用して、フォルダ内のファイルを順次処理
Sub SalesReports_SummarizeUsingDir()
Dim fileName As String
Dim wb As Workbook, summaryWb As Workbook
Dim ws As Worksheet, summaryWs As Worksheet
Dim row As Long
Dim folderPath As String
Dim summaryFilePath As String
' プログラム最速化処理
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
' まとめファイルとそのワークシートの初期化
Set summaryWb = Workbooks.Add
Set summaryWs = summaryWb.Sheets(1)
With summaryWs
.Cells(1, 1).Value = "日付"
.Cells(1, 2).Value = "店舗番号"
.Cells(1, 3).Value = "店舗名"
.Cells(1, 4).Value = "売上合計"
.Cells(1, 5).Value = "カテゴリAの売上"
.Cells(1, 6).Value = "カテゴリBの売上"
.Cells(1, 7).Value = "カテゴリCの売上"
End With
row = 2
' "data"フォルダのパス
folderPath = ThisWorkbook.Path & "\data\"
' Dir関数を使用してフォルダ内の.xlsxファイルを列挙
fileName = Dir(folderPath & "*.xlsx")
Do While fileName <> ""
Set wb = Workbooks.Open(folderPath & fileName)
Set ws = wb.Sheets(1)
' データの読み取りとまとめファイルへの記録
With summaryWs
.Cells(row, 1).Value = ws.Range("B3").Value
.Cells(row, 2).Value = ws.Range("B4").Value
.Cells(row, 3).Value = ws.Range("B5").Value
.Cells(row, 4).Value = ws.Range("B7").Value
.Cells(row, 5).Value = ws.Range("B8").Value
.Cells(row, 6).Value = ws.Range("B9").Value
.Cells(row, 7).Value = ws.Range("B10").Value
End With
row = row + 1
wb.Close False
' 次のファイル名を取得
fileName = Dir()
Loop
' まとめファイルの保存
summaryFilePath = ThisWorkbook.Path & "\売上報告まとめ.xlsx"
summaryWb.SaveAs summaryFilePath
' プログラム最速処理を元に戻す
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
MsgBox "売上報告のまとめが完了しました。", vbInformation
End Sub
挙動としては、FileSystemObject(FSO)メソッドのときと同じです。出力されるファイルも同じ内容になります。
ちなみに、どちらのほうが実行時間は短いのか調べてみたところ、
FSOメソッドが7.34秒、Dir関数が6.50秒だったので、Dir関数のほうが若干速めですね。
VBAでフォルダ内のファイルを順番に処理する方法まとめ
この記事に関してまとめると。
- VBAでフォルダ内のファイルを順番に処理する方法2つ
-
- FileSystemObject (FSO) メソッドとDir関数を使用する方法がある。
- FSOメソッドのほうが複雑な処理が可能であるが、Dir関数のほうが実行が高速。
- どういうファイル検索をしたいかによって使い分ける必要がある。
- FileSystemObject (FSO) メソッドについて
-
- ファイルやフォルダをオブジェクトとして扱い、柔軟に操作できる。
- フォルダ内ファイルを順番に処理するのは、FileSystemObjectを作成→GetFolderメソッドを使用して処理対象のフォルダを取得→Filesコレクションを取得→For Eachループで各ファイルを操作という流れ。
- Dir関数について
-
- 指定するパターンに一致するファイルやフォルダ名を文字列として返す。
- 再び引数なしでDir関数を呼び出すと、同じ検索パターンに一致する次のファイル名を取得。
膨大なファイルに対して、同じように処理を加えていくというのは、エクセル使うときにしばしば見られる光景ですよね。
一つひとつファイルを開いて閉じてーをやっていたら、とんでもなく時間がかかってしまうので、この記事で紹介した、FSOメソッドがDir関数を使って、一撃で処理していきたいですね!