VBA: Output file list in a directory, recursively

Table of Contents

This is the code I frequently use.

It recursively exports file list in a directory on Microsoft Office Application, like Excel. It is written in VBA.

VBA can be written in object oriented way, but I didn’t because I had to write the code as soon as possible.

Process Flow

A user clicks the button on the sheet.
The program show a folder browsing dialog.
User chooses the folder, then the program make the list of file in the folder.
The program exports the file list onto a form (or sheet).

To Do

Create a module file and create the following functions.
- get file list
- handle folder browsing dialog
Create a dialog to show the result.
Create a button on the sheet and add function on clicking.

Create a module file of VBA, and add the functions below.

Get file list

getFileAndDirectories is the function to get file list and make output text. Folder and subfolder are separated by “/“, it is the requirement of my circumstance.

Public Function getFileAndDirectories( _
ByVal folderPathString$, _
Optional ByVal prefixString$ = "") As String
Dim fso As Object
Dim fol As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set fol = fso.GetFolder(folderPathString)
Dim resultString$
resultString = ""
If prefixString <> "" Then
prefixString = prefixString & "/"
End If
For Each fil In fol.Files
resultString = resultString & prefixString & fil.Name & vbNewLine
Next
Dim sfol As Object
For Each sfol In fol.subFolders
resultString = resultString & getFileAndDirectories(sfol.path, prefixString & sfol.Name)
Next
Set fol = Nothing
Set fso = Nothing
Set sfol = Nothing
getFileAndDirectories = resultString
End Function

Public Function getFileAndDirectories( _

ByVal folderPathString$, _

Optional ByVal prefixString$ = "") As String

Dim fso As Object

Dim fol As Object

Set fso = CreateObject("Scripting.FileSystemObject")

Set fol = fso.GetFolder(folderPathString)

Dim resultString$

resultString = ""

If prefixString <> "" Then

prefixString = prefixString & "/"

End If

For Each fil In fol.Files

resultString = resultString & prefixString & fil.Name & vbNewLine

Dim sfol As Object

For Each sfol In fol.subFolders

resultString = resultString & getFileAndDirectories(sfol.path, prefixString & sfol.Name)

Set fol = Nothing

Set fso = Nothing

Set sfol = Nothing

getFileAndDirectories = resultString

End Function

Selecting Folder

The above function get the file list in the folder, and we should add the function to decide which folder should be searched.

The following function meets our requirement.

Public Function OpenFolderDialog(Optional ByVal title$ = "Choose Folder") As String
Dim FolderDlg As Office.FileDialog
Dim NewFolderPath As String
Dim Result As Integer
NewFolderPath = ""
Set FolderDlg = Application.FileDialog(msoFileDialogFolderPicker)
With FolderDlg
.AllowMultiSelect = False
.title = title
End With
Result = FolderDlg.Show()
If Result = -1 Then
NewFolderPath = FolderDlg.SelectedItems(1)
End If
Set FolderDlg = Nothing
OpenFolderDialog = NewFolderPath
End Function

Public Function OpenFolderDialog(Optional ByVal title$ = "Choose Folder") As String

Dim FolderDlg As Office.FileDialog

Dim NewFolderPath As String

Dim Result As Integer

NewFolderPath = ""

Set FolderDlg = Application.FileDialog(msoFileDialogFolderPicker)

With FolderDlg

.AllowMultiSelect = False

.title = title

End With

Result = FolderDlg.Show()

If Result = -1 Then

NewFolderPath = FolderDlg.SelectedItems(1)

End If

Set FolderDlg = Nothing

OpenFolderDialog = NewFolderPath

End Function

OK, module was created.

Result Output Form

Create a form to show the result.

I created the form, textViewForm, having only one TextBox. Set TextBox property to handle multi-line text.

The result is text data, so it can be printed on sheet.

Now, add button on the sheet and set to start process on clicking.

Add Button

Add a button onto the sheet and add the following code to start process on clicking it.

Private Sub listFiles_Click()
Dim path As String
path = OpenFolderDialog("Please Choose One Folder.")
textViewForm.outputTextBox.text = getFileAndDirectories(path)
textViewForm.Show
End Sub