Directory size to Excel

At work I was asked to write a script that would take disk size and export it to a spreadsheet. That spreadsheet would then give average monthly directory size. I chose to do this in VB script. If you know any other scripting or programming language, VB script comes easy. Download diskuse and place it in same directory as diskuse.vbs script. Run diskuse.vbs from command prompt and output will be placed in this Excel file.

/* diskuse.vbs*/

Dim sheetNumber
Dim usedRows
Dim currentRow
Dim lastDate
dim folder
Dim file
Dim fsObject
Dim size
Dim sizeMB

sheetNumber = Month(Date)
‘File System object
set fsObject = createobject(“Scripting.FileSystemObject”)

‘Get present working directory path
folder = fsObject.GetAbsolutePathName(“.”)

‘ now get the bytes of folder and parse it to size
size = fsObject.GetFolder(folder).Size
‘Given size is in bytes. Change it to MB.
sizeMB = (size/1024)/1024
‘———Open Excel workbook————
Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(folder&”diskuse.xls”, 2, false) ‘(path,Do not update links, readOnly=false)
Set dataSheet = objWorkbook.Worksheets(sheetNumber)

objExcel.Application.Visible = False

‘———Open worksheet for this month———
objWorkbook.WorkSheets(sheetNumber).Activate

‘———get number of occupied rows———

usedRows = dataSheet.UsedRange.Rows.Count

‘If this script already ran today, overwrite last used row
lastDate = objExcel.Cells(usedRows, 3).Value

If lastDate = Date() Then
currentRow = usedRows
else
currentRow = usedRows+1
End If

‘———Append data———
objExcel.Cells(currentRow, 1).Value = folder
objExcel.Cells(currentRow, 2).Value = sizeMb
objExcel.Cells(currentRow, 3).Value = Date()
objExcel.Cells(currentRow, 4).Value = Time()

‘———Save and close workbook———

objWorkbook.save()
‘———exit excel———
objExcel.Application.Quit
msgbox(“Done at ” & Time())
WScript.Quit

The author would love to hear your comments. Let us discuss at Twitter @qasimchadhar.