FileSystemObject VBA Question RRS feed

  • Question

  • I want to automate in Excel VBA a process that will otherwise be very tedious and time consuming. 

    In Windows Explorer I can type or paste a string into the Search box and have it return a list of all files in that folder that contain the string. I want to do the same thing in VBA. I'm pretty sure I will use FileSystemObject, but that's as far as I've gotten. 

    I have a long list of strings (Customer Numbers) and for each string I want to find out if there is at least one file in the folder that contains that string. So it's a yes or no kind of thing. I don't need to know the name of the file or anything else. However, if someone can just get me started with the code that does the searching I can add in the rest.

    Thanx for the help,


    Tuesday, December 13, 2016 1:31 PM

All replies

  • I am assuming that you only want to search one folder. Is this assumption correct? If so, you can use the following to first select the required folder and then via an InputBox you enter the string to be found in a file name and the code adds wild cards and searches the file names for an instance of the string.

    You can eliminate the InputBox and use a loop to iterate through the list of strings and output the file name result adjacent to the list of strings.

    Sub TestFileFind()
        Dim strFolder As String
        Dim strFile As String
        Dim strToFind As String
        With Application.FileDialog(msoFileDialogFolderPicker)
          .Title = "Select required path"
          .AllowMultiSelect = False
          If .Show = True Then
            strFolder = .SelectedItems(1)
            MsgBox "User cancelled." & vbLf & vbLf & _
                  "Processing terminated."
                  Exit Sub
          End If
        End With

        strFile = Application.InputBox(Prompt:="Enter string withing file name to find.", _
                                                Left:=100, Top:=100, Type:=2)
        strFile = "*" & strFile & "*"   'Add leading and trailing wild cards
        strToFind = Dir(strFolder & "\" & strFile)
        'Following is just for demo. Replace with required code if match found.
        If strToFind <> "" Then
            MsgBox strToFind & " found."
            MsgBox strFile & " not found."
        End If
    End Sub

    Regards, OssieMac

    Wednesday, December 14, 2016 12:12 AM
  • Means you want to search for files with a search mask!?

    Download and import this file into your project, a sample how to use in included in the comments:


    Wednesday, December 14, 2016 11:15 AM
  • Here is a simple recursive search routine. 

    Dim pathArr() As String
    Dim first As Boolean
    Function Recurse(sPath As String, searchFor As String)
      Dim fso As New FileSystemObject  'Add reference to Microsoft Scripting Runtime
      Dim myFolder As Folder
      Dim mySubFolder As Folder
      Dim myFile As File
      Set myFolder = fso.GetFolder(sPath)
      For Each mySubFolder In myFolder.SubFolders
        For Each myFile In mySubFolder.Files
          If InStr(myFile.Name, searchFor) > 0 Then
            Debug.Print myFile
            If first Then
              pathArr(UBound(pathArr)) = myFile
              first = False
              ReDim Preserve pathArr(UBound(pathArr) + 1)
              pathArr(UBound(pathArr)) = myFile
            End If
          End If
        Next myFile
        Recurse = Recurse(mySubFolder.Path, searchFor)
    End Function
    Sub TestR()
        ReDim pathArr(0)
        first = True
        Call Recurse("C:\myFiles", "SearchForThis")
    ' pathArr contains array of paths that meet search term
    End Sub

    Wednesday, December 14, 2016 1:49 PM
  • Thursday, December 15, 2016 8:14 PM