PDA

View Full Version : [SOLVED] List Files from target directory thru all subdirs



MWE
09-27-2005, 05:40 PM
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
09-27-2005, 06:51 PM
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
09-27-2005, 06:55 PM
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
09-27-2005, 07:04 PM
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
09-27-2005, 07:14 PM
Interesting. I tested with 2002 and added 4 folders, on inside each other, so 4 deep and all files were listed.

lucas
09-27-2005, 07:31 PM
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
09-27-2005, 07:35 PM
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
09-27-2005, 07:45 PM
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
09-27-2005, 08:28 PM
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
09-27-2005, 09:12 PM
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
09-27-2005, 09:15 PM
Try this.


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



That works Jake. Thanks.

gibbo1715
09-27-2005, 11:22 PM
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