How to Get File Paths from a Folder and Subfolders in Excel Using VBA

August 25, 2025 Umesh Manral 0 Comments
Advertisement

How to Get File Paths from a Folder and Subfolders in Excel Using VBA

Tired of manually listing files? Learn how to use a simple Excel VBA macro to automatically get every file path from a folder and all its subfolders. Download our free tool!

Advertisement

Let me tell you a quick story. In one of my first jobs, I had this daily task that drove me crazy. Every morning, I had to open a big folder on our server, click through a dozen subfolders, and copy the file paths of hundreds of Excel, Word, and PDF files into a spreadsheet.

It was a huge waste of time. I’d spend over an hour just on this one boring job, and I knew there had to be a better way. That’s when I discovered I could teach Excel to do it for me using a simple macro.

I learned how to code a little VBA magic, and I built a tool that could scan an entire folder—and every single subfolder inside it—and list all the file paths automatically. It turned a 90-minute chore into a 10-second click.

I’m sharing this because I know I’m not the only one. If you’re an executive, data analyst, or admin who does this manually, this will change your life.

How to Get Excel to List All Your Files Automatically

The magic behind this is a VBA script. Don’t worry if that sounds technical. I’ve already done the hard part for you. You just need to paste the code into Excel.

Here’s how it works:

  • You click a button in Excel.
  • A window pops up asking you to choose a folder. You pick the main folder you want to scan.
  • You click “OK,” and Excel gets to work. It goes through every file in that folder and then dives into every subfolder, no matter how deep they go.
  • In seconds, you have a perfect list of every file path in column A.
  • It really is that easy.

Here’s the Code You Need

First, press ALT + F11 to open the Excel VBA editor. Right-click on your project name on the left, select Insert > Module, and paste this code into the big white window.

Sub GetFilePaths()

Dim MyFolder As String
Dim MyFile As String
Dim NextRow As Long

' Clear old results
Range("A2:A10000").ClearContents

' Let the user pick a folder
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select the Main Folder"
.Show
If .SelectedItems.Count = 0 Then Exit Sub
MyFolder = .SelectedItems(1) & "\"
End With

' Find the next empty row to start listing
NextRow = 2

' Call the main scanning routine
Call FindAllFiles(MyFolder, NextRow)

MsgBox "All done! All file paths have been listed.", vbInformation

End Sub

' This is the routine that does the hard work
Sub FindAllFiles(ByVal FolderPath As String, ByRef RowNumber As Long)

Dim FSO As Object
Dim MainFolder As Object
Dim SubFolder As Object
Dim File As Object

' Create the object that can look through folders
Set FSO = CreateObject("Scripting.FileSystemObject")
Set MainFolder = FSO.GetFolder(FolderPath)

' Loop through each FILE in the current folder
For Each File In MainFolder.Files
Cells(RowNumber, 1).Value = File.Path
RowNumber = RowNumber + 1
Next File

' Now, loop through each SUBFOLDER in the current folder
For Each SubFolder In MainFolder.SubFolders
' This is the magic part: it calls itself to go deeper!
Call FindAllFiles(SubFolder.Path, RowNumber)
Next SubFolder

End Sub

To run it, close the editor, go to View > Macros > View Macros, select GetFilePaths, and click Run.

Get the Ready-to-Use Tool

Prefer to just have a file that works? I’ve got you covered.

I’ve put the complete macro into an Excel file for you. Just download it, open it, and click the button. It’s that simple.

Download the Automatic File Path Tool Here : Download File

(Remember to “Enable Content” when you open the file so the macro can run!)

Stop wasting your valuable time on manual tasks. Let Excel handle the boring stuff so you can focus on more important work.

FAQs (Answers to Common Questions)

1. Is this safe for my computer?

Yes, 100%. The macro only reads the folder structure to list the files. It cannot edit, delete, or harm your files in any way.

2. Why does Excel say “Macros have been disabled”?

This is just Excel being cautious. To run the tool, you need to click “Enable Content” on the yellow bar at the top of the window. This gives the macro permission to run.

3. Can I list only certain types of files, like just PDFs?

Yes, but it requires a small change to the code. You can add a simple IF statement to only list files that end with “.pdf”. It’s an easy tweak if you’re comfortable with the code.

4. It’s taking a long time to run. Is that normal?

If you’re scanning a folder with thousands of files and many subfolders, it can take a minute or two. Excel is working hard! Just be patient and let it finish.

5. Can I add other information, like the file size?

Definitely. The code can be easily expanded to pull the file size, the last modified date, and more, putting that information in the next columns (B, C, etc.).

Keep Reading