【ExcelVBA】サブフォルダ含めてフォルダ内の全ファイルの拡張子の種類を取得するには

この記事では、サブフォルダ含めてフォルダ内の全ファイルの拡張子の種類を取得する方法についてご説明します。

すぐにソースコードを見たい場合はこちらをクリック

Table of Contents

【動画】サブフォルダ含めてフォルダ内の全ファイルの拡張子の種類を取得する実際の動き

本題に入る前に、まずは次の動画をご覧ください。

FileSystemObjectオブジェクトのGetExtensionNameメソッドにファイルを指定して実行して拡張子を取得しています。

目次へ戻る

サブフォルダ含めてフォルダ内の全ファイルの拡張子の種類を取得する方法

サブフォルダ含めてフォルダ内の全ファイルの拡張子の種類を取得するには、次の流れの通りマクロを作成します。

マクロ作成の流れ

STEP.1

コマンドプロンプトを呼び出してサブフォルダ含めてフォルダ内の全てのフォルダ名とファイル名を取得したいディレクトリをCSVファイルに書き出す

コマンドプロンプトを呼び出してサブフォルダ含めてフォルダ内の全てのフォルダ名とファイル名を取得したいディレクトリをCSVファイルに書き出します。

STEP.2

STEP.1のCSVファイルを読み込む

STEP.1のCSVファイルを読み込みます。

STEP.3

読み込んだCSVファイルから、フォルダかファイルかを判定する

読み込んだCSVファイルから、フォルダかファイルかを判定します。

STEP.4

フォルダの場合は何もせず、ファイルの場合は拡張子を取得する

フォルダの場合は何もせず、ファイルの場合は拡張子を取得します。
FileSystemObjectオブジェクトのGetExtensionNameメソッドを使うことでファイルの拡張子が取得できます。
なお拡張子のないファイルは、取得される拡張子がないです。(GetExtensionNameメソッド実行後何も返ってこない)

マクロを実行するExcelファイル(例)

今回ご紹介するマクロを実行するExcelファイル(例)は次の画像の通りです。

「searchpath」という名前が付けられたオレンジ色のセルに、サブフォルダ含めて全ファイルの拡張子の種類を取得したいパスを入力して実行ボタンをクリックするとマクロが実行されます。

マクロが実行されて取得された拡張子は、「拡張子」と書かれたセルの下のセルに出力されます。(下の画像が、マクロ実行後に拡張子が出力された後のイメージ)

コードの例

Excelのマクロのコードは次の通りに記述していきます。

Option Explicit

