VBA & Macro Problems

Excel VBA Macro to Extract Cell Data, File Names, and Modified Dates

Natalie Taylor Oct 1, 2026 869 views

Question details

The user needs a VBA macro to automate the extraction of data from cell C3, along with the file name and last modified date, across approximately 100 Excel files into a master sheet.

How to Create an Excel VBA Macro to Extract Cell Data, File Names, and Dates

Product: Microsoft Excel
Device & OS: not provided
Scenario: Consolidating specific data points and file metadata from multiple Excel workbooks into a single master sheet without manually opening each file.
Observed behavior: The macro needs to loop through a target folder, extract the file name to column A, modified date to column B, and cell C3 value to column C, then close the source files without saving.

Before you start

Before running the macro, ensure all target Excel files are placed in a single dedicated folder and verify that macros are enabled in your Excel Trust Center settings.

Solution 1Recommended

Use a VBA Macro with the Dir Function to Loop Through Files

This solution uses standard VBA functions to iterate through a target folder, extract the required metadata and cell value, and close the source files safely.

This macro leverages the built-in VBA Dir function to loop through all Excel files in a specified folder. It utilizes FileDateTime to capture the modification date and copies the specific cell data before closing the workbook without saving to optimize performance.

Set Up Your Destination Sheet

Open your master Excel workbook. In row 1, set up your headers: type 'File Name' in A1, 'Date Last Modified' in B1, and 'Data' in C1.

Open the VBA Editor

Press ALT + F11 to open the Visual Basic for Applications (VBA) editor. Click 'Insert' > 'Module' in the top menu to create a new blank module.

Write the Looping Script

Paste a VBA script that defines your folder path and uses the Dir() command to loop through all .xlsx files. Include the Workbooks.Open method to open each file in the background.

Map the Data to Columns

Within the loop, assign the active workbook's Name property to column A, use the FileDateTime() function on the file path for column B, and set column C equal to the value of Range("C3").

Close Without Saving

Add the command ActiveWorkbook.Close SaveChanges:=False at the end of the loop to close the source file, then proceed to the next file using the Next command. Press F5 to run the macro.

Use a VBA Macro with the Dir Function to Loop Through Files

Automated Processing: Closing the workbooks with SaveChanges:=False prevents unwanted modification prompts from interrupting your macro while processing the 100+ files.

Automate Data with WPS Office

Run VBA Macros to Automate Data Extraction in WPS Office

WPS Office provides robust built-in support for Excel VBA macros. You can easily run scripts to extract file metadata and cell data across multiple workbooks exactly as you would in Microsoft Excel.

1. Open WPS Spreadsheet: Download and install WPS Office, then open your master workbook where you want to consolidate the data.
2. Access the VBA Editor: Navigate to the 'Tools' or 'Developer' tab on the ribbon and click 'Visual Basic' to open the VBA editor.
3. Insert the Macro Code: Click 'Insert' > 'Module', paste your folder-looping data extraction VBA code, and update the folder path to match your PC.
4. Run the Automation: Press F5 or click 'Run' to execute the macro. WPS will automatically pull the file names, dates, and C3 values from your source files.

Fully compatible with Microsoft Excel macro formats (.xlsm)Seamlessly run standard VBA scripts and advanced user formsLightweight architecture that processes large batches of files quicklyFree and intuitive interface for everyday spreadsheet tasks

Frequently Asked Questions

Why does the macro fail to open some files in the folder?

This usually happens if the files are already open by another user, are corrupted, or have unsupported file extensions. Ensure your Dir function specifies "*.xlsx" or "*.xls" to avoid attempting to open non-Excel files.

How do I extract data from a cell other than C3?

In the VBA code, locate the line that references Range("C3").Value and simply change "C3" to your desired cell reference, such as "D10" or "A5".

Can I extract the file creation date instead of the last modified date?

Standard VBA's FileDateTime function only returns the last modified date. To capture the creation date, you must use the FileSystemObject (FSO) library instead of the standard Dir function.

Will this macro work if my source files are password protected?

No, the macro will pause and prompt you to manually enter a password for each protected file. To bypass this, you must include the Password:="yourpassword" argument directly in the Workbooks.Open method.

Excel & Data Problems