みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。
今回はエクセルVBAを使ってバラバラの経費精算書のデータをデータベースに集約するシリーズです。
前回の記事はこちら。
フォルダやファイルの操作をすることができるFileSystemオブジェクトについて紹介しました。
今回はその続き。フォルダ内のすべてのブックについて開いて経費データを収集していきますよ。
ということで、エクセルVBAでFor Each～Next文を使ってフォルダ内のブック全てを開く方法についてお伝えします。
では、行ってみましょう！
前回のおさらい
まず、今回の目標について確認しておきましょう。
以下のような経費精算書があります。
これは、社内のスタッフごとに別ファイルで作成されて、「data」というフォルダに集められているとします。
その「data」フォルダと同じ階層にあるブックに、以下のような「経費データ」というシートがあり、そこに経費データを収集していくというのが目標です。
そして、スタート地点となるプログラムはこちらです。
Sub 経費精算データ取り込み()
With Workbooks.Open(ThisWorkbook.Path & "\data\" & "201807経費精算書_経費_3001.xlsx")
With .Worksheets(1)
Dim month As Date: month = .Range("G4").Value
Dim departmentName As String: departmentName = .Range("G6").Value
Dim staffId As Long: staffId = .Range("G8").Value
Dim staffName As String: staffName = .Range("G7").Value
Dim i As Long: i = 12
Dim di As Long: di = 2
Do While .Cells(i, 1).Value <> ""
wsData.Cells(di, 1).Value = month '1 対象月
wsData.Cells(di, 2).Value = .Cells(i, 1).Value '2 日付
wsData.Cells(di, 3).Value = "" '3 部署No
wsData.Cells(di, 4).Value = departmentName '4 部署
wsData.Cells(di, 5).Value = staffId '5 社員No
wsData.Cells(di, 6).Value = staffName '6 氏名
wsData.Cells(di, 7).Value = .Cells(i, 2).Value '7 科目
wsData.Cells(di, 8).Value = .Cells(i, 5).Value '8 摘要
wsData.Cells(di, 9).Value = .Cells(i, 6).Value '9 金額
wsData.Cells(di, 10).Value = .Cells(i, 7).Value '10 備考
i = i + 1: di = di + 1
Loop
End With
.Close
End With
End Sub
3行目にある通り、フォルダ内の唯一のブックだけに対応しているプログラムになっていますので、これを「data」フォルダ内のすべてのブックについて繰り返し処理をするように修正していく必要があります。
For Each～Next文とは
繰り返しの構文にFor Each～Next文というものがあります。
これがエクセルVBAにおいて、半端なく便利なシロモノなのです。
記述方法としては、以下のように書きます。
’処理
Next オブジェクト変数
と書きます。
コレクションというのは同種のオブジェクトの集合です。
For Each～Next文の意味としては、コレクション内の全てのオブジェクトてについて、一つずつ順番にオブジェクト変数にセットしながらプログラムを繰り返してね、という意味になります。
Worksheetsコレクションの例
わかりやすい例えを使って説明をしますね。
例えば、反復の対象とするオブジェクトをWorksheetオブジェクトとして以下のプログラムを実行してみましょう。
Sub 現在のマクロブックのすべてのワークシート名()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Debug.Print ws.Name
Next ws
End Sub
wsはFor Each～Next文用に用意したWorksheet型のオブジェクト変数です。命名にルールはありませんので、好きなオブジェクト変数名でOKです。
このオブジェクト変数wsにはFor Each～Nextを繰り返すたびにコレクション内にあるオブジェクトが順番にセットされます。
コレクションとしてはThisWorkbook.Worksheetsが指定されています。つまりThisWorkbookに含まれている全てのワークシートが設定されています。
- オブジェクト変数：ws
- コレクション：ThisWorkbook.Worksheets
このFor Each～Nextは、ThisWorkbookに含まれる全てのワークシートについて、一つずつ順番にFor Each～Nextの中を繰り返してね、という意味になります。
例えば「Sheet1」「Sheet2」「Sheet3」の3つのワークシートが存在しているのであれば、このプログラムの実行により、それらのシート名が順番にイミディエイトウィンドウに表示されることになります。
イメージ湧きましたよね？
フォルダ内のすべてのファイルについて繰り返す
今回の目的としては、フォルダ「data」内にあるファイル全てについて処理したいということになります。
Folderオブジェクト内のFilesコレクションを取得する
前回の記事でお伝えした通り、フォルダを取得するにはFileSystemオブジェクトのgetFolederメソッドを使えば良いですね。
そして、そのフォルダ内のファイルのコレクション、つまりFilesコレクションをFilesプロパティで取得することができます。
これをFor Each～Next文のコレクションの位置に指定してあげればよいですよね。
フォルダ内のファイルについてのFor Each～Next文
以上をもとに、以下のようにFor Each～Next文を作ることができます。
Dim f As File
For Each f In fso.GetFolder(ThisWorkbook.Path & "\data").Files
'処理
Next f
オブジェクト変数とコレクションについて整理しますと、以下のようになります。
- オブジェクト変数：f
- コレクション：fso.GetFolder(ThisWorkbook.Path & “\data”).Files
FileSystemオブジェクトとFor Each文でFilesコレクションの全ての要素に処理をすることについては、以下の記事も参考になると思います。
Fileオブジェクトのフルパスを取得して開く
それで、それぞれのファイルについて、Workbookオブジェクトとして開く必要がありますね。
どうするかというと、繰り返しの対象となっているFileオブジェクトfについて、そのフルパスがわかれば、Openできますよね。
FileオブジェクトはPathプロパティで、そのフルパスを取得できます。
フォルダ内のブックを開いてデータを収集するプログラム
これを元に、フォルダ「data」内のすべての経費精算書を開いてデータを収集するプログラムを作成すると、こちらになります。
Sub 経費精算データ取り込み()
Dim fso As FileSystemObject
Set fso = New FileSystemObject
Dim di As Long: di = 2
Dim f As File
For Each f In fso.GetFolder(ThisWorkbook.Path & "\data").Files
Debug.Print f.Path
With Workbooks.Open(f.Path)
With .Worksheets(1)
Dim month As Date: month = .Range("G4").Value
Dim departmentName As String: departmentName = .Range("G6").Value
Dim staffId As Long: staffId = .Range("G8").Value
Dim staffName As String: staffName = .Range("G7").Value
Dim i As Long: i = 12
Do While .Cells(i, 1).Value <> ""
wsData.Cells(di, 1).Value = month '1 対象月
wsData.Cells(di, 2).Value = .Cells(i, 1).Value '2 日付
wsData.Cells(di, 3).Value = "" '3 部署No
wsData.Cells(di, 4).Value = departmentName '4 部署
wsData.Cells(di, 5).Value = staffId '5 社員No
wsData.Cells(di, 6).Value = staffName '6 氏名
wsData.Cells(di, 7).Value = .Cells(i, 2).Value '7 科目
wsData.Cells(di, 8).Value = .Cells(i, 5).Value '8 摘要
wsData.Cells(di, 9).Value = .Cells(i, 6).Value '9 金額
wsData.Cells(di, 10).Value = .Cells(i, 7).Value '10 備考
i = i + 1: di = di + 1
Loop
End With
.Close
End With
Next f
End Sub
実行すると、以下のように太郎さんだけでなく花子さんのデータも収集することができました。
まとめ
以上、エクセルVBAでフォルダ内の複数のワークブック全てを開いてデータを取り込むプログラムについて解説をしました。
この状態であればひとまずお仕事には使えるレベルにはなったかなと思います。
ちなみに、フォルダ内のファイルを参照するテクニックはお仕事の現場でかなりの活躍を見せてくれます。
経費精算書だけでなく、出勤管理表でも申込書でもフォーマットさえ統一されていれば活用をすることができますので、ぜひ一度作業の検討をしてみてくださいね。
次回は、シートのデータがある最終行数を求めるステートメントを紹介します。
どうぞお楽しみに！
連載目次：エクセルVBAで経費データをデータベースに集約する
請求書シリーズと逆のパターンですが、バラバラの帳票からデータ一覧つまりデータベースに情報を集めて蓄積していく、というお仕事も多いと思います。ここでは各担当者から提出された経費精算書をデータベースに蓄積するプログラムを目標にして進めていきます。- 【エクセルVBA入門】バラバラの経費精算書をデータにまとめる
- 【エクセルVBA入門】Do While～Loop文で条件を満たす間繰り返し
- 【エクセルVBA入門】繰り返しを使ってデータの転記をするときの２つのポイント
- 【エクセルVBA入門】With文でプログラムをスッキリわかりやすく書く
- 【エクセルVBA入門】他のワークブックをWithで開く＆保存せずに閉じる
- 【エクセルVBA入門】フォルダやファイルを操作するFileSystemオブジェクトとその使い方
- 【エクセルVBA入門】For Each～Next文でフォルダ内のブック全てを開く方法
- 【エクセルVBA入門】シートのデータがある最終行番号を求めるステートメントを徹底解説
- 【エクセルVBA入門】開いたブック名から文字列を抽出して人為的なミスを回避する方法
- 【エクセルVBA入門】マクロを作るときに知っておきたいマスタデータのこと
- 【エクセルVBA入門】開いたブックのファイル名から番号を取り出して数値に変換する
- 【エクセルVBA入門】マクロでVlookupを使ってデータを検索する方法
- 【エクセルVBA入門】Vlookupメソッドを使ったときに発生するエラーを回避する方法
- 【エクセルVBA入門】エラーが発生したときに分岐処理を追加する方法
- 【エクセルVBA入門】オートフィルタや行の非表示で隠れている行を全て表示する
