指定したフォルダ内のサブフォルダのフォルダ名を全部取得（Excel VBA）

【本日のミッション】

Excel VBAにて指定したフォルダ内のサブフォルダ名を全て取得し、Excelのシートに書き出せ

以前お話ししました

指定したフォルダ内とサブフォルダ内全てのファイル名を取得（Excel VBA）

のフォルダ名バージョンです。

1 ミッションの概要
2 ファイルの準備とプロシージャ
3 フォルダ取得の順番
4 Dir関数で取得したいフォルダ属性を指定
5 指定した属性以外の「標準ファイル」が含まれる理由
6 GetAttr関数とは
7 And演算子とは
8 GetAttr実行時に出てくる「実行時エラー’53: ファイルが見つかりません。」
9 “.”と”..”とは？
10 Callとは？
11 For Eachとは？
12 CreateObject(“Scripting.FileSystemObject”)とは
13 再帰処理とは？
14 【参考】

ミッションの概要

ファイル名はいらんねん。フォルダ名だけ欲しいねん。

って時ありますよね？

今回指定フォルダにするのはこちらです。このフォルダ内のサブフォルダ名を全てExcelのシートに書き出しましょう！

ファイルの準備とプロシージャ

Excelマクロ有効ブック（.xlsm）のファイルを新規作成します。

そのファイルのシートに下記の通り、フォルダのパスを入力するセル（A3）、取得したフォルダ名を書き出すセル（6行目1列目）を準備します。

フォルダパスには、フォルダ名を取得したいサブフォルダの上の階層のフォルダのフルパスを入力します。

今回は、「第1階層」フォルダ名は取得しません。

「第1階層」フォルダの中に入っているサブフォルダ全てのフォルダ名を取得します。

このシートをアクティブにした状態で、Alt＋F11でVsual Basic Editerを起動します。

［挿入］－［標準モジュール］で新規モジュールを作成し、下記の通りコードを記入します。

Sub 指定したフォルダ内のサブフォルダ名全部取得()
    'アクティブシートから処理対象のフォルダパスを取得し
    'そのフォルダ内のサブフォルダ名を全てアクティブシートに取得
    '【変数】
    Dim ws As Worksheet '処理対象シート
    Dim pt As String    '処理対象パス
    Dim t_row As Long   'フォルダ名書出行

    '■変数セット
    Set ws = ActiveSheet '変数ws=アクティブシートをセット
    pt = ws.Range("A3")  '変数ptにセルA3のパスをセット
    t_row = 6            'フォルダ名を書き出す初めのセルの行番号をセット
    
    '■指定したフォルダ内のサブフォルダ名をセルに書き出し
    Call フォルダ名一覧取得(pt, ws, t_row)
End Sub

