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

【本日のミッション】

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

以前お話ししました

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

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

ファイル名はいらんねん。フォルダ名だけ欲しいねん。って時ありますよね?

今回指定フォルダにするのはこちらです。

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 buf As String 'フォルダ名
    Dim fd As Object  'フォルダ

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

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

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

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

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

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

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

以前にもお話ししていますが、再度ややこしポイントを押さえておきますね。

■スポンサーリンク

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

Dir関数は第2引数に下記属性を指定することができます。

ファイル名のみを取り出したかった前回は標準ファイルを取り出したい時に指定する定数「vbNormal」が規定値なので、何も指定せずに取得できたのです。

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

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

フォルダ名だけ取り出したいなら

buf = Dir(pt & “\” & “*.*”, vbDirectory)

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

この記述だけだとフォルダ名以外に、第1階層のファイル名、サブフォルダ内のファイル名全てと謎の”.”と”..”という文字を取得してしまいます。

実はDir関数は引数に指定した定数の値の合計で、何をとってこないといけないか判断しているのです。

今回は「vbDirectory」しか指定していないので合計値は「16」になります。

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

vbNormal(0)+vbDirectory(16)=16

と、勝手にvbNormal(0)も含めちゃうんです。厄介な関数ですね。

なのでその後にある

If GetAttr(pt & “\” & buf) And vbDirectory Then
If buf <> “.” And buf <> “..” Then

みたいな判定式が必要になってきます。

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

If GetAttr(pt & “\” & buf) And vbDirectory Then

GettAttrを初めて使ったとき

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

ってエラーが出ました。

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

buf = Dir(pt & “\” & “*.*”, vbDirectory)

上記コードを見ると、

「buf=C:\VBA\第1階層\File1_1.xlsx」

なんじゃないの?って思ってしまうんですけど、Dir関数で取得しているのはファイル名のみ

「File1_1.xlsx」

なんです。

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

「そんなファイル、存在しないぜ!ハハハハ!!」(伊之助風口調で)

と主張していたのです。

なので、GetAttrの引数でbufを使うときには、上階層のパスも含めて入れてあげます。

GetAttr(pt & “\” & buf)

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

■GetAttrの「And」とは?

If GetAttr(pt & “\” & buf) And vbDirectory Then

何度見ても理解できない、この「And」の使い方。

とりあえず、このまま使っておけば問題なのですが、気になりますよね?

GetAttrと「And」の話は、下記ページをご参照ください。

GetAttrとは?「= vbDirectory」ではなく「And vbDirectory」となるビット演算の疑問

■”.”と”..”とは?

これも一般Peopleには理解できないヤツです。

「.」 と 「..」につきましても、長くなりますのでこちらをご参照ください。

フォルダ名だけを取得したい時に出てくる 「.」 と 「..」 とは?

■Callとは?

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

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

今回、私は

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

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

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

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

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

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

この引数の深~~~いお話はまた次の機会にさせて頂きますね。

参照渡し「ByRef」と値渡し「ByVal」の違い(Excel VBA)

■For Eachとは?

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

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

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

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

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

Next fd

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

■CreateObject(“Scripting.FileSystemObject”)とは

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

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

■再帰処理とは?

こちらに関しては後日、ご説明させていただきます。

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

【参考】
指定したフォルダ内のファイル名全てを取得(Excel VBA)
変数でよく使われる「buf」「tmp」の意味
Dir関数が取得するファイルの順番
フォルダ内のフォルダ名全てをGetAttrを使って「エラー53 ファイルが見つかりません。」を出さずに取得(Excel VBA)
GetAttrとは?「= vbDirectory」ではなく「And vbDirectory」となるビット演算の疑問
フォルダ名だけを取得したい時に出てくる 「.」 と 「..」 とは?
指定したフォルダ内とサブフォルダ内全てのファイル名を取得(Excel VBA)
CreateObject(“Scripting.FileSystemObject”) を使ってサブフォルダを取得
再帰処理とは?フォルダ内とサブフォルダ内全てのファイル名を取得(Excel VBA)
参照渡し「ByRef」と値渡し「ByVal」の違い(Excel VBA)

■スポンサーリンク

■ランキングに参加しています。
↓このブログを気に入っていただけましたら、ポチッとお願いします。
にほんブログ村 IT技術ブログへ
にほんブログ村