Search folders

Searching a folder for a file provides many features. Programs controlled by the available of data extends its functionality. This document includes the code to select a file name or part of a file name and search a folder. It displays a message block with the files selected. It also has the capability to select the folder to search. The same VBA macro code is used in Excel, Word and PowerPoint. To display a list of files in a folder try Files in folder.

SAS Fifth Dimension Macro-Search Folder

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

Sub SEARCH_FOLDER()
    SUBNAME = "SEARCH_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
         .Filename = SEARCH_STR
         If .Execute(SortBy:=msoSortByFileName, _
            SortOrder:=msoSortOrderAscending) > 0 Then
            MSG = "There were " & .FoundFiles.Count & _
                  " file(s) found." & Chr(10)
            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