Loop Through All Subfolders Using VBA

Question

I'm looking for a VBA script that will loop through all subfolders of a specified folder. When I say all subfolders, I mean each folder inside the specified folder, and each folder inside of that, and each folder inside of that...in theory there could be infinite nested subfolders, but in actuality it will probably not go above 3 or 4. I'm using the VBA Scripting Runtime objects, so that once I loop into the folder I can check properties of some files (but I know how to do that part).

Thank you for your help!

This question is different from the listed "similar" questions in the previous questions contained known directories, whereas the need here was to find known and unknown directories. Also needed multiple layers of subdirectories. You guys really should just read the question before you fire off "duplicate".

Plenty of examples here on SO alone - have you tried searching? — Tim Williams, Mar 25 '14 at 20:17
possible duplicate of [Cycle through sub-folders and files in a user-specified root directory](http://stackoverflow.com/questions/14245712/cycle-through-sub-folders-and-files-in-a-user-specified-root-directory) and [get list of subdirs in vba](http://stackoverflow.com/questions/9827715/get-list-of-subdirs-in-vba?lq=1) — Dmitry Pavliv, Mar 25 '14 at 20:17
It is similar to what simoco posted, however in that example there appears to be a list whereas here the subfolders are unknown at the outset - the script would need to find them. Thank you. — Jake, Mar 25 '14 at 20:19
And yes I have tried searching, to no avail. Thank you for the suggestion. — Jake, Mar 25 '14 at 20:20

score 121 · Accepted Answer · edited Aug 09 '19 at 14:01

121

Just a simple folder drill down.

sub sample()
    Dim FileSystem As Object
    Dim HostFolder As String

    HostFolder = "C:\"

    Set FileSystem = CreateObject("Scripting.FileSystemObject")
    DoFolder FileSystem.GetFolder(HostFolder)
end  sub

Sub DoFolder(Folder)
    Dim SubFolder
    For Each SubFolder In Folder.SubFolders
        DoFolder SubFolder
    Next
    Dim File
    For Each File In Folder.Files
        ' Operate on each file
    Next
End Sub

edited Aug 09 '19 at 14:01

iDevlop

24,841
11
90
149

answered Mar 25 '14 at 20:21

Rich

4,134
3
26
45

4

Thank you, I figured it was something simple! – Jake Mar 25 '14 at 20:26
6

No problem, all programmers keep a copy of this subfolder iterations at one point or another, it was mostly copy and paste. Don't forget to mark an answer my friend. – Rich Mar 25 '14 at 20:27
3

non-recursive method added below for completeness ;) – Cor_Blimey Mar 25 '14 at 20:58
2

Have you forgotten to define something? not working for me :( – mojo3340 Dec 13 '16 at 11:55
this is fantastic +1 – jellz77 Oct 25 '17 at 21:21
1

@Rich You might want to consider wrapping the sample use/calling-code within a working sub. Newer coders seem to be getting confused on how to deal with it.... – Mistella Oct 29 '18 at 18:38
3

I guess you can't get away with just having SubFolder as variant. It needs to be **Dim SubFolder as Object** – hypers Nov 29 '18 at 09:24
As written, files in the deepest sub-folder will be processed first. The order of the two loops can be reversed, so that files in the top folder will be processed first. `For Each File...` first, followed by `For Each SubFolder...` – johny why Jul 19 '21 at 23:50
Personally, i wouldn't ask the calling fx to create the FSO or get the folder object. That should be left to the DoFolder proc. The caller should only have to pass the target directory as a string. – johny why Jul 20 '21 at 06:07
i would call DoFolder something more meaningful, like "Get_Files". DoFolder doesn't tell us what we're "doing" to the folder. – johny why Jul 20 '21 at 06:07

Cor_Blimey · Answer 2 · 2016-05-19T19:30:02.140

51

And to complement Rich's recursive answer, a non-recursive method.

Public Sub NonRecursiveMethod()
    Dim fso, oFolder, oSubfolder, oFile, queue As Collection

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set queue = New Collection
    queue.Add fso.GetFolder("your folder path variable") 'obviously replace

    Do While queue.Count > 0
        Set oFolder = queue(1)
        queue.Remove 1 'dequeue
        '...insert any folder processing code here...
        For Each oSubfolder In oFolder.SubFolders
            queue.Add oSubfolder 'enqueue
        Next oSubfolder
        For Each oFile In oFolder.Files
            '...insert any file processing code here...
        Next oFile
    Loop

End Sub

You can use a queue for FIFO behaviour (shown above), or you can use a stack for LIFO behaviour which would process in the same order as a recursive approach (replace Set oFolder = queue(1) with Set oFolder = queue(queue.Count) and replace queue.Remove(1) with queue.Remove(queue.Count), and probably rename the variable...)

edited May 19 '16 at 19:30

answered Mar 25 '14 at 20:58

Cor_Blimey

3,260
1
14
20

2

+1 This is seriously badass. I need to read more on collections. – Rich Mar 25 '14 at 20:59
I'm curious now. If there are 3 subfolders in the `.Subfolders` object, and it performs the `queue.Add oSubfolder` three times. Won't it bypass the other 2 during the next loop call and `Set oFolder = queue(1)` is activated? – Rich Mar 25 '14 at 21:03
4

@Rich it will, but the other 2 have been stored in the collection which we then process in the next 2 loops (don't forget `queue.Remove 1` removes the first folder, so the second folder is now the first member of the collection and so is retrieved on the next call to queue(1). A stack approach would go the opposite way and always operate on the last folder added (replace queue(1) with queue(queue.Count) and queue.Remove 1 with queue.Remove queue.Count). – Cor_Blimey Mar 25 '14 at 21:06
Makes sense. Thanks dude. I'll definitely be using this feature more often. – Rich Mar 25 '14 at 21:09
3

Collections are super useful in VBA as they are basically our only "list" type of object (keyed dictionaries can be used from Scripting.Runtime as well). You can use them easily add results as you are processing something, then worry about getting into an array at the end (saves on array copying overhead that using Redim preserve would have each loop). You use them to find uniques, to map values, to do quick lookups on large amounts of data, and so many other things. – Cor_Blimey Mar 25 '14 at 21:11
Yeah, and ASPX has the entire global Session() collection built into it, which is pretty epic, and I've used dictionaries a-lot for vbs, not as much vba. – Rich Mar 25 '14 at 21:13
4

I'd like to point out that if the order of processing is important for you, this method will not give the same order as the recursive method. Here the folders processing order will be like this: `Root`, `Root\Sub1`, `Root\Sub2`, `Root\Sub1\Sub1.1`, `Root\Sub1\Sub1.2`, `Root\Sub2\Sub2.1`, `Root\Sub2\Sub2.2`... That is, folders are processed level by level. – lapis Mar 27 '15 at 10:06
1

@Lapis good point. The same order can be obtained in the non-recursive way by using a stack instead of a queue (so instead of `x = queue(1): queue.remove 1` it would be something like `x = queue(queue.count): queue.remove(queue.count)` – Cor_Blimey Apr 18 '15 at 14:09
@lapis even better, that's the behaviour i was looking for. – robotik Aug 02 '16 at 11:50

Loop Through All Subfolders Using VBA

2 Answers2

Linked

Related