Sub フォルダ名一覧取得(pt As String, ws As Worksheet, t_row As Long)
    Dim FSO As Object   'FileSystemObject
    Dim fd As String    'フォルダ名
    Dim s_fd As Object  'サブフォルダ

    fd = Dir(pt & "\" , vbDirectory) '変数fdに1個目のフォルダ名を格納
    Do While fd <> ""                'fdが空になるまでDo While内の処理を続ける
        If (GetAttr(pt & "\" & fd) And vbDirectory) = vbDirectory Then '標準ファイルを取得しないために必要
            If fd <> "." And fd <> ".." Then 'ナゾの「.」と「..」を取得しないために必要
                ws.Cells(t_row, 1) = fd      'Cells(t_row, 1)にフォルダ名を書き出し
                t_row = t_row + 1            'フォルダ名を書き出すセルの行番号+1（一つ下の行番号）
            End If
        End If
        fd = Dir() '変数fdに次のフォルダ名を格納（対象データが無い場合は空欄になる）
   Loop
    Set FSO = CreateObject("Scripting.FileSystemObject")   'FileSystemObjectのインスタンス化
    For Each s_fd In FSO.GetFolder(pt).SubFolders          '変数s_fdに対象フォルダ内のサブフォルダを順に取得
        Call フォルダ名一覧取得(s_fd.Path, ws, t_row)       '再帰処理
    Next s_fd
    Set FSO = Nothing 'FSOを空っぽにする
End Sub

前回と同様、2つのプロシージャ（Sub～End Subまで）に分かれています。

「Sub 指定したフォルダ内のサブフォルダ名全部取得()」プロシージャのどこかにカーソルを置いてF5キーをクリックしてください。

Excelのシートを見てください。

ほ～ら！！サブフォルダ名だけ、全部取得できましたよ～(#^.^#)

フォルダ取得の順番

どんな順番で取得しているかといいますと

おぉ～～～！！ファイル名取得の時とは順番が違いますね～。

■■■スポンサーリンク■■■

Dir関数で取得したいフォルダ属性を指定

Dir関数は、第1引数に指定したファイル・フォルダが存在する場合、パスを除いたファイル名・フォルダ名を返します。存在しない場合は長さ0の文字列 ”” 空欄 を返します。

Dir（ファイル・フォルダのパス,ファイル・フォルダの属性）

Dir (“C:\VBA\第1階層\” , vbDirectory) の場合

ファイル・フォルダが存在する場合（ファイル名・フォルダ名を返す）→ 第2階層1
ファイル・フォルダが存在しない場合（空欄を返す） → ””

第2引数に下記定数（値）を指定することで、特定の属性のオブジェクトのみを取得の対象とすることができます。

定数	値	属性
vbNormal (既定)	0	標準ファイル
vbReadOnly	1	読み取り専用ファイル
vbHidden	2	隠しファイル
vbSystem	4	システムファイル
vbVolume	8	ボリュームラベル。この値を指定すると、すべての属性は無効になります。
vbDirectory	16	フォルダ
vbAlias	64	エイリアスファイル

※4、8はMacでは使えません。また、64はMacintoshのみ使えます。

Dir関数は一度引数を指定すると、次に別の引数を指定するまで、同じ引数に対する処理を行います。

fd=Dir("C:\VBA\第1階層\", vbDirectory)
fd=Dir()
fd=Dir()

1行目の処理では、変数fdに「C:\VBA\第1階層\」に一致するファイル・フォルダが存在すれば、そのファイル名・フォルダ名を返します。

2行目以降の「fd=Dir()」では引数を指定していませんが、「C:\VBA\第1階層\」に一致する別のファイル名・フォルダ名を返します。

その処理を繰り返し、「C:\VBA\第1階層\」に一致するファイルが無くなった時、長さ0の文字列 ”” 空欄 を返します。

何度も「fd=Dir()」を書くのは面倒だし、どのタイミングでファイルが無くなるかわからないので、Do While ～ Loopを使って繰り返し処理を行います。

fd=Dir("C:\VBA\第1階層\", vbDirectory)
Do While fd <> ""         'fdが空欄になるまでDo While内の処理を続ける 
    ～fdで行いたい処理を記述～
    fd = Dir()            '変数fdに次のファイル名・フォルダ名を格納（ファイル名・フォルダ名が無い場合は空欄になる） 
Loop

参考：Dir関数が取得するファイルの順番
 参考：Dir関数の使い方。ファイル名やフォルダ名の取得方法。（Excel VBA）

指定した属性以外の「標準ファイル」が含まれる理由

フォルダ名を取り出したい場合、

fd = Dir(pt & "\" , vbDirectory)

の記述だけで十分って思ってしまいますよね。

この記述だけだと

フォルダ名以外に、なぜかファイル名と謎の”.”と”..”という文字を取得してしまっています。

Dir関数は第2引数に指定した定数の値の合計で、どの属性を取得するか判断しています。

Dir関数の定数一覧を見てみましょう。オレンジ文字のところが定数の値です。
それぞれの定数には、決められた値があります。

定数	値	属性
vbNormal (既定)	0	標準ファイル
vbReadOnly	1	読み取り専用ファイル
vbHidden	2	隠しファイル
vbSystem	4	システムファイル
vbVolume	8	ボリュームラベル。この値を指定すると、すべての属性は無効になります。
vbDirectory	16	フォルダ
vbAlias	64	エイリアスファイル

今回はフォルダを取得するため、「vbDirectory（16）」を指定しています。

Dir関数はこの「16」という値から

16 = vbNormal（0）＋vbDirectory（16）

標準ファイル vbNormal の「0」も含めて処理します。

どうあがいてもDir関数では、値が「0」の標準ファイルを処理対象としてしまいます。

指定した属性以外の標準ファイルを含めないようにするためにGetAttr関数を利用します。

GetAttr関数とは

GetAttr関数は引数に指定したファイルまたはフォルダの属性を、整数 (Integer型) で返します。

GetAttr(ファイル・フォルダのパス)

GetAttr関数が返すのは下記値になります。

定数	値	説明
vbNormal（規定値）	0	標準ファイル
vbReadOnly	1	読み取り専用ファイル
vbHidden	2	隠しファイル
vbSystem	4	システムファイル
vbVolume	8	ボリュームラベル
vbDirectory	16	フォルダ
vbArchive	32	アーカイブ
vbAlias	64	エイリアスファイル

※4、8、32はMacでは使えません。また、64はMacintoshのみ使えます。

例えば、下記のようなフォルダで・・・（それぞれのファイルには、ファイル名と同じ属性がつけられています。）

GetAttr("C:\VBA\Dir関数検証\03_読み取り専用ファイル.xlsx")

とした場合、読み取り専用ファイル vbReadOnly の値「1」を返します。

GetAttr("C:\VBA\Dir関数検証\01_フォルダ")

とした場合、フォルダ vbDirectory の値「16」を返します。

GetAttr("C:\VBA\Dir関数検証\11_隠しファイル属性付きフォルダ")

フォルダ vbDirectory（16）に、隠しファイル vbHidden（2）の属性もセットされている場合、合計値の「18」を返します。

しかし、合計値の「18」だけでは何の属性を含んでいるのか判断できません。

それを解決してくれるのが「And演算子」なのです。

■■■スポンサーリンク■■■

And演算子とは

ここで使われるAnd演算子は私たちがよく知っている

A And B → A かつ B

の意味では使われていません。

A And B

この場合のAnd演算子は、左辺のAと右辺のBをそれぞれビット単位に分解し比較します。

Aが「18」という値だった場合、「18」を

隠しファイル vbHidden（2）＋ フォルダ vbDirectory（16）

という風に、属性の値の単位に分解します。

左辺Aと右辺Bに同じビットが存在するときに、同じビットの値を返します。

“C:\VBA\Dir関数検証\11_隠しファイル属性付きフォルダ” が、隠しファイルの属性付きのフォルダで

GetAttr("C:\VBA\Dir関数検証\11_隠しファイル属性付きフォルダ") And vbDirectory

とした場合は「16」を返します。つまり「フォルダ」属性を含んでいることがわかります。

(GetAttr("C:\VBA\Dir関数検証\11_隠しファイル属性付きフォルダ") And vbDirectory) = vbDirectory

これらを括弧（）で囲み上記のようなコードにすると、「vbDirectory」(16）と同じ値を返したら「True」を返します。

GetAttr("C:\VBA\Dir関数検証\11_隠しファイル属性付きフォルダ") And vbHidden

とした場合は「2」を返します。つまり「隠しファイル」属性を含んでいることがわかります。

(GetAttr("C:\VBA\Dir関数検証\11_隠しファイル属性付きフォルダ") And vbHidden) = vbHidden

これらを括弧（）で囲み上記のようなコードにすると、「vbHidden」（2）と同じ値を返したら「True」を返します。

“C:\VBA\Dir関数検証\01_フォルダ”に「フォルダ」属性を含んでいるかどうか調べるときには、こんな感じにします。

If (GetAttr("C:\VBA\Dir関数検証\01_フォルダ") And vbDirectory) = vbDirectory Then
    MsgBox "フォルダです!"
End if

■■■スポンサーリンク■■■

GetAttr実行時に出てくる「実行時エラー’53: ファイルが見つかりません。」

GettAttrを初めて使ったとき

「実行時エラー’53: ファイルが見つかりません。」

というエラーを出してしまいました。

ファイル名ならDir関数を使って取ってきているはずなのに・・・

エラーを出した時、GetAttr関数の引数を下記のようにしていました。

fd = Dir(pt & "\" , vbDirectory)
If (GetAttr(fd) And vbDirectory) = vbDirectory  Then

変数fdにフルパス「C:\VBA\第1階層\File1_1.xlsx」が入っていると思ったのですが、Dir関数で取得しているのはパスを除いたファイル名・フォルダ名

File1_1.xlsx

だけなんです。

ファイル名だけを引数に入れられたGetAttrは

「そんなファイルありません」

と警告してくれていたのです。

GetAttrの引数には、上階層のパスも含めて入れてあげます。

GetAttr(pt & “\” & fd)

そうすると、実行時エラー53が出なくなります。

“.”と”..”とは？

「.」とは自分自身のいるカレントフォルダ、「..」はカレントフォルダの一つ上の階層のフォルダのことを指しています。

今回の処理では

fd = Dir(pt & "\", vbDirectory)

「pt=C:\VBA\第1階層」としているので

「.」は・・・「C:\VBA\第1階層」自分自身のフォルダ

「..」は・・・「C:\VBA」一つ上の階層のフォルダ

となります。

今回の処理では「.」や「..」フォルダは必要ありませんので、

If fd <> "." And fd <> ".." Then
End if

という処理で、除外しています。

Callとは？

他のSubプロシージャやFunctionプロシージャの処理を行います。

「Call」を省略して、プロシージャの名前だけでも処理してくれるのですが、私は後から見たとき、これナニ？ってならないように「Call」を付けるようにしています。

今回、私は

Call ファイル名一覧取得(pt, ws, t_row)

みたいに、プロシージャ名の後ろに「（引数）」を指定しています。

今日は簡単に説明しちゃいますね。

「指定したフォルダ内のサブフォルダ名全部取得」プロシージャ内で、Callにセットした「pt」「 ws」「 t_row」を引数のお弁当箱に入れて、「フォルダ名一覧取得」プロシージャに持っていきますよ～、みたいな感じです。

お弁当箱に入れて持ってきた引数は、持って行った先のプロシージャでも、それぞれが何者（データ型）か宣言し、そのプロシージャ内で利用します。

Sub フォルダ名一覧取得(pt As String, ws As Worksheet, t_row As Long)

この引数の深～～～いお話はまた次の機会にさせて頂きますね。
参照渡し「ByRef」と値渡し「ByVal」の違い（Excel VBA）

↓Callステートメントをもう少し詳しく
Callステートメントとは

For Eachとは？

For Each 変数 In コレクション
＊＊＊処理＊＊＊
Next

とすることで、コレクションの中にあるもの1つ目から順に変数に入れてNextまでの処理を行い、コレクションの中にあるもの全ての処理が終わったらNext以降の処理に移ります。

今回は「s_fd」という変数をObjectとして宣言しています。Objectはここではフォルダです。

For Each s_fd In FSO.GetFolder(pt).SubFolders

Call フォルダ名一覧取得(s_fd.Path, ws, t_row)

Next s_fd

コレクション部の「FSO.GetFolder(pt).SubFolders」ってナゾですよね。意味合いとしては、変数pt（処理対象パス）のフォルダのサブフォルダ群のことです。

CreateObject(“Scripting.FileSystemObject”)とは

こちらに関しては、下記ページをご参照ください。

CreateObject(“Scripting.FileSystemObject”) を使ってサブフォルダを取得

再帰処理とは？

こちらに関しては、下記ページをご参照ください。

再帰処理とは？フォルダ内とサブフォルダ内全てのファイル名を取得（Excel VBA）

【参考】

■■■スポンサーリンク■■■