目次
フォルダ一覧表の重要性
「あのファイルはどこに入れたっけ？」
仕事上頻繁に発生する小さな問題がファイルを探す手間です。
重要なファイルが必要な時に限って予想のフォルダに入っていなかった、最終的にはエクスプローラで検索して探した、といった小さな手間が積み重なると業務上の大きな無駄となります。
当サイトでは以前ファイル一覧表を生成する方法をご紹介いたしました。
今回はその応用として、フォルダごと一覧表を作成する方法をご紹介します。
フォルダ一覧表をVBAで自動生成する方法
整理するフォルダの例
今回は次のようなフォルダを用意しました。
ドキュメント直下に「2020」というフォルダを作成し、いくつかの階層にフォルダを作成してファイルを散りばめました。
ファイルとボタンの作成
次のようにExcelファイルを用意します。
拡張子はマクロ有効ブックの「 .xlsm 」です。
ボタンの作成については以前の記事で詳しく紹介しています。
FileSystemObject の導入
以前はファイル操作をDirで行いましたが、今回はさらに複雑になるので操作を単純にするための機能を導入します。
FileSystemObjectはVBAの参照設定から導入します。
[Microsoft Scripting Runtime] をチェックします。
これで FileSystemObject を利用してファイル操作を行うことができるようになりました。
VBAのソースコード
次のように記述しました。
Public Const ParentFolder = "C:\Users\user\Documents\2020"
Public fso As FileSystemObject
Public pathbuf(10) As String
Public fpl As Object
Public l As Folder
Public e As File
Public i As Long
Public level As Long
Public num As Long
Sub getfilename()
Set fso = New FileSystemObject
level = 0
num = 5
Cells(num, level + 2) = ParentFolder
Set fl = fso.GetFolder(ParentFolder)
Call getsubfolder(fl)
Set fso = Nothing
End Sub
Function getsubfolder(fp) As Object
For Each l In fp.SubFolders
level = level + 1
Debug.Print (l.Name)
Cells(num, level + 2) = l.Name
pathbuf(level) = l.Name
Set fpl = fso.GetFolder(fp + "\" + l.Name)
Call getsubfolder(fpl)
Next
For Each e In fpl.Files
Debug.Print (e.Name)
Cells(num, level + 3) = e.Name
num = num + 1
Next
i = Len(pathbuf(level))
fps = Left(CStr(fp), Len(fp) - i)
level = level - 1
Set fpl = fso.GetFolder(fps)
End Functionソースコードの解説
VBAのコードを1行ずつ解説していきます。
定数・変数の宣言（１～９行目）
Public Const ParentFolder = "C:\Users\user\Documents\2020"Public Const（グローバル定数）で親フォルダのパスを宣言します。
Public fso As FileSystemObjectFileSystemObjectでファイル一覧を取得するための準備です。
Public pathbuf(10) As Stringパスの操作をする場合のバッファとして用意した文字列の配列です。
Public fpl As Object再起呼び出しのために用意したグローバル変数のパスです。
Public l As Folder取得したフォルダ名が格納されます。
Public e As File取得したファイル名が格納されます。
Public i As Longパスの文字列長さを計算するために用意した変数です。
Public level As Longフォルダの階層を表します。親フォルダが０、子フォルダが１、孫フォルダが２と続きます。
num = 5全体のファイルの数を表します。
メイン記述部分
Sub getfilename()プロシージャを定義します。
Set fso = New FileSystemObjectFileSystemObjectを利用する準備です。
level = 0階層を0にリセットします。
num = 5フォルダ数をリセットします。今回はExcelシートの5行目から記述したかったのであらかじめ5を足してあります。
Cells(num, level + 2) = ParentFolder親フォルダをExcelシートに記述します。
Set fl = fso.GetFolder(ParentFolder)親フォルダからフォルダ一覧を取得します。
Call getsubfolder(fl)後述する関数を呼び出します。
Set fso = Nothingフォルダを参照した後メモリを解放するための処理です。
End Subプロシージャの終わりです。
フォルダ名を記述する関数
Function getsubfolder(fp) As Object関数を定義します。
For Each l In fp.SubFolders取得したフォルダ一覧の各要素について操作することを意味します。
level = level + 1階層を1段深くします。
Cells(num, level + 2) = l.Nameフォルダ名を記述します。
pathbuf(level) = l.Nameパスのバッファにフォルダ名を格納します。
Set fpl = fso.GetFolder(fp + "\" + l.Name)親フォルダのパスに子フォルダ名を追加して新しいパスを作成します。さらにそこからフォルダ一覧を取得します。
Call getsubfolder(fpl)関数の再起呼び出しをおこないます。
Next7行前の For Each に対応します。次の要素に進むことを意味します。
For Each e In fpl.Filesファイル一覧の操作を意味します。
Cells(num, level + 3) = e.Nameファイル名の記述を示しています。
num = num + 1ファイル数を1つ増やします。
Next次のファイルに進むことを意味します。
i = Len(pathbuf(level))格納したパスの文字列長さを算出します。
fps = Left(CStr(fp), Len(fp) - i)1つ上のパスに戻るときの操作を意味します。格納したパスの長さ分だけ最新のパスからマイナスすることで求めています。
level = level - 1階層を1つ登ります。
Set fpl = fso.GetFolder(fps)1つ上のパスで再びフォルダ一覧を取得します。
End Function関数の終了を意味します。
VBAの実行結果
こちらが実行結果です。
同じ階層にどんなフォルダ・ファイルがあるかが一目でわかるようになりました。
ファイルを検索したいときもExcel内で「Ctrl+F」で検索したり、Vlookup関数で検索したりすることも可能です。
まとめ
- VBAでフォルダ一覧表が自動生成できる
- FileSystemObjectでファイル操作が簡単になる
- 再起呼び出しを使って親フォルダ直下全てのファイルを取得できる
