Sometimes, we need list of system folder and their size in MB. This is not a regular task, but it takes a lot of manual effort when such kind of requirement occurs. Thus, it's need a automation which will give us list of all subfolders and their size. Fortunately, this task can be automated using a small excel macro which will provide list of all subfolder and their size.









Using excel macro this task will be made so easy . Which will provide list and size of each sub irrespective of number folders in provided path in macro code.

Here is the code which will list all folder and their size in MB:

Sub Get_Folder_Size_Details()
' Author: Dreams24
' Written for VBA Tricks and tips blog
' http://vbatricksntips.com

    Dim FSO As FileSystemObject
    Dim Folder_Name As Folder

 'Assign path of folder for which you want it's sub folder list and size
    Root_Path = "C:\Desktop\Test Folder"
    ThisWorkbook.Sheets(1).Range("A2:D100").ClearContents

    'Initialize Variables And Objects
    i = 1
    Set FSO = New FileSystemObject

    'Loop to get each Subfolder in the Root Path
    For Each Folder_Name In FSO.GetFolder(Root_Path).SubFolders
        i = i + 1
        ThisWorkbook.Sheets(1).Cells(i, 1) = Folder_Name.Name
        On Error Resume Next
        'Folder_Name.Size returns value in Bytes. Thus divided by 1021 to convert in MB
        ThisWorkbook.Sheets(1).Cells(i, 2) = (Folder_Name.Size / 1024) / 1024
    Next

    MsgBox "All folders listed with their size"
End Sub

Share this post if you think this is really useful...