Help us understand the problem. What is going on with this article?

VBAで特定のフォルダ以下のファイルを全て取得する関数(正規表現によるフィルター対応)

.NETのDirectory.GetFilesとか Directory.EnumerateFilesにあたる機能がほしかったのですが無かったみたいなので忘却録のような形で。
FileSystemObjectのFileオブジェクトを取得するため、フルパス、名前だけ、更新日時など簡単に出力時に切り替えられるかと。

以下を任意モジュールに以下をコピーして下さい。

Private Filedic As Object 'Dictionary , 参照:Microsoft Scripting Runtime
Private Reg As Object 'RegExp , 参照:Microsoft VBScript Regular Expressions 5.5

Public Function GetFilesForAllDirectories(currentFolder As String, Optional regPattern As String = ".*", Optional regIgnoreCase As Boolean = False) As Variant

    Set Filedic = CreateObject("scripting.dictionary") 'New Dictionary(Microsoft Scripting Runtimeが有効な場合)
    Set Reg = CreateObject("VBScript.RegExp") 'New RegExp(Microsoft VBScript Regular Expressions 5.5が有効な場合)
    Reg.Pattern = regPattern
    Reg.IgnoreCase = regIgnoreCase 'デフォルトはTrueです。True=大文字小文字を区別しない、False=区別する

    Call GetFilePath(currentFolder)
    GetFilesForAllDirectories = Filedic.Keys

End Function

Private Sub GetFilePath(folderPath As String) as Variant

    Dim fso As Object: Set fso = CreateObject("scripting.Filesystemobject") ' New FileSystemObject (Microsoft Scripting Runtimeが有効な場合)
    Dim subfolder As Object 'Folder(Microsoft Scripting Runtimeが有効な場合)

    For Each subfolder In fso.GetFolder(folderPath).SubFolders
        Call GetFilePath(subfolder.Path)
    Next

    Dim file As Object 'File (Microsoft Scripting Runtimeが有効な場合)
    For Each file In fso.GetFolder(folderPath).Files
        If Reg.test(file.Path) Then
            Filedic.Add file, 0
        End If
    Next

End Sub

使い方
regPatternを指定しなかった場合は全てのファイルを取得します。
以下では、xls/xlsx/xlsm/xlsmなどのexcelファイルを取得しています。

Sub PrintGetFilesTest()

    Dim currentFolder As String: currentFolder = "C:\hogehoge\"
    Dim regPattern As String: regPattern = ".*\.xls.?$"

    Dim files:files = GetFilesForAllDirectories(currentFolder, regPattern, True)
    Dim file as variant 'as Fileとしたいですが関数側で互換性のためにVariant指定しているため、Fileとしたい場合は Microsoft Scripting Runtimeを有効とした上で関数側を書き換えて下さい。
    For Each file In files
        Debug.Print file.Path 
        'ファイルオブジェクトを取得しているため
        'file.Pathだとフルパスを、file.Nameだとファイル名だけを、file.DateLastModifiedだと更新日時を取得できます。
        'ex .Attributes→拡張子 .DateCreated→ファイル作成日 .DateLastAccessed→最終アクセス日 .Size→ファイルサイズ
    Next
End Sub
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away