There are a few ways you can loop through files in a folder. You can do it only for a chosen folder without entering the folders inside it, or you can do it recursively and then save them to a text file.
I created a few examples, where you can see different examples and choose the one you need.
This is the treeview of directories and files.
Loop through files in a folder
This example is the most simple one. It will list all files and folders that are located in the chosen directory.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
Sub LoopThroughFiles() Dim myFile As String, rng As Range, cellValue As Variant, i As Integer, j As Integer Dim fileName As Variant fileName = Dir("D:\mydir\") myFile = "D:\temp\list.txt" Open myFile For Output As #1 While fileName <> "" If Left(fileName, 1) <> "." Then Write #1, fileName End If fileName = Dir Wend Close #1 End Sub |
This code will return the list of files in D:\mydir\.
“pic1.jpg”
“pic2.jpg”
“pic3.jpg”
“text.txt”
Loop through a folder and display files and directories
If you want to list also directories, you have to add an additional parameter to the Dir function, namely vbDirectory.
Change the following line fileName = Dir(“D:\mydir\”) to fileName = Dir(“D:\mydir\”, vbDirectory)
Now, the code will return the following list.
“data”
“pic1.jpg”
“pic2.jpg”
“pic3.jpg”
“text”
“text.txt”
Loop through files recursively
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
Sub LoopAllSubFolders(ByVal folderPath As String) Dim fileName As String Dim fullFilePath As String Dim numFolders As Long Dim folders() As String Dim i As Long If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\" fileName = Dir(folderPath & "*.*", vbDirectory) While Len(fileName) <> 0 If Left(fileName, 1) <> "." Then fullFilePath = folderPath & fileName If (GetAttr(fullFilePath) And vbDirectory) = vbDirectory Then ReDim Preserve folders(0 To numFolders) As String folders(numFolders) = fullFilePath numFolders = numFolders + 1 'print directories Write #1, fileName Else 'print files Write #1, fileName End If End If fileName = Dir() Wend For i = 0 To numFolders - 1 LoopAllSubFolders folders(i) Next i End Sub |
|
1 2 3 4 5 6 7 |
Sub loopAllSubFolderSelectStartDirector() Dim myFile As String myFile = "D:\temp\list2.txt" Open myFile For Output As #1 Call LoopAllSubFolders("D:\mydir\") Close #1 End Sub |