VBA & Macro Problems

How to Retrieve File Details and Video Duration Using Excel VBA

Nimra Malik Sep 28, 2026 869 views

Question details

The user needs to retrieve file metadata, including MP4 video duration, file size, and creation date, using an Excel VBA script.

How to Retrieve File Details and Video Duration Using Excel VBA

Product: Excel VBA
Device & OS: Windows
Scenario: Automating the extraction of media file properties directly into a spreadsheet using Windows Shell properties in VBA.
Observed behavior: The VBA script throws Namespace or ParseName errors when processing file paths, especially when attempting to read files with spaces in their names or when passing incorrect path formats.

Before you start

Ensure that the target MP4 files are stored on a local Windows drive, as Shell.Application relies on the Windows Explorer property system to read extended metadata.

Solution 1Recommended

Use Shell.Application and GetDetailsOf for Media Metadata

This is the ideal method for extracting extended file properties like media duration, frame rate, or specific MP4 metadata exposed by Windows Explorer.

The Shell.Application object interacts directly with Windows Explorer, allowing you to pull the exact properties you see when right-clicking a file and viewing its Details tab. To avoid errors, you must strictly separate the folder path from the file name.

Initialize the Shell Object

In your VBA editor, declare your variables and create the Shell object using: Dim objShell As Object, set objShell = CreateObject("Shell.Application").

Define the Namespace (Folder)

Extract the directory path from your full file path. Pass this folder path to the Shell object using: Set objFolder = objShell.Namespace("C:\Your\Folder\Path"). Ensure there is no trailing backslash.

Parse the File Name

Pass ONLY the file name with its extension (e.g., "video.mp4") to the folder object: Set objFile = objFolder.ParseName("video.mp4"). Do not pass the full file path here, or ParseName will fail.

Retrieve the Duration

Use the GetDetailsOf method to read the property. For example, duration is often index 27 on modern Windows versions: duration = objFolder.GetDetailsOf(objFile, 27). The output will be a time string formatted as HH:MM:SS.

Use Shell.Application and GetDetailsOf for Media Metadata

Handling ParseName Errors with Spaces: If you receive an error at ParseName with files containing spaces, the issue is usually caused by passing the full file path instead of just the file name. As long as objShell.Namespace receives the folder and ParseName receives just the file name, spaces will not cause failures.

Solution 2

Use FileSystemObject for Basic Attributes

A simpler and more robust approach when you only need standard attributes like file size and creation date, rather than media-specific metadata.

Powerful Spreadsheet Editor

Automate File Data Extraction with WPS Spreadsheet

WPS Spreadsheet fully supports VBA (Visual Basic for Applications) and macros, allowing you to run scripts that interact with the Windows Shell. You can easily extract video durations, file sizes, and creation dates directly into your worksheets using the exact same code you would use in Microsoft Excel.

1. Enable the Developer Tab: Open WPS Spreadsheet, go to the Options menu, and ensure the Developer tools are enabled for your workspace.
2. Open the VBA Editor: Navigate to the Developer tab on the ribbon and click on 'Visual Basic' to launch the integrated VBA Editor.
3. Insert Your Script: Right-click your project, select Insert > Module, and paste your Shell.Application or FileSystemObject code.
4. Run the Macro: Press F5 or run the macro from your worksheet to instantly parse your MP4 files and populate your cells with the extracted file metadata.

Full support for standard Excel VBA macros and Shell.Application scriptingHighly compatible with Microsoft Excel .xlsm and .xlsb formatsLightweight application with exceptionally fast execution speedsFree to use with a familiar user interface for seamless migration

microsoft office alternative - wps office

Frequently Asked Questions

Why does ParseName return Nothing when a file has spaces?

ParseName typically fails if you mistakenly pass the entire file path instead of just the file name. Ensure you separate the directory path (used in Shell.Namespace) from the file name (used in ParseName). Spaces in the file name itself are perfectly valid and will not cause errors if parsed correctly.

Can I get the MP4 video duration in seconds instead of a time string?

GetDetailsOf returns a formatted time string (e.g., '00:05:30'). To convert this to total seconds, you need to parse the string in VBA. You can use the TimeValue function and multiply the result by 86400, or split the string by colons and calculate the seconds manually.

Are the GetDetailsOf property ID numbers the same on all PCs?

No, the index numbers for Windows Explorer properties (such as media duration or frame rate) can vary depending on your version of Windows. It is highly recommended to write a small VBA loop through IDs 0 to 300 to find the exact index for 'Length' or 'Duration' on your specific system.

Excel & Data Problems