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.
Sub SubDirList() 'Excel VBA process to loop through directories listing files
Dim sfil(1 To 1) As String
sfil(1)="C:\Users\HYMC\Roaming\" 'Change this path to suit.
For Each sname In sfil()
Private Sub SelectFiles(sPath) 'Excel VBA to show file path name.
Dim file As Object
Dim oFSO As Object
Dim i As Integer
For Each fldr In Folder.SubFolders
For Each file In Folder.Files
i=i + 1
Set oFSO =Nothing
To successfully run the above procedure ensure you have the path correct for your computer.
This is the key line to changes
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.