Recursively access subfolder files inside a folder

Question

I have written this code to access Excel files inside a folder:

strPath="C:\Test\"

Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFso.GetFolder (strPath)
Set objExcel= CreateObject("Excel.Application")
objExcel.Visible= False

For Each objFile In objFolder.Files
 If objFso.GetExtensionName(objFile.Path) = "xls" Then

Now I have to create some subfolders and put some .xls files in those.

What modification should I do in my code for searching files in main folder and all other subfolders (there are also some folders inside subfolders)?

Answer 1

This is actually a well-solved problem. Recursion means that you create a self-referencing function (a function that calls itself). In your case you'd make the function call itself for each subfolder of the current folder.

TraverseFolders objFso.GetFolder(strPath)

Function TraverseFolders(fldr)
  ' do stuff with the files in fldr here, or ...

  For Each sf In fldr.SubFolders
    TraverseFolders sf  '<- recurse here
  Next

  ' ... do stuff with the files in fldr here.
End Function

Answer 2

Run this at the start of your script, it will list all the files in all folders:

dir /S/B > AllFoldersAndFiles.txt

then loop through the files list. This works for me.

Recursive vb's a bit tricky.