Search Sub folders

VBA has the capablility to search a folder for sub-folders, allowing any file in any sub-folder to be accessed. This document includes the code to select a file name or part of a file name and search all sub-folders. It displays a message block with the files selected. It also has the capability to select the folder to start the search. The same VBA macro code is used in Excel, Word and PowerPoint. To display a list sub-folders try Subfolders.

SAS Fifth Dimension Macro-Search Sub Folders

Review the following code, copy it to Excel, Word or PowerPoing and execute it.

Sub SEARCH_SUB_FOLDERS()
    SUBNAME = "SEARCH_SUB_FOLDER"
    If InStr(Application.Name, "Excel") _ 
    Then EXAMPLE = "*.xls"
    If InStr(Application.Name, "Word") _ 
    Then EXAMPLE = "*.doc"
    If InStr(Application.Name, "PowerPoint")  _ 
    Then EXAMPLE = "*.ppt"
    DIRECTORY = InputBox("Select string and folder" & _
                Chr(10) & "Delimit with comma" & Chr(10) & _
    "Current folder is default", SUBNAME, EXAMPLE & "," & _
     CurDir())
    If DIRECTORY = "" Then GoTo ENDIT
    If InStr(DIRECTORY, ",") Then
       SEARCH_STR = _ 
       Mid(DIRECTORY, 1, InStr(DIRECTORY, ",") - 1)
       DIRECTORY = _
       Mid(DIRECTORY, InStr(DIRECTORY, ",") + 1)
    Else
       SEARCH_STR = DIRECTORY
       DIRECTORY = CurDir()
    End If
    Set fs = Application.FileSearch
    With fs
        .LookIn = DIRECTORY
        .SearchSubFolders = True
        .Filename = SEARCH_STR
        If .Execute() > 0 Then
           MSG = "There were " & .FoundFiles.Count & _
            " file(s) found."
           For II = 1 To .FoundFiles.Count
               MSG = MSG & .FoundFiles(II) & Chr(10)
           Next
           MsgBox MSG
        Else
           MsgBox "There were no files found."
        End If
    End With
ENDIT:
End Sub