CODEWITHSUNDEEP
excelvbafiledirectory
user2993882
user2993882

Reputation: 21

VBA macro to list files in multiple folders

I have the following code and I would like to get the files in various folders listed horizontally. How can I amend this code so that for a given file path in column A, I get the files in this column in column C onwards? My knowledge only allows me to do it for one folder (rather than the 150 I want it to look in)

`enter code here`
Sub ListFiles()
  iCol = 3
  Call ListMyFiles(Range("A5"), Range("B5"))
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
        iRow = 5

        Cells(iRow, iCol).Value = myFile.Name
        iCol = iCol + 1

    Next
    If IncludeSubfolders Then
        For Each mySubFolder In mySource.SubFolders
            Call ListMyFiles(mySubFolder.Path, True)
        Next

    End If
End Sub

Upvotes: 2

Views: 5443

Answers (2)

jacouh
jacouh

Reputation: 8741

I tested this in Excel 2007:

Sub ListMyFiles(mySourcePath, IncludeSubfolders, iRow, iCol)
  Dim iColNow, iColSub
  Dim MyObject, mySource, myFile, mySubFolder
  Set MyObject = CreateObject("Scripting.FileSystemObject")
  Set mySource = MyObject.GetFolder(mySourcePath)
  On Error Resume Next
  iColNow = iCol
  For Each myFile In mySource.Files
    Cells(iRow, iColNow).Value = myFile.Name
    iColNow = iColNow + 1
  Next
  If IncludeSubfolders Then
    '
    'iColSub = iCol + 1
    '
    iColSub = iCol
    For Each mySubFolder In mySource.SubFolders
      iRow = iRow + 1
      Call ListMyFiles(mySubFolder.Path, IncludeSubfolders, iRow, iColSub)
    Next
  End If
End Sub

Sub ListFiles()
  Dim iRow, iCol
  iRow = 5
  iCol = 3
  Call ListMyFiles(Range("A5"), Range("B5"), iRow, iCol)
End Sub

IRow and iCol are function arguments to control starting position of result output. Range("A5") gives the starting folder name like C:\temp, Range("B5") is the subfolders listing control key, 1=true, 0=false.

enter image description here ========>

enter image description here

A blank row will be created for a folder with file entries.

iRow is recursively modified, in order to change rows for each subfolder.

Upvotes: 1

Jimmy Smith
Jimmy Smith

Reputation: 2451

'it's all in iRow
`enter code here`
Dim iRow as integer
Sub ListFiles()
  iCol = 3
  iRow = 5
  Call ListMyFiles(Range("A5"), Range("B5"))
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


        Cells(iRow, iCol).Value = myFile.Name
        iCol = iCol + 1              
    Next
    iRow = iRow + 1 
    If IncludeSubfolders Then
        For Each mySubFolder In mySource.SubFolders
            Call ListMyFiles(mySubFolder.Path, True)
        Next

    End If
End Sub

Upvotes: 0

Related Questions