VBA & Macro Problems

How to Retrieve a File's Last Modified Date Using VBA in Excel

Olivia Miller Oct 16, 2026 870 views

Question details

The user wants to retrieve and display the last modified date of a file by combining a folder path and a file name stored in separate Excel columns using VBA.

How to Retrieve the Last Modified Date of a File Using VBA

Product: Excel
Device & OS: not provided
Scenario: Automating file metadata retrieval based on paths and names listed in an Excel table.
Observed behavior: The user needs a working VBA script to correctly concatenate the path strings and access the file properties without returning an error.

Before you start

Ensure you have the Developer tab enabled in Excel to access the VBA editor, and verify that the folder paths and file names in your spreadsheet are correctly spelled and accessible on your drive.

Solution 1Recommended

Use FileSystemObject in VBA to Retrieve File Modified Date

Utilize the Scripting.FileSystemObject in VBA to easily build the file path and extract file metadata such as the last modified date.

The most reliable way to handle file operations and metadata in VBA is through the FileSystemObject. By using its BuildPath method, you avoid errors related to missing backslashes when combining the folder path and file name.

Open the VBA Editor

Press Alt + F11 on your keyboard to launch the Visual Basic for Applications Editor.

Insert a New Module

In the VBA Editor menu, click on 'Insert' and select 'Module' to create a blank workspace for your script.

Add the FileSystemObject Code

Write a subroutine utilizing CreateObject("Scripting.FileSystemObject"). Use the fso.BuildPath method to combine the folder path cell and the file name cell into a fullPath variable.

Retrieve the Date

Use Set fileItem = fso.GetFile(fullPath) to target the file, and then call fileItem.DateLastModified to get the date. You can output this using a MsgBox or write it to another cell in your loop.

Use FileSystemObject in VBA to Retrieve File Modified Date

Path Building: Using the fso.BuildPath method automatically handles the addition of missing backslashes between your folder path and file name, preventing 'File Not Found' errors.

Solution 2

Use a File Picker Dialog to Check the Last Modified Date

If you don't have predefined paths in a table, use a file picker macro to manually select a file and retrieve its date.

VBA Support in WPS Office

Run VBA Macros Seamlessly in WPS Spreadsheet

WPS Office provides a robust Spreadsheet application that fully supports VBA macros, allowing you to automate tasks and retrieve file metadata seamlessly.

1. Install WPS Office: Download and install the free WPS Office suite on your computer.
2. Open Your Workbook: Launch WPS Spreadsheet and open the file containing your folder paths and file names.
3. Access the VBA Editor: Navigate to the Developer tab and click on 'VBA Editor' to open the scripting environment.
4. Run Your Macro: Paste your FileSystemObject code into a module and click Run to automatically process your file dates.

Fully compatible with Microsoft Excel VBA scripts and macro featuresLightweight application that runs smoothly on various devicesFree to download and use for your daily spreadsheet automation needsFamiliar ribbon interface ensures a zero-learning-curve transition

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting a 'File not found' error when running the macro?

This usually happens if the combined path is incorrect or the file doesn't exist at the specified location. Ensure the folder path and file name exactly match the actual directories, and verify that you are using fso.BuildPath to properly handle backslashes.

Do I need to enable any specific references for FileSystemObject in VBA?

When using late binding with CreateObject("Scripting.FileSystemObject"), you do not need to enable any additional references. It will work by default in the standard VBA environment.

Can I write the last modified date back to an Excel column instead of displaying a message box?

Yes, instead of using MsgBox to display fileItem.DateLastModified, you can assign the value directly to an adjacent cell inside your loop, such as cell.Offset(0, 2).Value = fileItem.DateLastModified.

Excel & Data Problems