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.
Also Read: Adding A ToolTip To Any Cell In Excel
- Create a New Excel Workbook. Enter a Folder Path in Sheet1->Cell A1.
- Press Alt + F11. This will open the VB Editor.
- Copy paste the below code.
- 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.