List all of the Files in Sub Directories

The following Excel VBA procedure will loop through all the files in the directory and its sub directories and list these files name and path in Column A.  As you can see from the code it is a lot more complex than just opening the files in a single directory.  The task is just a demonstration, you may wish to perform more complex tasks once the files have been opened.  This will be discussed later.  Both procedures below need to be run together.

Option Explicit

Sub SubDirList() 'Excel VBA process to loop through directories listing files
Dim sname As Variant
Dim sfil(1 To 1) As String
sfil(1)="C:\Users\HYMC\Roaming\" 'Change this path to suit.

For Each sname In sfil()
SelectFiles sname
Next sname

End Sub

Private Sub SelectFiles(sPath) 'Excel VBA to show file path name.
Dim Folder As Object
Dim file As Object
Dim fldr
Dim oFSO As Object
Dim i As Integer

Set oFSO=Createobject("Scripting.FileSystemObject")
Set Folder=oFSO.GetFolder(sPath)
i=1
For Each fldr In Folder.SubFolders
SelectFiles fldr.Path
Next fldr

For Each file In Folder.Files
Range("A6536").End(xlUp)(2).Value=file
i=i + 1
Next file

Set oFSO =Nothing
End Sub


To successfully run the above procedure ensure you have the path correct for your computer.

This is the key line to changes

"C:\Users\HYMC\Roaming\"

Get this correct and the Excel VBA coding should run smoothly listing all files from the root and sub directories in Column A of the worksheet the code is run from.