Private Sub btn_exec_Click()

    Dim buf                 As String                   '一時的な値の格納先変数
    Dim getPath             As String                   'サブフォルダ含めて全てのフォルダ名とファイル名を取得したいパス
    Dim cmdTxt              As String                   'コマンドプロンプトのコード用変数
    Dim fldrFileNMExptTxt   As String                   'フォルダ名とファイル名が出力されたファイル名
    Dim st                  As ADODB.Stream             'バイナリ データまたはテキストのストリームのインスタンス用変数
    Dim wshObj              As WshShell                 'WshShellオブジェクト
    Dim fso                 As FileSystemObject         'FileSystemObjectのインスタンス用変数
    Dim rowCnt              As Integer                  '行位置用カウンタ
    Dim noExtflg            As Boolean                  '拡張子無判定フラグ
                                                        '→True：拡張子がないファイルあり／False：拡張子がないファイルなし
    
    'フォルダ名とファイル名を出力させるCSVファイル名を取得する
    fldrFileNMExptTxt = ActiveWorkbook.Path & "\" & "data.csv"
    
    'サブフォルダ含めて全ファイルの拡張子の種類を取得したいパス
    '→「searchpath」という名前の付いたセルに入力されたパスが、変数「getPath」に格納されます。
    getPath = Sheets("top").Range("searchpath").Value
    
    '拡張子を出力する最初のセルの行
    rowCnt = 6
    
    '拡張子無判定フラグにFalseを設定する
    noExtflg = False
    
    'バイナリ データまたはテキストのストリームのインスタンスを生成する
    Set st = New ADODB.Stream
    
    'FileSystemObjectのインスタンスを生成する
    Set fso = New FileSystemObject
    
    'WshShellオブジェクトからインスタンスを生成する
    Set wshObj = New WshShell
            
    If Right(getPath, 1) <> "\" Then
    
        '入力されたパスの末尾に「\」が付いていない場合に付ける
        getPath = getPath & "\"
        
    End If
    
    '実行するコマンド：dirコマンドでフルパスをCSVファイルに書き出す
    cmdTxt = "chcp 65001 | dir /b /s " & """" & getPath & """" & " > " & fldrFileNMExptTxt
    
    'コマンドプロンプトで変数「cmdTxt」のコマンドを実行する
    Call wshObj.Run("%ComSpec% /c " & cmdTxt, 1, WaitOnReturn:=True)
        
    With st
    
        .Charset = "UTF-8"                              '文字セットにUTF-8を設定する
        .Open                                           'streamを開く
        .LoadFromFile fldrFileNMExptTxt                 'フォルダ名とファイル名が出力されたファイルから読み込む
        
        If fso.GetFile(fldrFileNMExptTxt).Size = 0 Then
        
            '検索対象のデータが存在しない場合
            
            MsgBox "ファイルがありません。"
            
            '処理終了
            Exit Sub

        End If
        
        'Streamの末尾まで繰り返す
        Do Until .EOS
                
            '取り出したテキストを変数「buf」に格納する
            buf = .ReadText(adReadLine)
            
            'フルパスがフォルダなのかファイルなのかを判定する(True：ファイル／False：フォルダ)
            If fso.FileExists(buf) Then
                
                'ファイルの場合
                
                If fso.GetExtensionName(buf) = "" Then
            
                    '拡張子なしのファイルの場合
                
                    If noExtflg = False Then
                    
                        '拡張子無判定フラグがFalseの場合
                                        
                        '拡張子無判定フラグにTrueを設定する
                        noExtflg = True
                    
                    End If
                
                Else
                
                    '拡張子ありのファイルの場合
                    
                    If WorksheetFunction.CountIf(Range("E6:E" & rowCnt), fso.GetExtensionName(buf)) = 0 Then
                    
                        'まだシートに出力していない拡張子の場合
                    
                        '拡張子を出力する
                        Range("E" & rowCnt).Value = fso.GetExtensionName(buf)
                    
                        'カウンタを1つ増やす
                        rowCnt = rowCnt + 1
                        
                    End If
                
                End If
                                                                           
            End If
            
            DoEvents
            
        Loop
        
        'Streamの末尾まで処理が終わったので、streamを閉じる
        .Close
           
    End With
    
    '出力された拡張子の並びをソートする(ここでは昇順とする)
    Call Range("E6:E" & rowCnt).Sort(Range("E6"), Order1:=xlAscending)
    
    If noExtflg Then
    
        '拡張子がないファイルが存在している場合

        '拡張子がないファイルの場合はセルが空白なので、分かるように最終行に「拡張子無し」と出力する
        Range("E" & rowCnt).Value = "拡張子無し"

    End If    
    
    '後処理
    
    'フォルダ名とファイル名が出力されたファイルを削除する
    Call fso.DeleteFile(fldrFileNMExptTxt, True)
    
    Set wshObj = Nothing
    Set st = Nothing
    Set fso = Nothing

End Sub

コードの解説

注目すべきコード①

最初に見て頂きたいのは46行目から49行目です。

    '実行するコマンド：dirコマンドでフルパスをCSVファイルに書き出す
    cmdTxt = "chcp 65001 | dir /b /s " & """" & getPath & """" & " > " & fldrFileNMExptTxt
    
    'コマンドプロンプトで変数「cmdTxt」のコマンドを実行する
    Call wshObj.Run("%ComSpec% /c " & cmdTxt, 1, WaitOnReturn:=True)

46行目でフォルダ名とファイル名を出力させるCSVファイルの出力を行うコマンド文(dirコマンド)を変数「cmdTxt」に格納し、49行目でそのコマンドをコマンドプロンプトで実行します。

なぜわざわざコマンドプロンプトを使っているのか(呼び出しているのか)というと、処理の高速化を図るためです。

大量のフォルダやファイルが存在する場合に、マクロだけでやろうとすると時間がかかりすぎるために高速化が実現できるコマンドプロンプトを使っています。
(※絶対にコマンドプロンプトを使わなければいけないというわけではありませんが、今回は手軽に使うことができるコマンドプロンプトを採用しました)

