VBA & Macro Problems

How to List Files and Subfolders in Excel with VBA

Olivia Miller Oct 9, 2026 871 views

Question details

The user needs to create a VBA macro that scans a specific local folder and writes the names and full paths of all files and subdirectories into an Excel worksheet.

How to List Files and Subfolders in Excel Using VBA

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Cataloging or documenting local file system contents directly into a spreadsheet when local file-system access is required.
Observed behavior: Populating a complete list of file names in column A and their corresponding full paths in column B of the active worksheet.

Before you start

Ensure you have the Developer tab enabled in your spreadsheet software and that you have the exact local file path of the directory you wish to scan ready for use.

Solution 1Recommended

Use FileSystemObject in a VBA Macro

Run a custom VBA script using the FileSystemObject to iterate through a target folder and output file names and paths into your worksheet.

Using the FileSystemObject (FSO) is the most efficient way to interact with your computer's file system through VBA. This method allows you to fetch file names, paths, and even loop through nested subfolders.

Open the VBA Editor

Press Alt + F11 on your keyboard to launch the Visual Basic for Applications (VBA) editor.

Insert a New Module

In the top menu, click on 'Insert' and then select 'Module' to create a new standard module where you will paste your code.

Add the FileSystemObject Code

Write or paste a VBA macro that uses 'Scripting.FileSystemObject' to loop through the specified folder. Be sure to replace the dummy folder path in the code with your actual target directory.

Run the Macro

Close the VBA editor and return to your worksheet. Press Alt + F8, select your newly created macro from the list, and click 'Run'. The macro will populate Column A with file names and Column B with full paths.

Enable Microsoft Scripting Runtime: Depending on how your VBA code is written (Early Binding), you may need to enable the 'Microsoft Scripting Runtime' library. You can do this in the VBA editor by going to Tools > References and checking the corresponding box.

List Files and Folders Easily with WPS Spreadsheet

WPS Office Spreadsheet provides full support for VBA macros, allowing you to run FileSystemObject scripts seamlessly to list files and folders. It offers a familiar interface, making your macro development fast, efficient, and highly compatible.

1. Open WPS Spreadsheet: Launch WPS Office and open a new or existing spreadsheet where you want the file list to appear.
2. Access the Developer Tab: Navigate to the Developer tab on the top ribbon to access the macro and scripting tools.
3. Open the VBA Editor: Click on the 'VBA Editor' button or press Alt + F11 to open the environment and insert a new module.
4. Execute Your Script: Paste your FileSystemObject macro code and run it to instantly populate your spreadsheet with file names and folder paths.

Fully compatible with Microsoft Excel VBA macros and .xlsm file formats.Built-in VBA editor for quick scripting, editing, and debugging.Lightweight software with fast execution for processing large directories.Free and easy-to-use alternative with a highly familiar user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Can I list files from nested subfolders as well?

Yes, by using a recursive function in your VBA code, you can instruct the FileSystemObject to loop through every subfolder inside the main directory and extract the file paths.

Why am I getting a 'User-defined type not defined' error when running the macro?

This usually happens if you are using early binding without enabling the correct reference library. In the VBA editor, go to Tools > References and check 'Microsoft Scripting Runtime'.

How can I filter the extracted list to only show specific file types like PDFs?

You can add an 'If' statement inside your VBA loop to check the file extension using the Right() function or FSO.GetExtensionName() before writing the data to the worksheet.

Are Office Scripts the same as VBA macros for this task?

No. Office Scripts use TypeScript and are primarily designed for Excel on the web, meaning they cannot directly access local file systems. VBA is a desktop-based language that can use the FileSystemObject to access your local hard drive.

Excel & Data Problems