This blog post describes how to list files in a folder and subfolders using vba.

Where to copy vba code?

  1. Press Alt-F11 to open visual basic editor
  2. Click Module on the Insert menu
  3. Copy and paste vba code, both functions below.
  4. Exit visual basic editor
Public temp() As String
Function ListFiles(FolderPath As String)
Dim k As Long, i As Long
ReDim temp(2, 0)
If Right(FolderPath, 1) <> "\" Then
    FolderPath = FolderPath & "\"
End If
Recursive FolderPath
k = Range(Application.Caller.Address).Rows.Count
If k < UBound(temp, 2) Then
    MsgBox "There are more rows, extend user defined function"
Else
    For i = UBound(temp, 2) To k
          ReDim Preserve temp(UBound(temp, 1), i)
            temp(0, i) = ""
            temp(1, i) = ""
            temp(2, i) = ""
    Next i
End If
ListFiles = Application.Transpose(temp)
ReDim temp(0)
End Function
Function Recursive(FolderPath As String)
Dim Value As String, Folders() As String
Dim Folder As Variant, a As Long
ReDim Folders(0)
If Right(FolderPath, 2) = "\\" Then Exit Function
Value = Dir(FolderPath, &H1F)
Do Until Value = ""
    If Value = "." Or Value = ".." Then
    Else
        If GetAttr(FolderPath & Value) = 16 Then
            Folders(UBound(Folders)) = Value
            ReDim Preserve Folders(UBound(Folders) + 1)
        Else
            temp(0, UBound(temp, 2)) = FolderPath
            temp(1, UBound(temp, 2)) = Value
            temp(2, UBound(temp, 2)) = FileLen(FolderPath & Value)
            ReDim Preserve temp(UBound(temp, 1), UBound(temp, 2) + 1)
        End If
    End If
    Value = Dir
Loop
For Each Folder In Folders
    Recursive FolderPath & Folder & "\"
Next Folder
End Function

How to use custom function (array formula example)

  1. Select a cell range, three columns and many rows.
  2. Type =ListFiles("c:\temp1") in formula bar.
  3. Press and hold CTRL + SHIFT
  4. Press Enter once
  5. Release all keys