Let's Talk Excel

VBA Code to list files from a folder containing a string

list-Files-in-a-folder

The code bellow asks for a folder location, and then for a string. Then it searches through the files in that folder and returns a list of those, that contain the given string.

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

At this point, it does not search for files of a certain type but you could actually include things like *.xlsx or *.pdf in a string. You can also modify the code. In line 22, the final star should be replaced by the .xlsx or any other file type you desire if you want the code to return only the files of a certain type.

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: