'This script grabs the names and size subfolders of the defined subject folder. It places the info into an Excel
'document, sorts in descending folder size and autofits the cells. Now if you leave the script the way it is
'the script will not save the autofit because it creates the xls as a Tab deliminated file.
'The best way to run this script is to comment the Wscrpt.Echo "Done"; objExcel.Quit; and
'objWorkbook.Close TRUE statements out and then Save it as a new xls doc. BTW the Option Explicit is NOT needed.
Option Explicit
Const ForWriting = 2
Const CONVERSION_FACTOR = 1048576
Const xlDescending = 2
Const xlYes = 1
dim fso, fl, objFolder
dim colSubfolders, objSubfolder, objExcel, objWorkbook, objWorksheet
dim x
dim objRange, objRange2
dim strEndPoint
Set fso = CreateObject ("Scripting.FileSystemObject")
Set fl = fso.CreateTextFile ("e:\test6.xls", ForWriting, True)
fl.Close
Set objFolder = fso.GetFolder("E:\stuff")
Set colSubfolders = objFolder.Subfolders
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("e:\test6.xls")
Set objWorksheet = objWorkbook.Worksheets(1)
objExcel.Visible = True
objExcel.Cells(1, 1).Value = "Folder Name"
objExcel.Cells(1,2).Value = "Folder Size"
x = 1
For Each objSubfolder in colSubfolders
x = x+1
objExcel.Cells(x,1).Value = objSubfolder.Name
objExcel.Cells(x,2).Value = Convert(objSubfolder.size)
Next
strEndPoint = "B2:B" & x
Set objRange2 = objExcel.Range(strEndPoint)
Set objRange = objWorksheet.UsedRange
objRange.EntireColumn.Autofit()
objRange.Sort objRange2,xlDescending
'the objExcel.Quit is not needed if you get rid of the Echo "Done" statement.
objWorkbook.Close TRUE
objExcel.Quit
WScript.Echo "Done"
Function Convert(SIZE)
If SIZE =< 0 Then
Convert = 0
Else
Convert = SIZE / CONVERSION_FACTOR
End If
End Function
