Example 8: Use the concept of the above two examples and write a VBA code that enlists all the files inside a current location and its subfolder.

For this example you can use the following code:

view plaincopy to clipboardprint?

1. Sub Retrieve_File_listing()  
2. Worksheets(1).Cells(2, 1).Activate  
3. Call Enlist_Directories("C:\Users\Ankit\Desktop\ExcelTrick\ ", 1)  
4. End Sub  
5. Public Sub Enlist_Directories(strPath As String, lngSheet As Long)  
6. Dim strFldrList() As String  
7. Dim lngArrayMax, x As Long  
8. lngArrayMax = 0  
9. strFn = Dir(strPath & "*.*", 23)  
10. While strFn <> ""  
11.   If strFn <> "." And strFn <> ".." Then  
12.     If (GetAttr(strPath & strFn) And vbDirectory) = vbDirectory Then  
13.       lngArrayMax = lngArrayMax + 1  
14.       ReDim Preserve strFldrList(lngArrayMax)  
15.       strFldrList(lngArrayMax) = strPath & strFn & "\"  
16.     Else  
17.     ActiveCell.Value = strPath & strFn  
18.     Worksheets(lngSheet).Cells(ActiveCell.Row + 1, 1).Activate  
19.     End If  
20.   End If  
21.   strFn = Dir()  
22. Wend  
23. If lngArrayMax <> 0 Then  
24.   For x = 1 To lngArrayMax  
25.     Call Enlist_Directories(strFldrList(x), lngSheet)  
26.   Next  
27. End If  
28. End Sub  

Explanation: This code first iterates through all the folders present inside a location and stores them in an array. Later it recursively calls the ‘Enlist_Directories’ function to retrieve the file names.

So, this was all about VBA DIR Function in Excel.