4

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)?

user692942
  • 16,398
  • 7
  • 76
  • 175
Praveenks
  • 1,436
  • 9
  • 40
  • 79

2 Answers2

19

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
Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
  • 2
    +1 Simple in 6 lines yet very effective and thanks for not referencing a search engine. Just what this site is for. – glh Feb 20 '13 at 06:58
-5

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.

Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
Ned Cooney
  • 1
  • 2
  • 5
    Wih the OP asking for a VB solution *and* an approved solution already present, your answer does not add much. – Jan Doggen Sep 10 '15 at 13:50
  • 1
    This is one possible approach (provides better performance than using the `FileSystemObject` in some scenarios), but too incomplete to be useful in the context of the question. For one thing you can't just put command directly in a VBScript, and you must add a `cmd /c` to it, because `dir` and redirection are provided by `cmd.exe`. Also, the OP is looking for files with a specific extension, which is not reflected in your statment. – Ansgar Wiechers Sep 11 '15 at 21:59