The VBScript Network and Systems Administrator's Cafe

May 14 2008   8:58PM GMT

Getting disk usage data with the filesystem object and the Excel.Application object in VBScript

Jerry Lees Jerry Lees Profile: Jerry Lees

In my previous posting, entitled Working with the Excel.Application object in VBScript to create Excel Spreadsheets, we worked with excel in VBScript to create a excel spreadsheet. The spreadsheet wasn’t going to win any awards for complexity or usefulness, but none the less it was a spreadsheet– and more importantly it was generated by a script! I also promised to bring you a script to help you chart disk space usage in my next posting in the series. This posting is the fulfilment of that promise! Also, please note that you will need Excel installed where the script runs for this script to operate correctly– but it does not need to be installed on the system you are pulling disk free space information from since I used the WMI object instead of the filesystem object.

The script below uses WMI’s Win32_LogicalDisk class to grab the drives in the target system, specifically the drive you specify through the use of a where clause in the SQL statement that pulls back the WMI data. (Through the “where deviceid like” section of the SQL statement in the code)

 Also, I didn’t put a lot of effort into the code where the for/next loop is that loops through getting and saving the free space because I didn’t want to create a lot of extra complexity and wanted to create a script that would run through to completion pretty quickly. Currently, the script takes 10 minutes to complete. To test the script while it’s running, just create files in a folder and delete them a number of times. I created a 10Mb and 20Mb file and made a series of copy/pastes during execution– with a smattering of deletes in the mix.

For further customization, you can look at the code from the posting I wrote a while back Reporting CPU usage by saving it to a file with the VBScript filesystem object to get a good feel for how to modify the loop to get what you want. In essence, change the number 2 in “Wscript.Sleep 2” to a lager number to get a bigger gap between samples and change the 300 in the “For x = 1 to 300″ line to a larger number to get a longer sample period.

Here is the code:
Dim Freespace, CurrentRow, ServerName
Const xlSaveChanges = 1

‘the first row in excel is 1 (not 0)
CurrentRow = 1

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = False
For x = 1 to 300 ‘ change 300 to increase your sample duration
     objExcel.Cells(CurrentRow, 1).Value = GetFreeSpace(“C:”,ServerName)
     CurrentRow = CurrentRow + 1
     Wscript.sleep 2 ‘change this to spread out your samples

objExcel.ActiveWorkbook.SaveAs (“C:\excelvbscript.xls”)

Function GetFreeSpace(Drive,strComputer)
     Set objWMIService = GetObject(“winmgmts:{impersonationLevel=impersonate}!\\”_
         & strComputer & “\root\cimv2”)
     Set colDisks = objWMIService.ExecQuery (“Select * from Win32_LogicalDisk where deviceid like “_
         &chr(34) & Drive & chr(34))
     For Each objDisk in colDisks
         GetFreeSpace = (objDisk.freespace/1024)/1024 ‘ get MB free
End Function

As always, this code works perfectly. However, sometimes the formatting of the blog breaks the code if you copy and paste it into your editor. So, if you’d like to not type or troubleshoot any syntax errors due to the copy and paste problems– I’ve provided the code for download, plus example output files  from my final tests for you. You’ll find the code and other files available for download from my website’s ( File Depot under the ITKE Blog Scripts category. Enjoy and happy scripting!

 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: