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