あまり知られていないDir関数の特徴｜Excel VBA

ツイート

Dir関数は、ファイルが存在するかどうかを判定できる関数です。
引数に指定したファイルが存在すると、Dir関数はファイル名を返します。

Sub Sample1()
    Dim buf As String
    buf = Dir("C:\Sample\Book1.xls")
    Debug.Print buf
End Sub

引数に指定するファイル名は、* や ? のワイルドカードを指定できます。
また、Dir関数は引数を省略すると、直前に指定したファイル名が指定されたものとして、まだ返していないファイル名を順に返します。
この特徴を利用すると、あるフォルダに存在するすべてのファイル名を取得することができます。

次のコードは、C:\Sampleフォルダに存在するすべての「*.xls」を表示します。

Sub Sample2()
    Dim buf As String
    buf = Dir("C:\Sample\*.xls")

    Do While Len(buf) > 0
        Debug.Print buf
        buf = Dir()
    Loop
End Sub

さて、C:\Sampleフォルダに、次の4つのブックが存在するとします。

Book1.xls
Book2.xlsx
Book3.xls
Book4.xlsm

Book1.xlsとBook3.xlsは、Excel 2003以前のバージョンで保存したブックです。
Book2.xlsxは、Excel 2007以降のバージョンで保存した新しい形式のブック、Book4.xlsmはマクロ有効ブックです。

この状態で上記のSample2を実行すると、結果はどうなるでしょう。
「*.xls」に該当するブックは、Book1.xlsとBook3.xlsの2つなので、画面には2つのファイル名が表示されるはずです。
しかし、実行すると

Book1.xls
Book2.xlsx
Book3.xls
Book4.xlsm

の4つが表示されます。
「*.xls」の指定で「Book2.xlsx」「Book4.xlsm」も該当すると判断されてしまいました。

これは、VBAやDir関数の問題ではなく、Windowsの仕様です。
Dir関数で3文字の拡張子を指定した場合、その3文字「で始まる」拡張子が該当すると判断されます。
今回のような「xls」と「xlsx」「xlsm」のほかにも、HTMLファイルで使われる「htm」と「html」なども同じように扱われます。

では、フォルダに「xls」「xlsx」「xlsm」の複数のブックが存在するとき、「xls」のファイル名だけを取得するには、どうしたらいいでしょう。

それには、Dir関数が返すファイル名の拡張子が「xls」かどうかを判定します。
拡張子を判定する方法にはいろいろありますが、次のサンプルのように、Like演算子を使ってファイル名が「.xls」で終わっているかどうかを判定するのが最も簡単でしょう。

Sub Sample3()
    Dim buf As String
    buf = Dir("C:\Sample\*.xls")
    
    Do While Len(buf) > 0
        If LCase(buf) Like "*.xls" Then
            Debug.Print buf
        End If
        buf = Dir()
    Loop
End Sub

ファイルの拡張子を取得するには、ほかにも方法があります。
FileSystemObjectには拡張子を返すGetExtensionNameメソッドがあります。
FileSystemObjectにはファイルやフォルダに関するさまざまなオブジェクトが用意されているので、ファイル名以外の情報も取得したい場合に使うと良いでしょう。

拡張子の判定をFileSystemObjectで行うのなら、いっそファイルの一覧もFileSystemObjectで取得した方が簡単ですね。
もはや本記事のタイトルのDir関数は登場しませんが、拡張子「xls」のファイル名と、その作成日時を表示するサンプルをご紹介します。

Sub Sample4()
    Dim f As Object

    With CreateObject("Scripting.FileSystemObject")
        For Each f In .GetFolder("C:\Sample").Files
            If LCase(.GetExtensionName(f)) = "xls" Then
                Debug.Print f.Name & " --- " & _
                    Format(f.DateCreated, "yyyy/mm/dd hh:mm")
            End If
        Next f
    End With
End Sub

Excel 2007以降と、旧バージョンのExcelが使われている環境では、いろいろな拡張子のブックが混在することでしょう。
Dir関数で「xls」のファイルだけを取得するときには注意してください。

●補足●

FileSystemObjectのGetExtensionNameメソッドは、ファイルの拡張子を返します。
ただし「GetExtensionName("C:\Sample\" & buf)) = "xls"」の判定では「XLS」という大文字の拡張子が除外されてしまうため、Sample4ではGetExtensionNameメソッドの戻り値をLCase関数で小文字に変換しています。
なお、Dir関数は「Dir(*.xls)」という指定で、大文字の「XLS」もヒットします。