Two Methods for VBA to traverse all folders (filesearch and FileSystemObject)

Http://excelpx.com/Dispbbs.asp? Boardid = 177 & id = 132522

 

Two methods are commonly used to traverse all files in folders and subfolders. One is to use the filesercth object of VBA, and the other is to use FileSystemObject (Windows file management tool) and recursive methods. Blue pairCodeAnnotations are provided, hoping to help you

Method 1: Use a filesearch object

Sub MySearch ()
Dim FS, I, arr (1-10000)
Set FS = application. filesearch 'Set a search object
With FS
. Lookin = thisworkbook. Path &"/"' Set search path
. Filename = "*. xls "' Name and type of the file to be searched
. Searchsubfolders = true' Search for subfolders?
If. Execute> 0 then' If the file cannot be found
Msgbox "there were" &. foundfiles. Count &_
"File (s) found ." 'The display file cannot be found.
For I = 1 to. foundfiles. Count' Store all searched files to the array through Loops
Arr (I) =. foundfiles (I)
Next I
Sheets (1). Range ("A1"). Resize (. foundfiles. Count) = application. Transpose (ARR )'' Put the path and file name in the array in the cell
Else
Msgbox "there were no files found ."
End if
End
End sub

Method 2: reference the FileSystemObject object

Note:To use the FileSystemObject object, you must first reference the method. For details, see VBE -- tool -- reference -- locate the miscrosoft runtime project and select

Code and comments:

Dim arrfiles (1-10000 )'Create an array space to store the file name
Dim cntfiles % 'number of files

Public sub listallfiles ()
dim strpath $ 'declares the file path
dim I %
'set FSO = Createobject ("scripting. fileSystemObject ")
dim FSO as new FileSystemObject, FD as folder' Create a FileSystemObject object and a folder object

Strpath = thisworkbook. Path &"/"'"Set the folder directory to be traversed
Cntfiles = 0
Set FD = FSO. getfolder (strpath )'Set FD folder objects
Searchfiles FD'Call subaccountProgramSearch for files
Sheets (1). Range ("A1"). Resize (cntfiles) = application. Transpose (arrfiles)'Put the path and file name in the array in the cell

End sub

 

Sub searchfiles (byval FD as folder)

Dim FL as file
Dim SFD as folder
For each fl in FD. Files'Put files one by one in an array through Loops
Cntfiles = cntfiles + 1
Arrfiles (cntfiles) = fL. Path
Next fl

If FD. subfolders. Count = 0 Then exit sub'SubfoldersReturns the folders set consisting of all subfolders in the specified folder (including hidden folders and system folders ).

For each SFD in FD. subfolders'Perform cyclic search in the folders set
Searchfiles sfd'Use recursive methods to find the next folder
Next

End sub