話がそれたので話を戻しますが、コマンドプロンプトで実行するコマンド文は、実は2つのコマンドを1行で実行しています。

2つのコマンド(例)は次の通りです。(コマンド文は「部分一致」のコマンド文)
①chcp 65001
②dir /b /s “C:\work\” > C:\work\data.csv

コマンドの説明

①のコマンドは、文字コードをUTF-8に設定するコマンドで、文字化けしないように文字コードをUTF-8に設定しています。
(文字コードをUTF-8に設定すると文字化けしない)
②のコマンドは、dirコマンドを実行して、サブフォルダ含めて全ファイルと全フォルダの一覧をファイル「data.csv」に出力しています。

コマンドの「|」とは？

コマンドで使われている「|」の文字は、実行する複数のコマンドを1行で記述したい時に使う文字です。

以上、上記のコマンドが実行されると、フォルダ名とファイル名が出力された「data.csv」というファイルが出力されます。

この「data.csv」をExcelマクロが読み込んでフォルダかファイルかを判定し、ファイルの場合は拡張子を取得します。

ちなみに、フォルダ名とファイル名を全てセルに書き出したら、必要がなくなるので136行目で削除します。

    'フォルダ名とファイル名が出力されたファイルを削除する
    Call fso.DeleteFile(fldrFileNMExptTxt, True)

注目すべきコード②

次に見て頂きたいのは51行目から55行目です。

    With st
    
        .Charset = "UTF-8"                              '文字セットにUTF-8を設定する
        .Open                                           'streamを開く
        .LoadFromFile fldrFileNMExptTxt                 'フォルダ名とファイル名が出力されたファイルから読み込む

53行目で文字コードをUTF-8に設定し、フォルダ名とファイル名が出力された「data.csv」をExcelマクロが読み込むためにCharsetプロパティに「UTF-8」の文字列を設定しています。

次に54行目でOpenメソッドを実行し、55行目のLoadFromFile メソッドの引数に「data.csv」を設定して「data.csv」を読み込みます。

注目すべきコード③

次に見て頂きたいのは69行目から117行目です。

        'Streamの末尾まで繰り返す
        Do Until .EOS
                
            '取り出したテキストを変数「buf」に格納する
            buf = .ReadText(adReadLine)
            
            'フルパスがフォルダなのかファイルなのかを判定する(True：ファイル／False：フォルダ)
            If fso.FileExists(buf) Then
                
                'ファイルの場合
                
                If fso.GetExtensionName(buf) = "" Then
            
                    '拡張子なしのファイルの場合
                
                    If noExtflg = False Then
                    
                        '拡張子無判定フラグがFalseの場合
                                        
                        '拡張子無判定フラグにTrueを設定する
                        noExtflg = True
                    
                    End If
                
                Else
                
                    '拡張子ありのファイルの場合
                    
                    If WorksheetFunction.CountIf(Range("E6:E" & rowCnt), fso.GetExtensionName(buf)) = 0 Then
                    
                        'まだシートに出力していない拡張子の場合
                    
                        '拡張子を出力する
                        Range("E" & rowCnt).Value = fso.GetExtensionName(buf)
                    
                        'カウンタを1つ増やす
                        rowCnt = rowCnt + 1
                        
                    End If
                
                End If
                                                                           
            End If
            
            DoEvents
            
        Loop
        
        'Streamの末尾まで処理が終わったので、streamを閉じる
        .Close

69行目のDo Until .EOSのコードは、CSVファイルを最後の行まで読み込む、という意味です。

72行目では、読み込んだCSVファイルを1行取り出して変数「buf」に格納しています。なお、読み込んだ1行はフォルダ名かファイル名のどちらかです。

今回はファイルの拡張子が欲しいので、フォルダ名は必要ありません。なので、75行目のif文でフォルダかファイルかを判定します。判定はfso.FileExistsメソッドを実行します。

ファイルであることが判定出来たら、79行目のif文のfso.GetExtensionNameメソッドの実行結果を取得します。

fso.GetExtensionNameメソッドは拡張子を取得してくれるメソッドです。

