VBA & Macro Problems

How to Show a SharePoint Source File's Last Modified Date in Excel using VBA

Maira Mehtab Oct 10, 2026 868 views

Question details

The user wants to display the last modified date of a SharePoint source file inside an Excel workbook.

How to Show a SharePoint Source File's Last Modified Date in Excel

Product: Excel
Device & OS: not provided
Scenario: Tracking file updates and monitoring when a shared SharePoint source workbook was last modified from within another Excel file.
Observed behavior: Standard worksheet formulas cannot read external file properties, so a VBA-based solution is required to fetch the timestamp.

Before you start

Ensure your SharePoint folder is synchronized to your local computer (e.g., via OneDrive) so you have a valid local file path for the macro to read.

Solution 1Recommended

Use the VBA FileDateTime Function

Create a custom VBA function or macro to extract the last modified date of the synchronized SharePoint file using its local path.

The FileDateTime function retrieves the date and time a file was created or last modified. To use this with a SharePoint file, the file must be synced to your local drive so you can provide a standard local path (like C:\Users\Name\SharePoint\...). HTTP URLs generally do not work with this specific VBA function.

Open the VBA Editor

Open your Excel workbook and press Alt + F11 to launch the Visual Basic for Applications (VBA) Editor.

Insert a New Module

Click 'Insert' from the top menu and select 'Module' to create a new blank code module.

Write the Custom Function

Create a function using the syntax: FileDateTime("C:\Path\filename.xlsx"). For example, you can write a UDF (User Defined Function) that accepts a file path range and returns the date.

Call the Function in Excel

Return to your worksheet and use your new custom function in a cell, or run your macro to output the timestamp.

Manual Refresh Required: The timestamp is only retrieved when the VBA code runs. It will not update automatically if the source file changes while the workbook is open; you must re-run the macro or recalculate the worksheet.

Advanced Spreadsheet Features

Run VBA Macros to Check File Dates in WPS Spreadsheet

WPS Spreadsheet fully supports VBA (Visual Basic for Applications), allowing you to run custom macros and use functions like FileDateTime just as you would in Microsoft Excel. Manage your shared files efficiently with powerful macro support.

1. Open your workbook: Launch WPS Spreadsheet and open the file where you want to display the modified date.
2. Access the Developer tab: Navigate to the 'Developer' tab on the top ribbon menu.
3. Launch the VBA Editor: Click the 'VBA Editor' button to open the programming environment.
4. Run your macro: Insert your FileDateTime code into a new module and execute it to fetch the latest timestamp.

Full support for VBA macros and custom functionsSeamless compatibility with Microsoft Excel (.xlsm) formatsLightweight application with high performanceFree and intuitive tabbed interface

microsoft office alternative - wps office

Frequently Asked Questions

Can I use a direct SharePoint web URL in the FileDateTime function?

No, the FileDateTime function typically requires a valid local or mapped network path. You must synchronize the SharePoint library to your local machine using OneDrive to get a readable local path (e.g., C:\Users\...).

Is there a formula to get the last modified date without VBA?

There is no built-in standard Excel formula to retrieve external file properties like the last modified date. You must use a VBA macro or a Power Query workaround to get this information.

Why isn't the modified date updating automatically when the source file changes?

The VBA FileDateTime function only evaluates when the code is executed or triggered by calculation. If the external file is updated, you need to manually re-run the macro or force the sheet to recalculate (using F9) if you wrote it as a User Defined Function.

Excel & Data Problems