【エクセルVBA入門】For Each~Nextでフォルダ内のブック全てを参照する

★気に入ったらシェアをお願いします!


CDコレクション

みなさん、こんにちは!
昔はコレクションをよくしてましたタカハシ(@ntakahashi0505)です。

今回はエクセルVBAを使ってバラバラの経費精算書のデータをデータベースに集約するシリーズもいよいよ佳境です。第5回です。

前回の記事はこちら。

【エクセルVBA入門】他のワークブックをWithで開く&保存せずに閉じる
エクセルVBAを使ってバラバラの経費精算書のデータをデータベースに集約するシリーズの第4回。今回はWithを使って他のワークブックを開く方法、またそれを保存せずに閉じる方法についてお伝えしていきます。

特定のフォルダ内にあるワークブックをWithも使いながら開いて、そこからデータを読み取る方法でした。

この特定のフォルダに複数の経費精算書が入っている場合、その全てのワークブックについて同様に処理できたら便利だと思いませんか?

そうなんです。便利なんですよ。

今回は、フォルダ内の複数のワークブック全てを順番に処理していく方法についてお伝えします。

スポンサーリンク

前回のおさらい

前回のプログラムはこちらです。

アクティブワークブックと同じ階層の「data」というフォルダの中にある「経費精算書_経費.xlsx」というファイルについて開いて、「経費データ」シートに転記をします。

ただ、プログラム内で指定のワークブックしか使えないので、このプログラムはまだ実用的とは言えません。

今回は「data」というフォルダに複数の経費精算書が入っている場合は、全てのワークブックについて全部開いて「経費データ」シートにどんどん追加で転記をしていくようにしたいと思います。

For Each~Nextでフォルダの中の全てのファイルについて繰り返す

繰り返しの構文にFor Each~Nextというものがあります。

これがエクセルVBAでは半端なく便利なシロモノなのですが、記述方法としては

For Each オブジェクト変数 In コレクション

(プログラム)

Next オブジェクト変数

と書きます。

コレクションというのはオブジェクトの集合だと思って下さい。

意味としては、コレクション内の全てのオブジェクトてについて、一つずつ順番にオブジェクト変数にセットしながらプログラムを繰り返してね、という意味です。

これだけですと少しわかりづらいので、例えを使って説明をします。

対象とするオブジェクトをWorksheetオブジェクトとして以下のプログラムを実行します。

wsSampleはFor Each用に用意したWorksheet型のオブジェクト変数です。命名にルールはありませんので、好きなオブジェクト変数名でOKです。

このオブジェクト変数wsSampleにはFor Each~Nextを繰り返すたびにコレクション内にあるオブジェクトが順番にセットされます。

コレクションとしてはActiveWorkbook.Worksheetsが指定されています。つまりアクティブワークブックに含まれている全てのワークシートが設定されています。

  • オブジェクト変数:wsSample
  • コレクション:ActiveWorkbook.Worksheets

このFor Each~Nextは、アクティブワークブックに含まれる全てのワークシートについて、一つずつ順番にFor Each~Nextの中を繰り返してね、という意味になります。

例えば「Sheet1」「Sheet2」「Sheet3」の3つのワークシートが存在しているのであれば、このプログラムの実行により、それらのシート名が順番にイミディエイトウィンドウに表示されることになります。

イメージは湧いて頂けましたでしょうか?

ファイルシステムオブジェクト

今回の目的としては、特定のフォルダ内にあるワークブックをコレクションとして、そのワークブックそれぞれについて処理をしたいという要件になりますね。

ただ本題に入る前に、今回の場合は実は少し前準備が必要です。

フォルダ内のファイルを取り扱うときには、ファイルシステムオブジェクト(FileSystemObject)という特別なオブジェクトを準備する必要があります。

Dim FSOオブジェクト変数 As Object
Set FSOオブジェクト変数 = CreateObject(“Scripting.FileSystemObject”)

FSOオブジェクト変数は好きな名前でOKです。他の箇所は固定の文言なので、ファイルを使うときはおまじない的に記述してもらえればと思います。

例えば今回のプログラムの場合は

などと記述します。

これで準備が整いました。

フォルダ内のファイルをコレクションとして指定する

フォルダ内のファイルをFor Each~Nextのコレクションとして指定する書き方について説明します。

For Each オブジェクト変数名 In FSOオブジェクト.getfolder(フォルダのパス).Files

(プログラム)

Next オブジェクト変数名

と書きます。

