Removing Empty Folder is Now Easy – Get Folder List with Size

Get Folder List with VBA Dir – Remove Empty Folders

To get the list of folders within a folder through VBA, it would have been nice if the command name is VBA Dir.

But it is not like that. We have to read it by creating a object of Filesystemobject. If it is going to be plain list, then there are plenty of easy methods over there in internet.

But, in this article we are also going to get the size of each folder also. Let’s see how to get it.

Remove Empty Directories – Is Easy Now

When we open Windows Explorer to view list of files and folders, it will only show File size, but not Folder Size. Try DIR command in CMD prompt and it is the same case there as well.

It will be a time consuming process to right click on each folder to check for its size. If it is a empty folder then we choose to delete it of keep it.

Here with this VBA Folder list code, we are also getting its size. Once we run this code, we get list of all folders with their size. Filter out folders with zero size and delete them. This will clear some unwanted placeholders easily.

VBA Scripting.FileSystemObject

1. Create a New Excel Workbook. Enter a Folder Path in Sheet1->Cell A1.
2. Press Alt + F11. This will open the VB Editor.
3. Copy paste the below code.
4. Run the code by Pressing F5. (Result will be printed in Sheet1 itself.)

Note: Before executing this code, include this reference “Microsoft Scripting Runtime” from Tools -> Reference. Refer MSDN for detailed information on FileSystemObject and Scripting Runtime.

Private Sub Find_Folder_List_From_Path()
    'Get Root Folder Name with Path
    'For Example: D:\FolderName
    Root_Folder_Path = ThisWorkbook.Sheets(1).Cells(1, 1)

    'Create File System Objects
    'To Use this FSO, Go to Tools -> References.
    'Find for “Microsoft Scripting Runtime” & Click the Checkbox.
    Dim oSystemFilesAndFolders As FileSystemObject
    Dim oFolder As Folder

    'Initialize Variables And Objects
    i = 1
    Set oSystemFilesAndFolders = New FileSystemObject
    ThisWorkbook.Sheets(1).Cells(i, 2) = "Folder"
    ThisWorkbook.Sheets(1).Cells(i, 3) = "Size (MB)"
    ThisWorkbook.Sheets(1).Cells(i, 4) = "Size (GB)"
    
    'Loop to get each Subfolder in the Root Path
    For Each oFolder In oSystemFilesAndFolders.GetFolder(Root_Folder_Path).SubFolders
        i = i + 1
        ThisWorkbook.Sheets(1).Cells(i, 2) = oFolder.Name
        
        'Get Folder Size
        On Error Resume Next
        'oFolder.Size returns value in Bytes.
        '1 KB = 1024 Bytes
        '1 MB = 1024 KB
        '1 GB = 1024 MB
        ThisWorkbook.Sheets(1).Cells(i, 3) = (oFolder.Size / 1024) / 1024
        ThisWorkbook.Sheets(1).Cells(i, 4) = ((oFolder.Size / 1024) / 1024) / 1024
    Next
End Sub

Once this code gets executed successfully, you will list of folders with size in sheet1.

This list will be useful to decide what to do with folders of huge size to analyze whether it has any unwanted huge old data etc., and also you can remove empty folders if they are not required anymore.

Alternate Methods to Get Folder List

If the folder listing has to be done only for one time, the DOS command can be used. Just a plain DIR command placed inside a Batch file is enough, that can be executed whenever required.

• To list Subfolder and files use “dir /b” or “dir/b > D:dir/dir_list.txt” or
• For more graphical view use “tree” command in dos prompt)

Both ‘dir’ or ‘tree’ commands can be used in same manner and their outputs can be directed to a text file.

If the folder list result is overflowing through the command prompt screen, then writing it to a text file and viewing it in a text editor would be the feasible one.