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