フォルダ一覧表を自動生成して検索の手間を省略する【Excel VBA】

フォルダ一覧表を自動生成して検索の手間を省略する【Excel VBA】

フォルダ一覧表の重要性

「あのファイルはどこに入れたっけ?」

仕事上頻繁に発生する小さな問題がファイルを探す手間です。

重要なファイルが必要な時に限って予想のフォルダに入っていなかった、最終的にはエクスプローラで検索して探した、といった小さな手間が積み重なると業務上の大きな無駄となります。

当サイトでは以前ファイル一覧表を生成する方法をご紹介いたしました。

ファイル一覧表を自動生成する方法

今回はその応用として、フォルダごと一覧表を作成する方法をご紹介します。

フォルダ一覧表を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行ずつ解説していきます。

定数・変数の宣言(1~9行目)

Public Const ParentFolder = "C:\Users\user\Documents\2020"

Public Const(グローバル定数)で親フォルダのパスを宣言します。

Public fso As FileSystemObject

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

フォルダの階層を表します。親フォルダが0、子フォルダが1、孫フォルダが2と続きます。

num = 5

全体のファイルの数を表します。

メイン記述部分

Sub getfilename()

プロシージャを定義します。

Set fso = New FileSystemObject

FileSystemObjectを利用する準備です。

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)

関数の再起呼び出しをおこないます。

Next

7行前の 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関数で検索したりすることも可能です。

まとめ

  1. VBAでフォルダ一覧表が自動生成できる
  2. FileSystemObjectでファイル操作が簡単になる
  3. 再起呼び出しを使って親フォルダ直下全てのファイルを取得できる
(Visited 3,115 times, 19 visits today)

コメントを残す

メールアドレスが公開されることはありません。