「数百のファイルを1つ1つ開くの面倒……」、複数のExcelデータを1箇所に集約するマクロ術：働く価値を上げる“VBA／GAS”術（7）（3/3 ページ）

経費精算書のように「バラバラのデータを1箇所に集約すること」は、多く発生する業務の1つです。しかし、1つ1つのファイルを開きコピー＆ペーストするのは手間が掛かる業務。その業務を、マクロを使って自動化する方法を紹介します。

[高橋宣成，プランノーツ] PC用表示関連情報

LINE

Hatena

前のページへ | 　　　　　　

経費データを収集するプロシージャ

　では、Subプロシージャ「経費データ収集()」について解説します。処理の大まかな流れは以下のようになっています。

FileSystemオブジェクトの生成
各変数の宣言および準備
フォルダ「経費精算書」内の全ファイルで処理を繰り返し行う
　┣対象となっているファイルを開く
　┣データ「対象月」「部署」「氏名」を変数に格納
　┣開いたファイルの全経費データについて繰り返し処理を行う
　┃┗「経費収集」シートに各データを転記する
　┗開いたファイルを閉じる

　次にマクロ作成の上でポイントとなっている点を解説します。

FileSystemオブジェクトを使用するためのライブラリ

　今回のマクロの一番大きいポイントは、フォルダやファイルを操作する「FileSystemオブジェクト」を使用する点です。本来、フォルダやファイルは「Excel外の世界」。FileSystemオブジェクトは、それを操作できる強力な機能です。

　ただし、デフォルトの状態ではFileSystemオブジェクトを使用できません。まず、使用する準備として、「ライブラリ」を追加します。VBEのメニュー「ツール」から「参照設定」を選択します。

図4　ツールの参照設定を開く

　「参照設定」ダイアログの「参照可能なライブラリファイル」の中から「Microsoft Scripting Runtime」を探し出し、チェックを入れて「OK」をクリックします。

図5　参照設定ダイアログ

　これにより、後述する「Newキーワード」によるFileSystemオブジェクトの生成や最適な型でのオブジェクト変数の宣言、関連するメンバー候補表示などができるようになります。

FileSystemオブジェクトの生成

　FileSystemオブジェクトを使うには、生成したオブジェクトを対象に、メソッドやプロパティを使用します。そのため、オブジェクトを生成して、変数にセットし、取り扱えるようにする必要があります。

　FileSystemオブジェクトを生成して変数にセットするには、「Newキーワード」を使用して、以下のようにします。

Set 変数 = New FileSystemObject

　変数は、Microsoft Scripting Runtimeを追加していれば、「FileSystemObject型」での変数宣言が可能なので、その変数を使用します。

For Each～Next文によるループ

　続いて、以下のループ箇所を見ていきます。

Dim f As File
For Each f In fso.GetFolder(pass).Files
  '処理
Next f

　このループ構文は「For Each～Next文」で、コレクション内の全ての要素をループします。

For Each 変数 In コレクション
  '処理
Next 変数

　コレクションから順不同で1つずつ要素を取り出して変数に格納し、ループ内の処理を行います。全ての要素について処理したら、ループを終了して抜けます。今回の例では、「フォルダ内のFileコレクションから、Fileオブジェクトを1つずつ取り出して変数fに格納しながら処理する」ループになっています。

フォルダの取得とFileコレクションの取得

　For Each～Next文のコレクションとして指定されている、以下の部分を解説します。

fso.GetFolder(pass).Files

　まず、特定のフォルダを取得するには、「GetFolderメソッド」を使用します。以下の書式で、指定したフォルダパスのフォルダを「Folderオブジェクト」として取得できます。

FileSystemオブジェクト.GetFolder(フォルダパス)

　続いて、特定のフォルダ内の「Filesコレクション」（つまり「Fileオブジェクト」の集合）を取得するには、「Filesプロパティ」を使います。

Folderオブジェクト.Files

　このコレクションをFor Each～Next文を使ってループすることで、特定フォルダ内の全ファイルを処理できます。

WithステートメントとOpenメソッドの組み合わせ

　全てのブックファイルを開いて、そのシート上の値を読み取る必要があります。その際に、WithステートメントとOpenメソッドを組み合わせると、スッキリしたコードを書けます。書き方は以下の通りです。

With Workbook.Open(ファイル)
  '処理
End With

　このように記述することで、Withブロック内で開いたWorkbookオブジェクトの表記を省略できます。なお、Openメソッドは、ファイルパスを文字列で指定することが多々ありますが、Fileオブジェクト自体を指定しても動作するので、覚えておくと良いでしょう。

プロシージャ全体を確認

　解説は以上となります。再度プロシージャ「経費データ収集()」の内容を確認してみましょう。

Sub 経費データ収集()
Dim fso As FileSystemObject
Set fso = New FileSystemObject
Dim pass As String
pass = ThisWorkbook.Path & "\経費精算書"
Dim month As Date, department As String, fullname As String '対象月,部署,氏名
Dim i As Long, j As Long '経費精算書の行数,経費集計の行数
j = 2
Dim f As File
For Each f In fso.GetFolder(pass).Files
　　With Workbooks.Open(f)
　　　　With .Worksheets(1)
　　　　　　month = .Range("G1").Value　　  '対象月
　　　　　　department = .Range("G3").Value '部署
　　　　　　fullname = .Range("G4").Value   '氏名
　　　　　　
　　　　　　i = 8
　　　　　　Do While .Cells(i, 1).Value <> ""
　　　　　　　　Sheet1.Cells(j, 1).Value = month　　　　　　　　'対象月
　　　　　　　　Sheet1.Cells(j, 2).Value = .Cells(i, 1).Value   '日付
　　　　　　　　Sheet1.Cells(j, 3).Value = department　　　　   '部署
　　　　　　　　Sheet1.Cells(j, 4).Value = fullname　　　　　　 '氏名
　　　　　　　　Sheet1.Cells(j, 5).Value = .Cells(i, 2).Value   '科目
　　　　　　　　Sheet1.Cells(j, 6).Value = .Cells(i, 5).Value   '摘要
　　　　　　　　Sheet1.Cells(j, 7).Value = .Cells(i, 6).Value   '金額
　　　　　　　　Sheet1.Cells(j, 8).Value = .Cells(i, 7).Value   '備考
　　　　　　　　i = i + 1
　　　　　　　　j = j + 1
　　　　　　Loop
　　　　End With
　　　　.Close
　　End With
Next f
End Sub

次回はGoogle Apps Scriptでデータを集めるスクリプトを紹介

　今回は、Excel VBAでバラバラのExcelファイルからデータを1つにまとめるマクロを紹介しました。FileSystemオブジェクトは、ファイルやフォルダなどの操作を簡単に行える強力な機能です。コレクションの要素についてループを行うFor Each～Next文と併せて、ぜひ使いこなせるようにしましょう。

　次回はGASでバラバラのスプレッドシートからデータを収集するツールを作成します。どうぞお楽しみに。

著者プロフィール

高橋宣成

プランノーツ代表取締役

「ITを活用して日本の『働く』の価値を高める」をテーマに、ExcelやVBA、G Suite、Google Apps Script、クラウドなどによる企業または個人事業主向けのシステムおよびツールの開発やコンサルティング、セミナー講師などを務める。「IT×働き方」をテーマに運営するブログ「いつも隣にITのお仕事」は月間60万PV達成。