Directory content from two different folders with Excel VBA

Hi,


I'm looking to use VBA to retrieve the contents of two or three different folders and combine results.

e.g.
C:\Users\User\Music
C:\Users\User\Videos




I have used the below, but it has to be run twice

Dim iRow

Sub ListFiles()
    iRow = 11
    Call ListMyFiles(Range("C7"), Range("C8"))
End Sub

Sub ListMyFiles(mySourcePath, IncludeSubfolders)
    Set MyObject = New Scripting.FileSystemObject
    Set mySource = MyObject.GetFolder(mySourcePath)
    On Error Resume Next
    For Each myFile In mySource.Files
        iCol = 2
        Cells(iRow, iCol).Value = myFile.Path
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.Name
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.Size
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.DateLastModified
        iRow = iRow + 1
    Next
    If IncludeSubfolders Then
        For Each mySubFolder In mySource.SubFolders
            Call ListMyFiles(mySubFolder.Path, True)
        Next
    End If
End Sub
kieranjcollinsAsked:
Who is Participating?
 
MacroShadowCommented:
Try this:
Dim iRow As Long

Sub ListFiles()
    Dim rng As Range, c As Range
    Set rng = Range("A1:A3")
    iRow = IIf(iRow = 1048576, Range("C1").End(xlDown).Row, 1)
    For Each c In rng.Cells
        Call ListMyFiles(Range("A" & c.Row), Range("B" & c.Row))
    Next
End Sub

Sub ListMyFiles(mySourcePath, IncludeSubfolders)
    Dim MyObject As Object, mySource As Object, myFile As Object, mySubFolder As Object
    Set MyObject = CreateObject("Scripting.FileSystemObject")
    Set mySource = MyObject.GetFolder(mySourcePath)
    On Error Resume Next
    For Each myFile In mySource.Files
        Cells(iRow, 3).Value = myFile.Path
        Cells(iRow, 4).Value = myFile.Name
        Cells(iRow, 5).Value = myFile.Size
        Cells(iRow, 6).Value = myFile.DateLastModified
        iRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "C").End(xlUp).Row + 1
    Next
    If IncludeSubfolders Then
        For Each mySubFolder In mySource.SubFolders
            Call ListMyFiles(mySubFolder.Path, True)
        Next
    End If
    ActiveSheet.UsedRange.Columns.AutoFit
End Sub

Open in new window

0
 
MacroShadowCommented:
Use this code, it assumes that the path is in A1:A3 and B1:B3 is either true or false depending on whether subfolders should be included.
Sub ListFiles()
    Dim rng As Range, c As Range
    Set rng = Range("A1:A3")
    For Each c In rng.Cells
        Call ListMyFiles(Range("A" & c.Row), Range("B" & c.Row))
    Next
End Sub

Sub ListMyFiles(mySourcePath, IncludeSubfolders)
    Dim iRow As Long, iCol As Long
    Dim MyObject As Object, mySource As Object, myFile As Object, mySubFolder As Object
    iRow = 11
    Set MyObject = CreateObject("Scripting.FileSystemObject")
    Set mySource = MyObject.GetFolder(mySourcePath)
    On Error Resume Next
    For Each myFile In mySource.Files
        iCol = 2
        Cells(iRow, iCol).Value = myFile.Path
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.Name
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.Size
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.DateLastModified
        iRow = iRow + 1
    Next
    If IncludeSubfolders Then
        For Each mySubFolder In mySource.SubFolders
            Call ListMyFiles(mySubFolder.Path, True)
        Next
    End If
End Sub

Open in new window

0
 
kieranjcollinsAuthor Commented:
The listing of the second folder contents in the above start populating on the first row, overwriting the contents of the first folder.

Can the code work so that folder 2 contents are stacked on folder 1 contents etc?
0
 
kieranjcollinsAuthor Commented:
That worked a treat, thank you very much!
0
 
MacroShadowCommented:
You're welcome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.