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:
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...