ExcelBanter - Folder size

A quick and dirty solution is shown below. Before running the code, go
to the Tools menu in VBA, choose References, and scroll down to
"Microsoft Scripting Runtime" and put a check next to that item. Then,
in the code change the line marked with <<<<< to the appropriate
folder name. This code lists the subfolders of StartFolderName with
their subfolder count, size in KB, and file count. It is not
recursive. That is, subfolders of subfolders of subfolders etc are not
listed separately.

I have a free add-in at http://www.cpearson.com/excel/FolderTree.aspx
that will give you a directory listing in almost any format and
including many details about folders, files, subfolders, subfolders of
subfolders, etc.

Sub Start()
Dim FSO As Scripting.FileSystemObject
Dim StartFolderName As String
Dim StartFolder As Scripting.Folder
Dim SubFolder As Scripting.Folder
Dim R As Range
Set R = Range("A1")
R.Value = "Folder Name"
R(1, 2).Value = "Folder Size"
R(1, 3).Value = "SubFolder Count"
Set R = Range("A2")
StartFolderName = "C:\CPearsonWeb" '<<<<<<<<<<<<<<<<<<<<<< CHANGE
Set FSO = New Scripting.FileSystemObject
Set StartFolder = FSO.GetFolder(StartFolderName)
R.Value = StartFolder.Path
R(1, 2).Value = Format(StartFolder.Size / 1024, "#,##0") & " KB"
R(1, 3).Value = Format(StartFolder.SubFolders.Count, "#,##0")
For Each SubFolder In StartFolder.SubFolders
Set R = R(2, 1)
R.Value = SubFolder.Path
R(1, 2).Value = Format(SubFolder.Size / 1024, "#,##0") & " KB"
R(1, 3).Value = Format(SubFolder.SubFolders.Count, "#,##0")
Next SubFolder
Range("A:C").EntireColumn.AutoFit
End Sub

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Thu, 20 Nov 2008 06:31:02 -0800, Naz
wrote:

Hi

I've been given the task of monitoring our folder share size and the effects
of our clear out exercise.
Is there any way in excel to create some code that will give the size of a
particular directory.
So for example if i had a directory c:\temp to find out the size of the
folder, and possible any other data e.g. number of files, files by type?
I know there are probably tools on the net to do this but unfortunately we
can't install any software on or machines, and doing it manually is sooo time
consuming.

All help is appreciated.