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 [/vb] [vb 1="vbnet" language=","] 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 [/vb]

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