Excel.Application

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

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 diskspace 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 teh 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
ServerName=”.”

Set objExcel = CreateObject(”Excel.Application”)
objExcel.Visible = False
objExcel.Workbooks.Add
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
next

objExcel.ActiveWorkbook.SaveAs (”C:\excelvbscript.xls”)
objExcel.Quit
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
     Next
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 (www.webstemsadministration.com) File Depot under the ITKE Blog Scripts category. Enjoy and happy scripting!

Working with the Excel.Application object in vbscript to create Excel Spreadsheets

In my previous two postings we discussed the Microsoft Word.Application object (here and here) to use as a logging mechanism and as a documentation mechanism for our scripts we’re writing.

But what about if you wanted to log numbers, like in the performance counter posts I made a few weeks back? I’ve also seen several questions about how to do use excel in vbscript. Well, it’s super easy, once you get the object understood. The next few postings I’ll focus on this great way to save data in a format that is easily shareable and can easily be made into pretty pie charts and such for the boss.

First off I’ll give you a simple example that basically creates the object (not visible), adds a new workbook to it, adds a string to cell 1,1, and finally saves it before it quits. Below is the example code for this scriptlet:

Const xlSaveChanges = 1

Set objExcel = CreateObject(”Excel.Application”)
objExcel.Visible = False
objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value = “Test value”
objExcel.ActiveWorkbook.SaveAs (”C:\excelvbscript.xls”)
objExcel.Quit

I’ll dig deeper right after I go back and explain the Select Case statement I used in a previous posting– which I neglected to mention previously or explain in the posting. (Bad, Jerry, Bad Bad.) Hwever, after that I promis to come back with a more specific example of Excel.Application, but this should give you a little bit to play with in the mean time.

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 XML files and Generated Word Documents for you. You’ll find the code available for download from my website’s (www.webstemsadministration.com) File Depot under the ITKE Blog Scripts category. Enjoy and happy scripting!