VBA & Macro Problems

How to Copy Excel File Names, Paths, and Modified Dates to a Worksheet

Rana Garcia Oct 23, 2026 868 views

Question details

The user wants to generate an Excel list containing workbook file names, folder paths, and last modified dates by scanning a specific directory and its subfolders.

How to Copy Excel File Names, Paths, and Modified Dates to a Worksheet

Product: Excel
Device & OS: not provided
Scenario: Organizing, tracking, or auditing multiple Excel files stored across various directories and needing to extract their metadata into a single worksheet.
Observed behavior: The user needs a functional method to automatically pull file metadata (path, name, and modified date) into Excel columns without manually typing the information.

Before you start

Ensure you have the exact file path of the target folder ready. If you plan to use the VBA macro method, make sure the Developer tab is enabled in your ribbon and macros are allowed.

Solution 1Recommended

Use a VBA Macro with FileSystemObject

Automate the extraction of file names, paths, and modification dates across subfolders directly into your worksheet.

This method utilizes a folder picker dialog to let you select the target directory dynamically. It then uses the FileSystemObject to scan recursively and output the data neatly into columns A, B, and C.

Open the VBA Editor

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

Insert a New Module

Click on 'Insert' in the top menu, then select 'Module' to create a blank script window.

Paste the Macro Code

Write or paste a VBA script that utilizes 'Scripting.FileSystemObject' to loop through folders, read file properties, and print 'File.Path', 'File.Name', and 'File.DateLastModified' to the active worksheet cells.

Run the Macro

Close the VBA editor and press Alt + F8 to run the macro. Select your desired parent folder when the folder picker dialog appears.

Handling Path Errors: If the macro reports a 'path not found' error, ensure you have read permissions for all included subfolders and that the selected folder actually exists.

Solution 2

Use Command Prompt and Text to Columns

A quick, code-free method using the Windows Command Prompt to generate a text list of files, which is then parsed in Excel.

Manage Data with WPS Office

List and Manage File Metadata Easily in WPS Spreadsheet

WPS Spreadsheet fully supports advanced data manipulation and VBA macros, allowing you to run scripts to extract file names, paths, and dates smoothly.

1. Open WPS Spreadsheet: Launch WPS Office and open a new blank spreadsheet where you want the file list to appear.
2. Access the Developer Tab: Navigate to the Developer tab on the ribbon and click on 'VBA Editor' to open the scripting environment.
3. Insert the Macro Code: Insert a new module and paste your FileSystemObject macro code designed to list the files.
4. Run the Script: Execute the macro and select your target folder. WPS Spreadsheet will populate columns A, B, and C with the extracted file data.

100% compatible with Microsoft Excel (.xlsx, .xls) formats.Built-in support for VBA macros to automate repetitive tasks.Advanced 'Text to Columns' tool for easy data formatting.Lightweight installation and fast loading speeds.

microsoft office alternative - wps office

Frequently Asked Questions

How do I enable the Developer tab to run VBA macros?

To enable the Developer tab, go to File > Options > Customize Ribbon. Check the box next to 'Developer' in the right-hand pane and click OK. The tab will now be visible on your ribbon.

Can I modify the VBA script to only list PDF files instead of Excel files?

Yes. If your VBA script specifies a file extension (such as *.xlsx), you can simply change that condition within the code to *.pdf, and the macro will compile a list of PDF files instead.

Does the CMD method easily extract the last modified date?

The standard 'dir' command outputs the modified date alongside the file name. However, since it is exported as raw text, you must carefully use Excel's 'Text to Columns' feature to split the date, time, and file path into clean, individual columns.

Excel & Data Problems