List Files from target directory thru all subdirs

[MWE]

I am sure that this has been discussed before but I could not find anything in the KB, nor searching through several threads.

The Application.FileSearch method works for a target directory and for a target directory and the first level of subdirectories. Is there a way to search "all the way down". I have considered building a recursive procedure, but maybe there is already something available.

[Jacob Hilderbrand]

FileSearch can search all sub directories as well.

Dim i               As Long
With Application.FileSearch
        .LookIn = ThisWorkbook.Path 'Change to root path
        .FileType = msoFileTypeAllFiles
        .SearchSubFolders = True
        .Execute
For i = 1 To .FoundFiles.Count
            MsgBox .FoundFiles(i)
        Next i
    End With

[lucas]

I have one that I think came from Marco...don't quote me on that. Be sure to set the path in the code.....must have a backslash at the end of the path.

Ken has an entry in the kb that hyperlinks files in a directory at:
http://vbaexpress.com/kb/getarticle.php?kb_id=232

[MWE]

FileSearch can search all sub directories as well.

Dim i               As Long
With Application.FileSearch
        .LookIn = ThisWorkbook.Path 'Change to root path
        .FileType = msoFileTypeAllFiles
        .SearchSubFolders = True
        .Execute
For i = 1 To .FoundFiles.Count
            MsgBox .FoundFiles(i)
        Next i
    End With

My code looks pretty similar to yours; in particular I have .SearchSubFolder = True. For me (Excel2000 and WinXP), the search only goes down one level from the .LookIn directory (effectively .LookIn and its immediate children). I want to go down through all subdirectories from the .LookIn directory down to the bottom. For the particular case I want to examine, that is 8 to 10 levels, probably 75 separate subdirectories and a thousand files.

[Jacob Hilderbrand]

Interesting. I tested with 2002 and added 4 folders, on inside each other, so 4 deep and all files were listed.

[lucas]

It works for me in 2003 Jake...how to put the results into cell A1?
this isn't working for me:

Range("A1").Value = .FoundFiles(i)

[MWE]

I have one that I think came from Marco...don't quote me on that. Be sure to set the path in the code.....must have a backslash at the end of the path.

Ken has an entry in the kb that hyperlinks files in a directory at:
http://vbaexpress.com/kb/getarticle.php?kb_id=232

thanks, the file you attached and associated code does just what I want (or will with a few tweaks).

Who/what is Marco?

[lucas]

Marco is Xcav8tor One of the Approvers I think that was one of several FSO kb entries of his.

Mark, post back here or add this to the kb when you get it to listing files please.

[Jacob Hilderbrand]

Range("A1").Value = .FoundFiles(i)

This is somewhat correct, it will put the value in A1, but then for the next i it will replace the value in A1 again.

Try this.

Range("A" & i).Value = .FoundFiles(i)

[MWE]

Interesting. I tested with 2002 and added 4 folders, on inside each other, so 4 deep and all files were listed.

I tried several different "starting points" and only got the files at the "starting level" plus the files in any children of the top level. Maybe it is a difference between 2000 and 20002.

UPDATE: well it appears to be a difference in Office2000 before a reboot and after a reboot. The method now goes down "to the bottom".

It is scary, but many recent problems with VBA not doing what it should have been solved with a reboot. Maybe I should jut kick the computer case a little harder each time and would not have to reboot as often ...

[lucas]

Try this.

Range("A" & i).Value = .FoundFiles(i)

That works Jake. Thanks.

[gibbo1715]

Have a look at this thread where we used the same method and resulted it in a treeview on a userform (Thanks to ALi for this one)

http://www.vbaexpress.com/forum/showthread.php?t=4943

Gibbo