Results 1 to 14 of 14
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    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


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    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


  4. #4
    4 Star Lounger
    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

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    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


  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    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


  8. #8
    4 Star Lounger
    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.

  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Finding Subdirectories with 'dir' (XL97/WinNT4)

    Muchos Gracias!
    Beryl M


  10. #10
    Plutonium Lounger
    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 N-1 and then say N

    But, you might say, how would I count to N-1, and the answer is by following the same rule. Count to (N-1) -1 and then say N-1

    Does that help, or confuse?

    StuartR

  11. #11
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    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


  12. #12
    Plutonium Lounger
    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

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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 (N-1) 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 (N-1)!, you can calculate N! as N x (N-1)!

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •