Home Loop Through Subfolders: Open Only Spreadsheets?

Uncategorized

Loop Through Subfolders: Open Only Spreadsheets?

Learn how to use VBA to loop through all subfolders and open only spreadsheet files with efficient folder traversal logic.

byDev Solutions

February 2, 2026

VBA automation concept showing Excel file emerging from a maze of subfolders with text 'Only Open Spreadsheets!' to represent automated file traversal

🔁 VBA can fully automate the task of opening Excel spreadsheets across nested subfolders, saving manual effort.
🧐 Temporary and hidden system files can be filtered using string patterns to avoid wrongful processing.
💡 Using FileSystemObject from the Microsoft Scripting Runtime enables recursive traversal of folder trees.
📑 Aggregating data into a master workbook allows efficient data consolidation across many files.
⚠️ Using error-logging within your VBA script helps identify unreadable or problematic files for follow-up.

If you often work with many spreadsheets in many subfolders, opening them by hand is slow and often leads to mistakes. But with some VBA, you can automate this whole job. It will open only the files you need, no matter where they are. This guide shows you how to loop through folders, pick out spreadsheet files, open them safely, get the data you need, and make your automated process fast, accurate, and easy to keep up.

Use Cases: Why Look in Subfolders for Spreadsheets?

There are many reasons to make a VBA loop that looks through all folders:

Combine Data: Departments and regions often save regular reports in their own subfolders. A VBA loop can gather all Excel files and put them together in one place.
Make Reports: Financial reports, inventory lists, and project summaries often come in groups. If you get files automatically, you can get and work with all the right files, no matter how deep they are.
Clean Up Data or Update Many Files: Do you need to make formatting the same, fix formulas that do not work, or put data together again across dozens, or even hundreds, of Excel files? Searching Excel subfolders makes it easy to do regular updates for many files.
Bring into BI Tools: If you are bringing data into Power BI or SQL databases, getting updated spreadsheets automatically from many places keeps your data current.

These are real problems for business users, data analysts, IT auditors, and automation engineers every day.

Using FileSystemObject (FSO) in VBA to Go Through Folders

To work with folders and files well, the FileSystemObject is very helpful for VBA programmers. It lets you easily get to file systems, read file details, and go through folders and their subfolders.

Turning on the Microsoft Scripting Runtime Library

Before using FileSystemObject, you need to pick between early binding and late binding.

For early binding (best for making and testing programs):

Open the VBA editor (Alt + F11).
Go to Tools > References.
Check "Microsoft Scripting Runtime".

Then declare your object like this:

Dim fso As New FileSystemObject

For late binding (better for macros used on their own or shared):

Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")

The main plus of early binding is that it checks for errors when you write the code and helps you write it. But late binding does not need someone to turn on the library by hand on every computer.

You will use fso.GetFolder(path) to begin working with what is inside a folder.

Reference: Microsoft Docs – FileSystemObject

What Is a Spreadsheet File?

When you automate opening spreadsheets with VBA, it is important to only pick real Excel file types. These are:

.xls – An older Excel format. It is still used in old systems.
.xlsx – The usual Excel format. It came out with Excel 2007.
.xlsm – This Excel format can have macros. It is important for files with VBA code.

Leaving Out Temporary and Unneeded Files

We want to be fast and correct. Excel often makes many temporary files on its own. These might include:

Files that start with ~$, like ~$report.xlsx. These are auto-saved temp files from Excel's way to get files back after a crash.
Shortcut files (.lnk) and temporary cache files that seem like Excel files.
Files saved in other ways that look like spreadsheets but are not (for example, HTML or CSV files with the wrong endings).

So, you must check both the file's ending and its name pattern before opening it.

Main VBA Logic: Going Through All Folders and Subfolders

Here is the main part of the automation code: it loops through a main folder and then goes into every subfolder, checking each file.

Sub ProcessAllSpreadsheets()
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")

    Dim startFolder As String
    startFolder = "C:\Path\To\Your\Files"

    TraverseFolders fso.GetFolder(startFolder)
End Sub

Sub TraverseFolders(fld As Object)
    Dim fileItem As Object
    Dim subFld As Object

    ' Loop over files
    For Each fileItem In fld.Files
        If IsSpreadsheet(fileItem.Name) Then
            OpenSpreadsheet fileItem.Path
        End If
    Next

    ' Loop over subdirectories recursively
    For Each subFld In fld.SubFolders
        TraverseFolders subFld
    Next
End Sub

This code helps you go through files. It works like a web crawler: it is neat, follows rules, and repeats itself.

Filtering Files Carefully

Filtering is more than just one step. It can stop your program from crashing and running slowly.

Function IsSpreadsheet(fileName As String) As Boolean
    fileName = LCase(fileName)

    ' Ignore temp and autosave files
    If Left(fileName, 2) = "~$" Then
        IsSpreadsheet = False
        Exit Function
    End If

    ' Match known Excel extensions
    If Right(fileName, 4) = ".xls" Or _
       Right(fileName, 5) = ".xlsx" Or _
       Right(fileName, 5) = ".xlsm" Then
        IsSpreadsheet = True
    End If
End Function

You can add more rules for CSV files or other less common types of files, based on what you need.

Opening Spreadsheets with Workbooks.Open (Safely)

Now it is time to open the file. We will make sure there are as few problems as possible.

Sub OpenSpreadsheet(filePath As String)
    On Error Resume Next
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    Workbooks.Open Filename:=filePath, _
        UpdateLinks:=False, _
        ReadOnly:=True, _
        IgnoreReadOnlyRecommended:=True

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Using ReadOnly:=True stops files from being locked for writing and helps the process work with many files. Turning off update links means you will not see messages about outside data.

You might want to add logging here to see which files were opened when you are automating many files.

Reference: Microsoft Docs – Workbooks.Open

Putting Data Together into a Master Workbook (Optional)

After a spreadsheet is open, you might want to get some data from it. For example, let's say you want to get A1:C10 from every spreadsheet:

Sub CopyDataToMaster(wb As Workbook)
    Dim srcSheet As Worksheet
    Set srcSheet = wb.Sheets(1) ' Adjust if sheet names are standard

    Dim lastRow As Long
    With ThisWorkbook.Sheets("Master")
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        srcSheet.Range("A1:C10").Copy
        .Cells(lastRow, 1).PasteSpecial Paste:=xlPasteValues
    End With
End Sub

This helps with making reports in one place, creating dashboards, or checking things.

Handling Errors and Logging Failures Well

Do not let one bad file stop your whole process. Write down the error and keep going.

Function OpenSpreadsheetWithLog(filePath As String)
    On Error GoTo ErrorHandler

    Workbooks.Open Filename:=filePath, ReadOnly:=True
    Exit Function

ErrorHandler:
    LogToSheet filePath, Err.Description
End Function

Sub LogToSheet(filePath As String, errMsg As String)
    Dim logWs As Worksheet
    Set logWs = ThisWorkbook.Sheets("Log")

    Dim nextRow As Long
    nextRow = logWs.Cells(logWs.Rows.Count, 1).End(xlUp).Row + 1

    logWs.Cells(nextRow, 1).Value = filePath
    logWs.Cells(nextRow, 2).Value = errMsg
End Sub

This makes it much easier to find and fix problems and check your work after the main code has run.

Tips for Faster Work with Many Folders

When working with hundreds (or thousands) of spreadsheet files, it is very important for things to run fast.

Here are some useful tips:

📴 Turn off screen updating and alerts at the start:

Application.ScreenUpdating = False
Application.DisplayAlerts = False

🚫 Do not activate or select sheets you do not need (for example, avoid Sheet1.Activate)
⚡ Only use DoEvents a little bit. This helps the program respond and frees up memory.
🔁 Do less reading and writing to files. Only work with the parts you need.
🧠 Save often-used folder paths. Or, split the work over several days if files are on a slow network drive.

Reference: Microsoft Tech Community – Improving VBA Execution

Final Helpful Function: One Start Point for Processing

To let you use your script in other projects, put it together in a clean way:

Sub RunSpreadsheetProcessor(folderPath As String)
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")

    If fso.FolderExists(folderPath) Then
        TraverseFolders fso.GetFolder(folderPath)
    Else
        MsgBox "Error: Folder not found - " & folderPath
    End If
End Sub

You just need to change the path and run the main subroutine.

You can even run this from a button on your sheet.

Frequently Asked Questions

Q: Why do some spreadsheets not open?
A: They may be corrupted, locked by another user, or password-protected.

Q: How can I skip password-protected files?
A: Try opening in ReadOnly:=True. If it fails, log the error. Excel does not let you check passwords for security reasons.

Q: How to process only specific Excel spreadsheet names (e.g., “SalesReport_*.xlsx”)?
A: Add a condition inside IsSpreadsheet to include a Like "SalesReport_*" pattern.

Q: Can I safely open .xlsm files in automation?
A: Yes, especially using ReadOnly:=True and turning off macro warnings unless you need the macros to run.

Beyond the Basics: Making Your Script Better and Bigger

Once you have a good base, think about making it bigger:

⏰ Use Windows Task Scheduler: Run your VBA macro often to watch data as it comes in.
📤 Call from Access or Outlook VBA: Automate across different programs to get attachments or send out data.
💾 Send data to Power BI or SQL Server: Use Excel data with business intelligence tools.

You can also use this method with PowerShell or Python scripts. This gives you more options in bigger work setups.

Good Ways to Keep Your Work Running Long Term

Here are some tips for programmers to keep your work neat and dependable for a long time:

📝 Use clear comments and version tags in your modules.
📂 Put your code into different parts: one for input/output, one for working with data, and one for dealing with errors.
🎯 Do not hardcode folder paths or cell ranges. Keep them in a settings sheet instead.
🔍 Test with fake folders before using it for real work.
🧾 Keep a record of changes, mainly if the macro is shared or many people use it.

Automate Folders Easily

By putting together FileSystemObject, repeating logic, and good file filtering, you can automatically go through all subfolders, open only Excel spreadsheets, and even put data into one master sheet. This way does more than just get rid of manual work. It builds a system for Excel automation that you can use again and again, and that can grow with your needs. Whether you are tracking monthly goals or checking templates, this logic gives you speed and accuracy that is tough to get with manual steps.