Search for files that contain keywords in folder + subfolders

Iceshade

Board Regular
Joined
May 22, 2017
Messages
83
Hi Everyone,

Quite new to VBA and would really appreciate some help please ?

I have a macro in a workbook that when ran, scans a drive that the user selects and looks for all files that contain the keyword provided. Is there anyway to:
1. Get it to scan also the subfolders within the folder selected by the user?
2. Allow for multiple keywords to be searched at one time ?

This is the code:
VBA Code:
Sub ListFilesContainingString()
    Dim fldr As FileDialog
    Dim sItem As String
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = strPath
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With
NextCode:
    getfolder = sItem
    Set fldr = Nothing
   
    wrd = InputBox("Word:", "Insert search word")
    If wrd = "" Then
        MsgBox "???"
        Exit Sub
    End If
   
    strfile = Dir(getfolder & "\*" & wrd & "*")
    fc = 0
    Do While Len(strfile) > 0
        fc = fc + 1
        Cells(fc, 1).Value = strfile
        strfile = Dir
    Loop
   
End Sub

Have been at this for a few days now, just can't seem to change it to work.

Many many thanks in advance for any guidance here please.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,502
Do you want to scan the subfolders recursively (ie. subfolders within subfolders)?

Or do you only want to scan subfolders within your main folder?
 

Iceshade

Board Regular
Joined
May 22, 2017
Messages
83
Do you want to scan the subfolders recursively (ie. subfolders within subfolders)?

Or do you only want to scan subfolders within your main folder?
Hi Domenic, thank you for replying sorry it's taken a while to respond.

I have a folder that contains sub folders within subfolders. I would like to scan all subfolders recursively within a folder selected.

Greatly appreciate your response.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,502
The following code uses the FileSystemObject to search the subfolders recursively. And, it uses late-binding, so there's no need to reference the Microsoft Scripting Runtime library. And, of course, make any changes that you desire.

VBA Code:
Sub ListFilesContainingSearchTerms()

    Dim folderName As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select a Folder"
        .InitialFileName = Application.DefaultFilePath & "\" 'change the initial folder as desired
        If .Show = 0 Then Exit Sub 'user cancelled
        folderName = .SelectedItems(1)
    End With
   
    Dim wrd As String
    wrd = InputBox("Insert one or more search terms, separated by a semi-colom (ie. blue;red;green).", "Search Terms")
    If wrd = "" Then
        MsgBox "???", vbQuestion, "Search Terms"
        Exit Sub
    End If
   
    Cells.ClearContents 'clear contents of active worksheet for new list of files
   
    Dim searchTerms As Variant
    searchTerms = Split(Trim(wrd), ";") 'Trim() to remove any leading and trailing spaces, and Split() to split the string into an array
   
    Dim startRow As Long
    startRow = 1 'change the starting row as desired
   
    Dim fileCount As Long
    fileCount = 0
   
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
   
    ProcessFolder fso, folderName, True, searchTerms, startRow, fileCount

    MsgBox "Number of files that match the search terms: " & fileCount, vbInformation, "File Count"
 
    Set fso = Nothing
   
End Sub

Private Sub ProcessFolder(ByVal fso As Object, ByVal folderName As String, ByVal includeSubfolders As Boolean, ByRef searchTerms As Variant, ByRef rowNumber As Long, ByRef fileCount As Long)

    Dim currentFolder As Object
    Dim currentSubFolder As Object
    Dim currentFile As Object
   
    Set currentFolder = fso.GetFolder(folderName)
   
    For Each currentFile In currentFolder.Files
        If isMatchFound(currentFile.Name, searchTerms) Then
            Cells(rowNumber, "A").Value = currentFile.Name
            rowNumber = rowNumber + 1
            fileCount = fileCount + 1
        End If
    Next currentFile
   
    If includeSubfolders Then
        For Each currentSubFolder In currentFolder.subfolders
            ProcessFolder fso, currentSubFolder, True, searchTerms, rowNumber, fileCount
        Next currentSubFolder
    End If
   
End Sub

Private Function isMatchFound(ByVal fileName As String, ByVal searchTerms As Variant) As Boolean

    Dim i As Long
   
    For i = LBound(searchTerms) To UBound(searchTerms)
        If UCase(fileName) Like "*" & UCase(searchTerms(i)) & "*" Then 'case-insensitive match
            isMatchFound = True
            Exit Function
        End If
    Next i
   
    isMatchFound = False

End Function

Hope this helps!
 
Solution

Iceshade

Board Regular
Joined
May 22, 2017
Messages
83

ADVERTISEMENT

This is fantastic, can't thank you enough Dominic.

I've made a few changes, the last thing I am struggling with is adjusting this:

VBA Code:
For Each currentFile In currentFolder.Files
        If isMatchFound(currentFile.Name, searchTerms) Then
            Cells(rowNumber, "A").Value = currentFile.Name
            rowNumber = rowNumber + 1
            fileCount = fileCount + 1
        End If
    Next currentFile

How can I also get it to provide not only the current file name but also the last modified date of current file in column B ?

Greatly appreciate this again, thank you ! (I have a lot to learn!)
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,502
This is fantastic, can't thank you enough Dominic.

You're very welcome, glad I could help.

How can I also get it to provide not only the current file name but also the last modified date of current file in column B ?

You can use the DateLastModified property of the File object...

VBA Code:
    For Each currentFile In currentFolder.Files
        If isMatchFound(currentFile.Name, searchTerms) Then
            Cells(rowNumber, "A").Value = currentFile.Name
            Cells(rowNumber, "B").Value = currentFile.DateLastModified
            rowNumber = rowNumber + 1
            fileCount = fileCount + 1
        End If
    Next currentFile
 

Iceshade

Board Regular
Joined
May 22, 2017
Messages
83
You're very welcome, glad I could help.



You can use the DateLastModified property of the File object...

VBA Code:
    For Each currentFile In currentFolder.Files
        If isMatchFound(currentFile.Name, searchTerms) Then
            Cells(rowNumber, "A").Value = currentFile.Name
            Cells(rowNumber, "B").Value = currentFile.DateLastModified
            rowNumber = rowNumber + 1
            fileCount = fileCount + 1
        End If
    Next currentFile
Perfect, I've modified it further to give me the directory address. Exactly where I need this to be now.

Many thanks again Domenic !
 

Watch MrExcel Video

Forum statistics

Threads
1,129,745
Messages
5,638,114
Members
417,009
Latest member
Emily00028

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top