ここでもオブジェクト変数名は任意で良いです。FSOオブジェクト名も先ほどの準備段階で決めたものですし、フォルダのパスも決まっています。

ですから今回のプログラムの場合は

などと記述すればOKですね。

objFileはFor Each用のファイルオブジェクトを格納するオブジェクト変数です。

strPathは「data」フォルダのパスを格納する文字列型の変数です。getfolder(ActiveWorkbook.Path & “\data\”)と直接括弧の中に記述しても良いのですが、ちょっと長いのと他でも使うので変数にしました。

オブジェクト変数とコレクションについて整理しますと

  • オブジェクト変数:objFile
  • コレクション:objFSO.getfolder(strPath).Files

となります。

完成プログラムと調整箇所

以上を盛り込みつつ、少し調整したプログラムがこちら。

まず冒頭のプログラムにこれまで解説してきた内容

  • ファイルパス用の変数準備とファイルパス取得(strPass)
  • ファイルシステムオブジェクト変数の準備(objFSO)
  • For Each用ファイルオブジェクト変数の準備(objFile)
  • For Each~Nextによる繰り返し

を追加しています。

For Each用のオブジェクト変数を使って開くファイルを指定

その他の調整箇所としては2点あります。まず1つ目です。

まずこの箇所ですが、objFile.Nameを使って開くべきファイルのファイル名を取得しています。For Each用のオブジェクト変数を使うことで、毎回の繰り返しで別のファイルを開くというわけです。

カウント変数をi,jの2つに

もう1点はカウント用変数をiとは別のjを用意したという点です。

冒頭のプログラムでは、経費精算書が1人分しかなかったので共通のカウント変数iを使えばよかったのですが、これが2人分以上となるとそうもいかなくなるのはわかりますか?

例えばAさんとBさんの二人分の経費精算書があるとします。

For Eachでそれぞれの経費精算書について

  1. ファイルを開く
  2. カウント変数iに0を代入
  3. 経費精算書のデータがなくなるまで「経費データ」シートにデータを追加

という処理を行います。

Aさんの処理が終わって、Bさんのファイルを開いた時を考えてみましょう。

この時、Bさんのファイルを開いた時点でカウント変数iは0にリセットされてしまいます。

ですが、「経費データ」シートにはAさんのデータが何行かすでに転記されているわけですから、「経費データ」シートのセルの位置の指定にカウント変数iを使ってしまうと、Aさんのデータが上書きされてしまいます。

ですから、それぞれの経費精算書のセル位置を指定するためのカウント変数iとは別の、「経費データ」シートのセル位置指定用カウント変数jを使わないと都合が悪いということになります。

カウント用変数をどのように準備して、どのように使用するかというのは、なかなか経験がいるところですので、実際の各シートの動きをよくイメージしながら練習を重ねてみてください。

まとめ

フォルダ内の複数のワークブック全てを順番に処理していく方法、一気に説明してきましたがいかがだったでしょうか?

この状態であればひとまずお仕事には使えるレベルにはなったかなと思います。

ちなみに、フォルダ内のファイルを参照するテクニックはお仕事の現場でかなりの活躍を見せてくれます。

経費精算書だけでなく、出勤管理表でも申込書でもフォーマットさえ統一されていれば活用をすることができますので、ぜひ一度作業の検討をしてみてくださいね。

次ですが、こちらの記事で

【エクセルVBA入門】人為的なミスを回避する方法その1~ファイル名を活用する
エクセルVBAを使ってバラバラの経費精算書データを集約するシリーズ第6回目です。人が入力する場合は必ず人為的なミスが入り込みます。それをうまく回避する方法の一つとしてファイル名を使う方法をお伝えします。

人為的なミスをなくすというテーマに挑んでいきます。

どうぞ合わせてご覧ください!

連載目次:経費精算書のデータをデータベースに集約する

  1. 【エクセルVBA入門】バラバラの経費精算書をデータにまとめる
  2. 【エクセルVBA入門】Do While~Loopで条件を満たす間繰り返し
  3. 【エクセルVBA入門】Withでプログラムをスッキリわかりやすく書く
  4. 【エクセルVBA入門】他のワークブックをWithで開く&保存せずに閉じる
  5. 【エクセルVBA入門】For Each~Nextでフォルダ内のブック全てを参照する
  6. 【エクセルVBA入門】人為的なミスを回避する方法その1~ファイル名を活用する
  7. 【エクセルVBA入門】人為的なミスを回避する方法その2~マスタを利用
  8. 【エクセルVBA入門】データの重複を防ぐSubプロシージャの作成