CODEWITHSUNDEEP
vbaloopsexceldirectory
mike
mike

Reputation: 23811

Specify Additional Directory to Loop Through Excel/VBA

I am looping through a set of directories using a File System Object, and I want to specify an additional directory to loop through. For example, I currently have:

Sub test()
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder(Directory)
    Set colSubfolders = objFolder.Subfolders
    For Each objSubfolder In colSubfolders
        ' take some action
    End For
End Sub

But, I want to specify an additional folder to loop through, such as:

colSubfolders = colSubfolders + "additionalpath"
For Each objSubfolder In colSubfolders ....

Alternatively, is it possible to specify multiple objects in a loop command, such as:

For Each objSubfolder in colSubfolders, "additionalpath"

Upvotes: 3

Views: 786

Answers (1)

Alex K.
Alex K.

Reputation: 175766

Simplest way is to break out the code that enumerates the directories and the code that "takes some action" and wrap it in a call that repeats what you need;

Sub foo()
    enumDirs "c:\temp\", "c:\music", "c:\as many as you like ..."
End Sub

Sub enumDirs(ParamArray strPaths() As Variant)
    Dim i As Long
    For i = 0 To UBound(strPaths)
        enumDir CStr(strPaths(i))
    Next
End Sub

Sub enumDir(strPath As String)
    Dim objFolder As Object, colSubfolders As Object, objSubfolder As Object
    Set objFolder = CreateObject("Scripting.FileSystemObject").GetFolder(strPath)
    Set colSubfolders = objFolder.Subfolders
    For Each objSubfolder In colSubfolders
        TakeSomeAction strPath, objSubfolder.Name
    Next
End Sub

sub TakeSomeAction(strRoot As String, strFoundPath As String)
    Debug.Print ">"; strRoot & ", " & strFoundPath
End sub

Upvotes: 3

Related Questions