Folder and File Lister

traversd · May 22, 2003

Does anyone have an existing App or code for listing all the Folders in a directory by Size ?

My C:\ Is full and I want to identify the biggest folders for deleting files form....

Cheers
Darren

AJ · May 22, 2003

I thought when I started looking at this post that the answer would be fairly easy but it seems it's a little more complicated than that!

After a trawl through my knowledge and a major cobbling together of the VBA help available in Excel, here is an Excel macro that will do what you ask.

Copy this into a standard module, change (if necessary) the path you wish to interrogate and this should add a new workbook and place in Column A all the folder names and Column B, the respective sizes.

I have a feeling that the code is rather sloppy and I'm not really using the variables in the most efficient way. It works though so on that score - job done!

Cheers
AJ

Sub Folderinformation()

' Define variables
 Dim myPath As String, myFolder As String
 Dim myCounter As Long
 Dim fs, f

' Add new workbook for output of data and throw in some headings
 Workbooks.Add
 Range("A1").Value = "Folder Name"
 Range("B1").Value = "Folder Size"

' Specify variables
 myPath = "C:\"
 myCounter = 2
 Set fs = CreateObject("Scripting.FileSystemObject")

' Select specified drive and path
 ChDir myPath

' Get first entry
 myFolder = Dir(myPath, vbDirectory)

' Start looping through all folders
 Do While myFolder <> ""

' Make sure the current directory and the encompassing directory are ignored
 If myFolder <> "." And myFolder <> ".." Then

' Make sure MyFolder is actually a directory as the Dir command
' with vbDirectory also specifies files as well...
 If (GetAttr(myPath & myFolder) And vbDirectory) = vbDirectory Then

' Spit out folder name and size
 Cells(myCounter, 1) = myFolder
 Set f = fs.GetFolder(myFolder)
 Cells(myCounter, 2) = f.Size

' Increment the counter so the data collected goes on the next row in the output
 myCounter = myCounter + 1
 End If
 End If

' Get next one and carry on
 myFolder = Dir

 Loop

End Sub

traversd · May 22, 2003

AJ,

That worked like a charm.....

How about a further challenge ? How to get it to search the subdirectories and look at the folders within those as well ?

The idea is to give a quick overview of which folders can be targeted quickly.

i.e. I know my TRW folder is the biggest in the Root directory C:, but I also know there's nowt I can delete in there !

Regards
Darren

goblin · May 22, 2003

Here is a modification of a sub I had. You may have to fool around some with the offsets if you don't like the layout

Code:

Sub DetermineFolderSizes()
    Dim wbNew As Workbook
    Dim r As Range
    Dim startFolder As String
    
    startFolder = "c:\"
    
    Set wbNew = Workbooks.Add
    wbNew.Activate
    Set r = ActiveSheet.Cells(1, 1)
    
    FolderSizes startFolder, r
End Sub

Function FolderSizes(folder As String, r As Range) As Long
    Dim fs, f, f1, fc, sfc, sf, s
    Dim mySize As Range
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(folder)
    Set sfc = f.Subfolders
    Set fc = f.Files
    
    r.Value = folder
    Set r = r.Offset(0, 1)
    Set mySize = r
    mySize.Value = 0
    
    For Each sf In sfc
        Set r = r.Offset(1, 0)
        mySize.Value = mySize.Value + FolderSizes(sf.Path, r)
    Next
    
    For Each f1 In fc
        mySize.Value = mySize.Value + FileLen(f1)
    Next
    
    Set r = r.Offset(0, -1)
    FolderSizes = mySize.Value
End Function

How it helps,
Goblin

goblin · May 22, 2003

Hmmm, just ran than thing I posted above, and it doesn't quite cover todays HDD. Recommend dividing the filelength of each file by 1024 (KB), or even 1024^2 (MB)

Goblin

Juan Pablo González · May 22, 2003

Another option....

Option Explicit

Dim FSO As Object 'FileSystemObject

Sub Test()
 Dim Ar() As Variant

 Set FSO = CreateObject("Scripting.FileSystemObject") 'New FileSystemObject

 'Constansts, change to do what you want
 Const Folder As String = "C:\"
 Const SearchSubFolders As Boolean = True

 ReDim Ar(1 To 2, 1 To 1)
 Ar(1, 1) = "Folder Name"
 Ar(2, 1) = "Folder Size"
 GetFolderInfo Ar, Folder, SearchSubFolders
 Set FSO = Nothing
 Range("A1").Resize(UBound(Ar, 2), 2).Value = Application.Transpose(Ar)
End Sub

Sub GetFolderInfo(ByRef Ar As Variant, _
 ByVal Folder As String, _
 ByVal SearchSubFolders As Boolean)
 Dim Fl As Object 'Folder
 Dim SubF As Object 'Folder
 Dim i As Long

 Set Fl = FSO.GetFolder(Folder)
 i = UBound(Ar, 2) + 1
 ReDim Preserve Ar(1 To 2, 1 To i)
 If Fl.IsRootFolder Then
 Ar(1, i) = Fl.Path
 Else
 Ar(1, i) = Fl.Path
 Ar(2, i) = Fl.Size / 1024 'Kb
 End If
 If SearchSubFolders Then
 For Each SubF In Fl.SubFolders
 GetFolderInfo Ar, SubF.Path, SearchSubFolders
 Next SubF
 End If
 Set Fl = Nothing
End Sub

I divide by 1024, but as goblin pointed, you may want to divide by 1024^2

onisdad · May 3, 2004

On the FolderInformation Sub, if I use a UNC as myPath then I get
a run-time error 76 "Path not found' ... line
Set f = fs.GetFolder(myFolder)

I only need to look at folders (and their size) within a directory.

Best Regards

Folder and File Lister

traversd

Board Regular

Excel Facts

AJ

Active Member

traversd

Board Regular

goblin

Active Member

goblin

Active Member

Juan Pablo González

MrExcel MVP

onisdad

New Member

Similar threads

Forum statistics

Share this page

Folder and File Lister

traversd

Board Regular

Excel Facts

AJ

Active Member

traversd

Board Regular

goblin

Active Member

goblin

Active Member

Juan Pablo González

MrExcel MVP

onisdad

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock