フォルダに存在するファイルをExcelシートに一覧する（Excelツール）

2021.11.17

概要

ExcelとVBAを利用して、フォルダに存在するファイルを一覧するツールを作成してみましょう。
フォルダのパスを指定して実行するとサブフォルダの内容まですべて別シートに一覧します。

ツールの実行結果は、こんな感じ

Windowsのエクスプローラーでも
ファイルの一覧を表示することは出来ますが、
一覧を保存したいときや検索結果から
更に何かしたいときは、
いまいち使いずらいんですよね。

このツールを使ってシートに出力すれば、
後は煮るなり焼くなり自由自在です。

ツールの要件

Excelで、指定したフォルダに存在するファイルを一覧する。
（サブフォルダ内のファイルも対象とする。）

Input

フォルダの指定

・利用者は、menuシートにフォルダ（のパス）を指定出来る。

（例：menuシート G4セルにフォルダパスを指定出来る仕様)

Output

一覧表示

・listシートに結果を一覧表示する
・表示項目は、ファイル名とフォルダのパス名

（例：listシート A列にNo、B列にファイル名、C列にパスを表示する仕様)

ツール実行例

実行時の状態

フォルダのパスを指定した様子

（パス：C:\sample_testを指定）

指定したフォルダの内容

（パス：C:\sample_testの内容、配下をtreeコマンドで表示したもの）

treeファイルはコマンドの結果からわかる通り
ファイルは以下の6ファイルになります。

C:\sample_test配下

・test_01.txt
・test_02.txt
・test_03.txt

C:\sample_test\sample_test_sub01配下

・test_sub01_01.txt
・test_sub01_02.txt

C:\sample_test\sample_test_sub02配下

・test_sub02_01.txt

実行した結果

（前述した6ファイルが一覧された）

ツールの内容実装内容

本ツールは、Excel VBAで実装している。

コード

本ツールは、2つのSUBで構成している。
・メイン処理
・フォルダ処理
※コードをそのままコピーして実装する際は
ひとつのModuleに張り付けるようにしてください。
　（Private宣言を行っている変数があるため）

メイン処理

必要なオブジェクトの生成と参照やシート全体の処理を行っている。

'ブックオブジェクト（自分自身）
Private b As Workbook

'シートオブジェクト
Private s_menu As Worksheet
Private s_list As Worksheet

'指定したフォルダパスの保持域
Private spath As String

'一覧表示の際のラインカウンタ
Private line_ctr As Integer

'ヘッダ位置（行）
Const HEADPOS = 3

'
' メイン処理
'
Sub main()

 'このブックオブジェクトの参照を取得
  Set b = Workbooks("sample01.xlsm")

 'このブックのmenuシートオブジェクトの参照を取得
  Set s_menu = b.Worksheets("menu")
 'メニューシートからフォルダパスの値を取得して保持
  spath = s_menu.Cells(4, 7)
  
 'このブックのlistシートオブジェクトの参照を取得
  Set s_list = b.Worksheets("list")
  s_list.Activate
  
 '指定されたフォルダパスをlistシートに表示
  s_list.Cells(2, 1) = "（指定フォルダ：" & spath & "）"
  
  'listシートに以前の結果が残っていたらクリア
  lastrow = s_list.Cells(Rows.Count, 1).End(xlUp).Row
  If lastrow > HEADPOS Then
    temprange = "4:" & Trim(Str(lastrow))
    s_list.Range(temprange).Delete
  End If
  
  'ラインカウンタクリア
  line_ctr = HEADPOS
  FolderSearch spath
  
  
  '罫線
  Range(s_list.Cells(4, 1), s_list.Cells(line_ctr, 3)).Borders.LineStyle = xlContinuous
  
  MsgBox "おわり"
  
End Sub
説明

全体的な説明はコメントを参照
1-15行め：モジュールのスコープで変数と定数の宣言指定をしています。
　　　　　（ここで宣言したものはメイン処理、フォルダ処理の双方から参照可能）
22-32行め：ブックオブジェクトやシートオブジェクトの参照
34-35行め：フォルダパスの取得
37-42行め：listシートの初期化（前回の結果をクリア）
44-46行め：フォルダ処理の実行
49-52行め：listシートの罫線加工と終了メッセージ

フォルダの処理

サブフォルダの中まで一覧したいため、再帰検索しながら処理をすすめる。

'
' フォルダ内の再帰検索処理
'
Private Sub FolderSearch(foldername As String)

  Dim fso As Object
  Dim folder As Object
  Dim subfolder As Object
  Dim file As Object
  
  'FileSystemObjectオブジェクト作成
  Set fso = CreateObject("Scripting.FileSystemObject")
  Set folder = fso.GetFolder(foldername)
  
  'サブフォルダの再帰処理
  For Each subfolder In folder.SubFolders
    FolderSearch subfolder.Path
  Next subfolder
  
  'フォルダ内に存在するファイル毎の処理
  For Each file In folder.Files
    With file
      'ラインカウンタアップ
      line_ctr = line_ctr + 1
      '1行分編集
      s_list.Cells(line_ctr, 1) = line_ctr - HEADPOS
      s_list.Cells(line_ctr, 2) = file.Name
      s_list.Cells(line_ctr, 3) = foldername
    End With
  Next file

End Sub
説明

全体的な説明はコメントを参照
4行め：サブの入り口
　　　　フォルダーのパスを受け取ってlistシートにファイルを一覧する
6-9行め：Scripting.FileSystemObjectのオブジェクト用
11-13行め：Scripting.FileSystemObjectのオブジェクト作成
15-18行め：処理中のフォルダに存在するサブフォルダの処理
　　　　　（自分で自分を呼ぶ、再帰処理）
20-30行め：処理中のフォルダに存在するファイルの処理
　　　　　listシートに書き出す。

補足

このツールは、メイン処理とフォルダ処理で構成されており
フォルダやサブフォルダに存在するファイルを一覧する処理をしますが
ファイルを一覧する部分（フォルダ処理の21行めのループ処理内）に
Excelブックを対象にする処理を実装することで
フォルダに存在するExcelブックに対する一括処理を行う際のひな型として使用することが出来ます。

次回は、このツールを拡張して
フォルダ内に存在するすべてのExcelブックを加工するツールを
掲載する予定です。

タイトルとURLをコピーしました