Results 1 to 14 of 14

20031202, 10:16 #1
 Join Date
 Jun 2001
 Location
 Basingstoke, Hampshire, United Kingdom
 Posts
 1,845
 Thanks
 11
 Thanked 0 Times in 0 Posts
Finding Subdirectories with 'dir' (XL97/WinNT4)
I have a spreadsheet which finds all files in a directory and returns the LastModified or LastAccessedDate but I would like to modify it to find everything in any subdirectories of the directory as well, and I can't find a way to do this using dir (as the function currently does).
Can anyone suggest a way I could do this? This is the function, if it helps:
Function GetFileList(filespec As String) As Variant
' Returns an array of filenames that match FileSpec
' If no matching files are found, it returns False
Dim FileArray() As Variant
Dim FileCount As Integer
Dim FileName As String
On Error GoTo NoFilesFound
FileCount = 0
FileName = Dir(filespec)
If FileName = "" Then GoTo NoFilesFound
' Loop until no more matching files are found
Do While FileName <> ""
FileCount = FileCount + 1
ReDim Preserve FileArray(1 To FileCount)
FileArray(FileCount) = FileName
FileName = Dir()
Loop
GetFileList = FileArray
Exit Function
' Error handler
NoFilesFound:
GetFileList = False
End Function
I would be very grateful for any suggestions!Beryl M

20031202, 11:14 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Finding Subdirectories with 'dir' (XL97/WinNT4)
Although you can use Dir(FolderName, vbDirectory) to find subfolders, Dir won't work in nested loops  it gets confused.
If you want to search in subfolders, you can use the Application.FileSearch object, with the SearchSubfolders property set to True.
Or you can use the Scripting.FileSystemObject: see HOW TO: Recursively Search Directories by Using FileSystemObject.

20031204, 11:33 #3
 Join Date
 Jun 2001
 Location
 Basingstoke, Hampshire, United Kingdom
 Posts
 1,845
 Thanks
 11
 Thanked 0 Times in 0 Posts
Re: Finding Subdirectories with 'dir' (XL97/WinNT4)
Thanks for the info, Hans  I'll have to study that!
Beryl M

20031205, 19:08 #4
 Join Date
 Aug 2002
 Location
 Dallas, Texas, USA
 Posts
 594
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Finding Subdirectories with 'dir' (XL97/WinNT4)
As HansV pointed out, you can't directly use Dir in a nested loop. This is because Dir 'resets' itself when you give it a new parameter, so called Dir() without parameters in previous nestings will not work correctly.
You don't have to use the FileSystemObject, to get around this, though. Instead, just capture everything from the Dir() statement before you start recursing....
For example:
Function RecurseFilesAndFolders(strPath)
Dim SubFolders As Collection
Dim strTemp as string
dim varTemp
Set SubFolders=New Collection
strTemp=Dir(strPath,vbDirectory)
Do Until strTemp=""
If strTemp<>"." and strTemp<>".." Then
If GetAttr(strPath & strTemp) And vbDirectory Then
SubFolders.Add strTemp
Else
'Do what you want, it's a file....
End if
End if
strTemp=Dir()
Loop
For Each varTemp in SubFolders
RecurseFilesAndFolders(strPath & varTemp & "")
Next
End Function

20031208, 10:51 #5
 Join Date
 Jun 2001
 Location
 Basingstoke, Hampshire, United Kingdom
 Posts
 1,845
 Thanks
 11
 Thanked 0 Times in 0 Posts
Re: Finding Subdirectories with 'dir' (XL97/WinNT4)
Thanks, Drew, that might be the way I'll go with this one (next time I get a chance to look at it!), but just a quick query  will this only look at one level of subdirectories or will it drill down to all those that exist?
At a quick glance it looks like it'll be just one level (which I'm fairly certain will be fine for what I want) but I just wondered.
I'll let you know how I get on in due course!Beryl M

20031208, 10:59 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Finding Subdirectories with 'dir' (XL97/WinNT4)
Drew's function is recursive: it creates a collection of subfolders of the strPath passed to it, then calls itself for each of these. So it will drill down to the deepest subfolder.

20031208, 11:51 #7
 Join Date
 Jun 2001
 Location
 Basingstoke, Hampshire, United Kingdom
 Posts
 1,845
 Thanks
 11
 Thanked 0 Times in 0 Posts
Re: Finding Subdirectories with 'dir' (XL97/WinNT4)
Ah  which just goes to show that I don't know what 'Recursive' means. Excuse me while I go and look it up!
<img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>Beryl M

