[Excel VBA] How to Get Total Folder Size (Including Subfolders) with FSO

2025年12月27日

Introduction

There are times when you want to check how much disk space a specific project or backup folder is occupying. While you can check this manually by opening the folder’s properties, you can also retrieve this information automatically using VBA.

By using the .Size property of the Folder object in FileSystemObject (FSO), you can easily get the total size of all files and subfolders contained within a specified directory.

Preparation: Setting References

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

Completed VBA Code

The following code targets the folder where the current Excel file is located, calculates its total size in Bytes, KB, MB, and GB, and displays the results in a message box.

' Reference: Microsoft Scripting Runtime
Sub GetFolderTotalSize()

    ' Declare variables
    Dim fso As New FileSystemObject
    Dim targetFolder As Folder
    Dim folderPath As String
    Dim sizeInBytes As Currency ' Use Currency for handling large numbers
    Dim message As String
    
    '--- Set the target folder path ---
    folderPath = ThisWorkbook.Path

    ' Stop process if the folder does not exist
    If Not fso.FolderExists(folderPath) Then
        MsgBox "Target folder not found.", vbCritical
        Exit Sub
    End If
    
    '--- 1. Get the Folder object ---
    Set targetFolder = fso.GetFolder(folderPath)
    
    '--- 2. Retrieve total size with the .Size property ---
    sizeInBytes = targetFolder.Size
    
    '--- 3. Convert units and create the message ---
    message = "[Folder Size Information]" & vbCrLf & vbCrLf & _
              "Target Folder: " & targetFolder.Path & vbCrLf & _
              "---------------------------------" & vbCrLf & _
              "Bytes: " & Format(sizeInBytes, "#,##0") & " B" & vbCrLf & _
              "Kilobytes: " & Format(sizeInBytes / 1024, "#,##0.00") & " KB" & vbCrLf & _
              "Megabytes: " & Format(sizeInBytes / 1024 / 1024, "#,##0.00") & " MB" & vbCrLf & _
              "Gigabytes: " & Format(sizeInBytes / 1024 / 1024 / 1024, "#,##0.00") & " GB"
              
    '--- 4. Display results ---
    MsgBox message, vbInformation, "Retrieve Folder Size"
    
    ' Release objects
    Set targetFolder = Nothing
    Set fso = Nothing

End Sub

Explanation of Key Points

1. Retrieving the Folder Object

Set targetFolder = fso.GetFolder(folderPath)

First, use the path of the folder you want to inspect to retrieve the Folder object via the GetFolder method.

2. The .Size Property

sizeInBytes = targetFolder.Size

This is the core of retrieving the total folder size. The .Size property of the Folder object returns the combined size of all items within that folder in bytes.

Importantly, this total size is calculated recursively. This means it includes not only the files directly inside the folder but also all subfolders, their contents, and so on, reaching deep into the hierarchy.

3. Unit Conversion and Formatting

Since the size is returned in bytes, you must divide by 1024 to convert it into more human-readable units like KB, MB, and GB.

KB: sizeInBytes / 1024
MB: sizeInBytes / 1024 / 1024
GB: sizeInBytes / 1024 / 1024 / 1024

The Format function is used to make the numbers easy to read with thousands separators.

Application: Listing Sizes for Each Subfolder

By applying this technique, you can create a practical macro that lists the total size of every subfolder within a parent directory directly onto a worksheet.

Sub ListSubfolderSizes()
    Dim fso As New FileSystemObject
    Dim parentFolder As Folder
    Dim subFld As Folder
    Dim i As Long
    
    Set parentFolder = fso.GetFolder(ThisWorkbook.Path)
    i = 2
    Range("A1:B1").Value = Array("Subfolder Name", "Total Size (MB)")
    
    ' Loop through all subfolders in the parent folder
    For Each subFld In parentFolder.SubFolders
        Cells(i, 1).Value = subFld.Name
        Cells(i, 2).Value = subFld.Size / 1024 / 1024 ' Display in MB
        i = i + 1
    Next subFld
    Columns("A:B").AutoFit
End Sub