79行目のif文では何をしているのかというと、ファイルに拡張子があるのかないのかを判定しています。

今回のマクロでは、ファイルに拡張子がある場合はセルE6を拡張子を出力する最初のセルとして、2つ目以降は順にE7、E8、・・・というようにE列の下の行にどんどん出力されます。

ただし、もし同じ拡張子のファイルがある場合は、一つ目のファイルの拡張子だけをセルに出力して、2つ目以降は何も出力しないようにしたいです。

そこで2つ目以降は何も出力しないようにするのに役立つのが、96行目で使われているWorksheetFunction.CountIfメソッドです。

このworksheetFunction.CountIfメソッドは、対象のセル範囲内に同じ値が存在していれば、存在している個数を返します。(例えば、1つ存在していれば1を、2つ存在していれば2を返します。1つも存在していなければ0を返します)

ある拡張子をセルに出力した後に、もう1つ存在する同じ拡張子のファイルの拡張子を取得したとしても、すでに1つ存在していることをWorksheetFunction.CountIfメソッドが知らせてくれるので、取得した拡張子をセルに出力するのを回避することができます。

すでに拡張子が存在していたら次のファイルの拡張子は出力しないようにするのにWorksheetFunction.CountIfメソッドを活用すると便利です。

また、拡張子がないファイルの場合は1つ懸念しないといけないことがあります。

というのも、拡張子がないファイルの場合はfso.GetExtensionNameメソッドが返す拡張子の値はブランクであり、セルに貼り付ける値がないのです。

ところが、セルには空白のセルが存在するために、1つ目の拡張子がないファイルから拡張子を取得しようとする前からすでに「空白のセルがある」とfso.GetExtensionNameメソッドが認識し、WorksheetFunction.CountIfメソッドで1以上の値を返してしまいます。(すでに空白のセルはE列に無数にあるので)

なので、拡張子がないファイルの場合はWorksheetFunction.CountIfは使いません。その代わりに「noExtflg」というboolean型の変数を使います。

拡張子がないファイルが存在する場合は「noExtflg」にTrueを設定します。(拡張子がないファイルが存在しなければFalse(のまま))

1度「noExtflg」にTrueが設定されれば拡張子がないファイルが存在していることが分かるので、2つ目以降拡張子がないファイルが見つかったとしても特に何かを行うわけではなく、別のファイルの拡張子を取得する処理に移ります。

なお、「noExtflg」にTrueの場合は、129行目で拡張子無しのファイルが存在することを知らせる「拡張子無し」の文言をセルに出力するようにしています。(文言はお好きなもので)

    If noExtflg Then
    
        '拡張子がないファイルが存在している場合
 
        '拡張子がないファイルの場合はセルが空白なので、分かるように最終行に「拡張子無し」と出力する
        Range("E" & rowCnt).Value = "拡張子無し"
 
    End If

注目すべきコード④

次に見て頂きたいのは122行目です。

    '出力された拡張子の並びをソートする(ここでは昇順とする)
    Call Range("E6:E" & rowCnt).Sort(Range("E6"), Order1:=xlAscending)

この行では、Sortメソッドを実行して出力した拡張子名順に並び替えています。(xlAscendingは昇順にするよう設定する定数です)

目次へ戻る

動作確認

マクロの実行前と実行後は次の通りです。

なお、対象のフォルダ内のファイルは次の通りです。

以上のフォルダ内のファイルが、拡張子を取得している対象のファイルになります。

目次へ戻る

【注意】参照設定が必要です

一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。

参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。

Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
Windows Script Host Object Model(wshom.ocx)

なぜ必要かというと、先ほどのコードの9行目の「ADODB.Stream」というオブジェクトが「msado28.tlb」というファイルを、先ほどのコードの10行目の「WshShell」と11行目の「FileSystemObject」というオブジェクトが「wshom.ocx」というファイルを参照するからです。

    Dim st                  As ADODB.Stream             'バイナリ データまたはテキストのストリームのインスタンス用変数
    Dim wshObj              As WshShell                 'WshShellオブジェクト
    Dim fso                 As FileSystemObject         'FileSystemObjectのインスタンス用変数

この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。

ここでは「msado28.tlb」と「wshom.ocx」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「WshShell」「ADODB.Stream」「FileSystemObject」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。

目次へ戻る