[Excel VBA] How to Get File Properties (Name, Extension, Date, Size) Using FSO

When handling files or file lists in VBA, you often need detailed information (metadata) such as the file extension, size, and last modified date, rather than just the file name.

By using the FileSystemObject (FSO), you can retrieve this information easily and quickly without opening the file. This article explains how to access various file properties using FSO with specific code examples.

Preparation: Set Reference

To use FSO smoothly, it is recommended to open Tools > References in the VBA Editor and check “Microsoft Scripting Runtime”.

VBA Code Example

The following code retrieves detailed information about the Excel file itself (the file containing this macro) and writes it to column B of the active sheet.

' Reference: Microsoft Scripting Runtime
Sub GetFileProperties()

    ' Declare variables
    Dim fso As New FileSystemObject
    Dim targetFile As File
    Dim targetFilePath As String
    
    '--- Set the target file path ---
    targetFilePath = ThisWorkbook.FullName

    ' Stop processing if the file does not exist
    If Not fso.FileExists(targetFilePath) Then
        MsgBox "Target file not found.", vbCritical
        Exit Sub
    End If

    '--- 1. Get info from path string using FSO methods ---
    Range("B1").Value = fso.GetBaseName(targetFilePath)      ' Base name (without extension)
    Range("B2").Value = fso.GetExtensionName(targetFilePath) ' Extension only

    '--- 2. Get File object and retrieve info from its properties ---
    Set targetFile = fso.GetFile(targetFilePath)
    
    With targetFile
        Range("B3").Value = .DateCreated        ' Date created
        Range("B4").Value = .DateLastAccessed   ' Date last accessed
        Range("B5").Value = .DateLastModified   ' Date last modified
        Range("B6").Value = .Drive              ' Drive name
        Range("B7").Value = .ParentFolder       ' Parent folder path
        Range("B8").Value = Format(.Size / 1024, "#,##0") & " KB" ' Size (in KB)
        Range("B9").Value = .Type               ' File type
    End With
    
    ' Release objects
    Set targetFile = Nothing
    Set fso = Nothing
    
    MsgBox "File information retrieved."

End Sub

Key Points of the Code

There are two main approaches to retrieving file information with FSO.

1. Methods for Manipulating Path Strings

The FSO object has convenient methods to extract specific parts from a file path string.

• .GetBaseName(path): Returns only the name part (e.g., returns “Report” from “Report.xlsx”).
• .GetExtensionName(path): Returns only the extension part (e.g., returns “xlsx” from “Report.xlsx”).

2. Using File Object Properties

By using Set targetFile = fso.GetFile(targetFilePath), you create a File object that represents the file itself. Once you have this object, you can access various details by referring to its properties.

Property	Description
.Name	File name (including extension)
.Path	Full path of the file
.ParentFolder	Path of the parent folder
.Size	File size (in bytes)
.Type	File type (e.g., “Microsoft Excel Worksheet”)
.DateCreated	Date and time the file was created
.DateLastModified	Date and time the file was last modified
.DateLastAccessed	Date and time the file was last accessed
.Drive	Drive name (e.g., “C:”)

Advanced: Listing All File Properties in a Folder

By applying this technique, you can create a practical macro that lists detailed information for all files in a specified folder.

Sub ListUpAllFileProperties()
    Dim fso As New FileSystemObject
    Dim targetFolder As Folder
    Dim f As File
    Dim i As Long
    
    ' Target the folder where this workbook is saved
    Set targetFolder = fso.GetFolder(ThisWorkbook.Path)
    i = 2 ' Start writing from row 2
    
    ' Write headers
    Range("A1:D1").Value = Array("File Name", "Size (KB)", "Type", "Last Modified")
    
    ' Loop through all files in the folder
    For Each f In targetFolder.Files
        Cells(i, 1).Value = f.Name
        Cells(i, 2).Value = f.Size / 1024
        Cells(i, 3).Value = f.Type
        Cells(i, 4).Value = f.DateLastModified
        i = i + 1
    Next f
    
    Columns("A:D").AutoFit ' Auto-fit column width
End Sub

Summary

By using FSO, you can easily access file metadata.

• If you only need the file name or extension, .GetBaseName and .GetExtensionName are convenient.
• If you need more information like size or modified date, use .GetFile to get the File object and access its properties.

Mastering these features will greatly expand the possibilities of your VBA tools, such as file organization, log recording, and system integration.