VBA: How to Get a Spreadsheet of File Names

I recently found a really cool article from Software Solutions Online that shows you how to get a list of files in a folder or a list of subfolders in a folder. All the things, basically.

This weekend, I’m adapting that slightly to get a complete list of files within a folder including everything in its subfolders. I know. This is the sort of thing that’s either really interesting or really not, depending on how much you use VBA.

Step 1: Get Some Input

The first thing I want to modify is adding a user input box so that a person can just give Excel the file path, and it’ll spit out all the files within it.

Dim inputVariable As Variant
inputVariable = InputBox(“What folder?“)

That pushes the user input into a variable creatively named inputVariable.

Step 2: Make All the Objects

Dim objFSO As Object
Set objFSO = CreateObject(“Scripting.FileSystemObject”)
‘Imma be honest. No idea what this does, but it doesn’t work without it
Dim objFolder As Object
Set objFolder = objFSO.GetFolder(File Path)
‘This one makes a folder object so you can access its properties(like the files inside it)
Dim objFile As Object
‘This variable will hold the files so you can access their properties(like their names)

Step 3: Start the Loop

i = 1
For Each objFile In objFolder.Files
‘For each file in the object folder
Cells( i + 1, 1) = objFile.Name
‘Get the name and pop it in row i+1, column 1
Cells(i + 1, 2) = objFile.Path
‘Get the name and pop it in row i+1, column 2
i = i + 1
‘Add 1 and run the loop on the next file
Next objFile

Step 4: Add a Loop for Subfolders within that Folder

x = UsedRange.Rows.Count
‘Go to the last row
For Each Folder In objFolder.SubFolders
‘For each subfolder within the object folder
Set objFolder = objFSO.GetFolder(Folder)
‘Set the object folder equal to that subFolder
For Each objFile In objFolder.Files
‘For each file in that subfolder
Cells(x + 1, 1) = objFile.Name
‘Get the name and pop it in row i+1, column 1
Cells(x + 1, 2) = objFile.Path
‘Get the name and pop it in row i+1, column 2
x = x + 1
‘Add 1 and run the loop on the next file
Next objFile
‘Add 1 and run the loop on the next folder
Next Folder

Then alls you gotta do is code it into a button and run it.

And this should be the result:

So here’s the problem. Now I have a button that will get all the files within a folder and its subfolders, but what about the subfolders within those subfolders? I’ve decided that’s next week’s problem.