20031208, 21:19 #8
 Join Date
 Aug 2002
 Location
 Dallas, Texas, USA
 Posts
 594
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Finding Subdirectories with 'dir' (XL97/WinNT4)
Recursive means that a function will keep calling itself until everything is done.
Technically, a For Each statement is recursive, because it runs through everything, but in the more commonly accepted terminology, recursion is when a function is called for every 'node' in a tree.

20031209, 11:54 #9
 Join Date
 Jun 2001
 Location
 Basingstoke, Hampshire, United Kingdom
 Posts
 1,845
 Thanks
 11
 Thanked 0 Times in 0 Posts
Re: Finding Subdirectories with 'dir' (XL97/WinNT4)
Muchos Gracias!
Beryl M

20031209, 14:51 #10
 Join Date
 Nov 2001
 Posts
 10,550
 Thanks
 0
 Thanked 7 Times in 7 Posts
Re: Finding Subdirectories with 'dir' (XL97/WinNT4)
A recursive function is (usually) one that calls itself.
A simple example is the definition of how to count to a number N.
To count to N, if N is 1 then just say 1, otherwise count to N1 and then say N
But, you might say, how would I count to N1, and the answer is by following the same rule. Count to (N1) 1 and then say N1
Does that help, or confuse?
StuartR

20031210, 12:03 #11
 Join Date
 Jun 2001
 Location
 Basingstoke, Hampshire, United Kingdom
 Posts
 1,845
 Thanks
 11
 Thanked 0 Times in 0 Posts
Re: Finding Subdirectories with 'dir' (XL97/WinNT4)
Umm ... well, I got the first sentence OK, but after that ... <img src=/S/headthrob.gif border=0 alt=headthrob width=15 height=15>
Beryl M

20031210, 20:18 #12
 Join Date
 Nov 2001
 Posts
 10,550
 Thanks
 0
 Thanked 7 Times in 7 Posts
Re: Finding Subdirectories with 'dir' (XL97/WinNT4)
Hans,
Your English is so very much better than my Dutch will ever be, that I feel a bit churlish pointing out that the word is not faculty, it is factorial.
StuartR

20031210, 20:45 #13
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Finding Subdirectories with 'dir' (XL97/WinNT4)
Oh dear, you're correct (of course). The moment I saw your post, I remembered. Strangely enough, before I posted my reply to Beryl this afternoon, I did some searching in Google, and found a web page mentioning faculty in this context, but I see now that it was on a French site (facult

20031210, 20:46 #14
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Finding Subdirectories with 'dir' (XL97/WinNT4)
Added: as StuartR pointed out, I should have used factorial instead of faculty.
OK, another example.
I'll start with an introduction. Four children Adrian, Beryl, Charlotte and Dave hold a race. One of them comes in first, another one second, etc. How many different results are possible?
Examples are
1. Beryl, 2. Dave, 3. Adrian, 4. Charlotte
1. Charlotte, 2. Dave, 3. Beryl, 4. Adrian
etc.
You count them as follows:
There are 4 possibilities for the #1: each of the four children can win.
Once the winner is known, there are 3 possibilities for #2: each of the remaining three can come in second.
Once #1 and #2 are known, there are 2 possibilities for the third place.
And then, you also know who comes in fourth place: there is only one remaining child.
So there are 4 x 3 x 2 x 1 = 24 possibilities.
With 5 children, there would be 5 x 4 x 3 x 2 x 1 = 120 possibilities, and with 7 children, there would be 7 x 6 x 5 x 4 x 3 x 2 x 1 = 5040 possibilities, etc.
This kind of calculation occurs quite often in statistics and probability, so mathematicians have given it a name and notation: 4 x 3 x 2 x 1 is called the faculty of 4, and it is written 4!
So 6! = 6 x 5 x 4 x 3 x 2 x 1 = 720, and in general, N! = N x (N1) x ... x 1
One way to compute the faculty of a number is to use a For ... Next loop:
Function Faculty(N)
Dim i, ReturnVal
ReturnVal = 1
For i = 2 To N
ReturnVal = ReturnVal * i
Next i
Faculty = ReturnVal
End Function
But there is also another way. If you already know that 5! = 120, you don't have to calculate 6! as 6 x 5 x 4 x 3 x 2 x 1, you can say 6! = 6 x 5!, so it is 6 x 120 = 720. In general, if you already know (N1)!, you can calculate N! as N x (N1)!
This leads to the following function:
Function Faculty(N)
If N = 1 Then
Faculty = 1
Else
Faculty = N * Faculty(N  1)
End If
End Function
This function is recursive: it calls itself repeatedly, until it arrives at 1. In this particular example, it is not very efficient, but there are situations in which recursive functions are the best way to solve a problem.