List files of certain pattern using Excel VBA

Question

How to list all the files which match a certain pattern inside a user specified directory? This should work recursively inside the sub folders of the selected directory. I also need a convenient way(like tree control) of listing them.

Answer 1

As a general pointer, take a look at Application.FileSearch, recursive functions, Userforms and the 'Microsoft TreeView Control'.

FileSearch can be used to find files within a folder matching a pattern, a recursive function can call itself until all paths have been exhausted, a UserForm can host controls for displaying your data and the TreeView control can display your file system.

Bear in mind that there are pre-built functions/controls which can be used for displaying file systems, e.g. Application.GetOpenFileName, Application.GetSaveAsFileName, Microsoft WebBrowser (given a 'file://...' URL).

Answer 2

Try Windows Scripting - File System Objects. This COM object which can be created form vba has functions for listing directories etc.

You can find documentation on MSDN

Answer 3

Following URL may be of interest to you.

http://www.cpearson.com/excel/FOLDERTREEVIEW.ASPX

Answer 4

Not exactly what you asked for, but I thought I would post this here as it is related.

This is modified from the code found at http://www.cpearson.com/excel/FOLDERTREEVIEW.ASPX

This requires the reference Microsoft Scripting Runtime.

Sub ListFilePaths()

    Dim Path As String
    Dim Files As Long

    Path = "C:\Folder"

    Files = GetFilePaths(Path, "A", 1)

    MsgBox "Found " & Files - 1 & " Files"

End Sub

Function GetFilePaths(Path As String, Column As String, StartRow As Long) As Long

    Dim Folder As Scripting.Folder
    Dim SubFolder As Scripting.Folder
    Dim File As Scripting.File
    Dim FSO As Scripting.FileSystemObject
    Dim CurrentRow As Long

    Set FSO = New Scripting.FileSystemObject
    Set Folder = FSO.GetFolder(folderpath:=Path)

    CurrentRow = StartRow

    For Each File In Folder.Files
        Range(Column & CurrentRow).Value = File.Path
        CurrentRow = CurrentRow + 1
    Next File

    For Each SubFolder In Folder.SubFolders
        CurrentRow = GetFilePaths(SubFolder.Path, Column, CurrentRow)
    Next SubFolder

    GetFilePaths = CurrentRow

    Set Folder = Nothing
    Set FSO = Nothing
End Function

Answer 5

You could try a program called DDFileCatcher, which will list files in Excel. It will list the filepaths only or the folder path and the filenames underneath. You can also choose from a lot of file properties to display. To choose files of a certain pattern, search for the pattern in Windows Explorer and drag and drop the found files onto DDFileCatcher and send them to Excel.

http://www.ddfilecatcher.com/filepaths-to-microsoft-excel.htm

Answer 6

Hey,

I see that the people above me have already answered how to recurse through the file tree, This might interest you in searching for patterns in the file/file name. It is a Function for VBA that will allow regular expressions to be used.

Private Function RegularExpression(SearchString As String, Pattern As String) As String

    Dim RE As Object, REMatches As Object

    'Create the regex object' 
    Set RE = CreateObject("vbscript.regexp")
    With RE
        .MultiLine = False
        .Global = False
        .IgnoreCase = True
        'set the search pattern using parameter Pattern'
        .Pattern = Pattern 
    End With

    'Search for the pattern' 
    Set REMatches = RE.Execute(SearchString) 
    If REMatches.Count > 0 Then
        'return the first match'
        RegularExpression = REMatches(0) 
    Else
        'nothing found, return empty string'
        RegularExpression = ""
    End If

End Function

You can use this to search the file names for patterns. I suggest regular expressions home for more information on how to use Regular expressions