フォルダ内のフォルダ名全てをGetAttrを使って「エラー53 ファイルが見つかりません。」を出さずに取得(Excel VBA)

【本日のミッション】

Excel VBAにて、指定したフォルダ内のフォルダ名全てをGetAttrを使って「53 ファイルが見つかりません。」というエラー出さずに取得し、Excelのシートに書き出せ

以前お話ししました

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

のフォルダ版です。

ファイルのところをフォルダに変えればいいだけやん♪

と軽く考えてしまうのですが、ファイルをフォルダに変えるのがやっかいなんです。

今回のミッションでは、下記フォルダから「第2階層1」「第2階層2」「第2階層3」の3つのフォルダ名のみを取り出したいのです。

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

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

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

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

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

■スポンサーリンク

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

    '■変数セット 
    Set ws = ActiveSheet '変数ws=アクティブシートをセット 
    pt = ws.Range("A3")  '変数ptにセルA3のパスをセット 
    t_row = 6            '変数t_rowにファイル名を書き出す初めのセルの行番号をセット 
    buf = Dir(pt & "\" & "*.*", vbDirectory) 'Dir関数の引数に「vbDirectory」=「フォルダ」を指定
    Do While buf <> ""                       'bufが空になるまでDo While内の処理を続ける
    If GetAttr(pt & "\" & buf) And vbDirectory Then '※1:標準ファイルを取得しないために必要
        If buf <> "." And buf <> ".." Then          '※2:ナゾの「.」と「..」を取得しないために必要
            ws.Cells(t_row, 1) = buf                'Cells(t_row, 1)にフォルダ名を書き出し 
            t_row = t_row + 1                       'フォルダ名を書き出すセルの行番号+1(一つ下の行番号)
        End If
    End If buf = Dir() '変数bufに次のフォルダ名を格納(対象データが無い場合は空欄になる)
Loop
End Sub

記入したモジュールのどこかにカーソルを置いてF5キーをクリックしてください。

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

フォルダ名を書き出すセルは6行目の1列目から下に順に書き出すようなコードにしています。

第一階層フォルダに入っている3つのフォルダ名のみが書き出されました♪

ここで難しいポイントは4ヶ所。

■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)

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

この記述だけだと

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

実は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とは?「= vbDirectory」ではなく「And vbDirectory」となるビット演算の疑問

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

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

こちらの解説も後日させて頂きます。

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

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

■スポンサーリンク

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