photo credit: Kecko Salt – Cellular Network Station via photopin (license)
みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。
エクセルVBAでバラバラの経費精算書をデータベースに集めるマクロの作り方をお伝えしています。
前回の記事はコチラ。
OpenメソッドとWith文を組み合わせて他のブックを開く方法をお伝えしました。
さて、今回から「フォルダの中のブックを順番に開く」という処理の作り方の解説に入っていきます。
だって、経費精算書はいくつも個別のファイルで集まってきますからね。
その第一歩として、今回はエクセルVBAでフォルダやファイルを操作するFileSystemオブジェクトとその使い方についてお伝えしていきます。
では、行ってみましょう！
経費精算データを集めるマクロ
では、目指すマクロについておさらいしておきましょう。
まず、社内のスタッフごとに、以下のようなフォーマットの「経費精算書」を作成しています。
皆さんから集められた経費精算書は、「data」というフォルダに集められます。
それで、その「data」フォルダと同じ階層にあるブックに、以下のような「経費データ」というシートがありまして
ポチっとマクロを動作させると、各ファイルの経費データがこの「経費データ」に集まってくるという寸法です。
これまで何回もコピペしていた作業が、ボタン一つで実行できるというわけです。
便利そうですよね～
VBAでフォルダやファイルを操作する
FileSystemオブジェクトとは
それで、フォルダ「data」の中のブックを順番に開くという操作が必要になりますよね。
「フォルダ」というのは、普通に考えたら「Excelの外の世界」になりますから、さすがにエクセルVBAでは操作できないかな？と思ってしまうのですが、そこはVBA。
実は、その外の世界を操作するための仕組みが用意されています。
それがFileSystemオブジェクトです。
FileSystemオブジェクトとは、ドライブやフォルダ、ファイルなどを操作するためのオブジェクトです。
FileSystemオブジェクトを通して、フォルダやファイルをFolderオブジェクトやFileオブジェクトとして取得し、操作ができるようになります。
Scriptingライブラリを追加する
FileSystemオブジェクトは「Scriptingライブラリ」に含まれていますので、参照設定から追加をしておくと便利です。
その方法をお伝えしますね。
まず、VBEのメニューの「ツール」→「参照設定」とたどります。
「参照設定」ダイアログが開きますので、「Microsoft Scripting Runtime」という項目を探して、チェックを入れて「OK」です。
これでScriptingライブラリの追加は完了です。
試しにコードウィンドウで「dim fso as filesy…」と打ってみると、その時点で自動メンバー表示の候補として「FileSystemObject」が登場していることが確認できるはずです。
FileSystemオブジェクトの生成
さて、FileSystemオブジェクトを使用する場合には「生成」という段取りを踏む必要があります。
FileSystemオブジェクトを生成、つまり作り出すわけですね。
Newステートメントを使って以下のように表現します。
生成したFileSystemオブジェクトは、生成した直後にオブジェクト変数にセットして扱うのが一般的ですので、以下のように変数の宣言とセットと組み合わせて書くことが多いでしょう。
Dim fso As FileSystemObject
Set fso = New FileSystemObject
CreateObject関数での生成との違い
FileSystemオブジェクトの生成は、以下のようにCreateObject関数を使うことでも実行可能です。
しかもこちらの方法では「Scriptingライブラリ」の追加をせずに使用可能です。
なら、こっちのほうがいいじゃん！？
ってなるのですが、ライブラリを追加するメリットもあります。
ライブラリで追加すると、それに含まれるオブジェクトとそのメンバーについて、自動メンバー表示の対象となるのです。
先ほど、FileSystemObjectが候補として表示されたのを確認しましたよね。
なので、ライブラリ追加にはひと手間いるのですが、その後のコーディングはかなり楽になるわけです。
FileSystemオブジェクトでフォルダを取得する
では、試しに以下のプロシージャを実行してみましょう。
Sub フォルダの取得()
Dim fso As FileSystemObject
Set fso = New FileSystemObject
Debug.Print fso.GetFolder(ThisWorkbook.Path & "\data").Name
End Sub
現在のマクロブックと同じフォルダに「data」というフォルダが存在していれば、イミディエイトウィンドウにそのフォルダ名が出力されるはずです。
GetFolderメソッドでフォルダを取得する
このプロシージャについて解説していきましょう。
まず、FileSystemオブジェクトに対して、GetFolderメソッドを使うことでフォルダを取得することができます。
フォルダのパスを引数として指定して、以下のように記述します。
これで、指定したパスのフォルダをFolderオブジェクトとして取得することができます。
また、以下のようにFolderオブジェクトのNameプロパティで、フォルダ名を取得できます。
Nameプロパティは色んなオブジェクトが持っているので、確認用として便利ですね。
まとめ
以上、エクセルVBAでフォルダやファイルを操作するFileSystemオブジェクトとは、またその導入方法についてお伝えしました。
ライブラリの追加の方法、FileSystemオブジェクトの生成、Folderオブジェクトの取得など、重要ポイント盛りだくさんでしたね。
次回は、このFileSystemオブジェクトを使って、フォルダの中のすべてのブックについて繰り返す処理の書き方についてお伝えします。
どうぞお楽しみに！
連載目次：エクセル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入門】オートフィルタや行の非表示で隠れている行を全て表示する
