Create excel pie chart.
This script lists a directory and creates a pie chart in Excel with direcories > 10 MB. The directorie name is an argument on the command line. To start the script type:
cscript dirspace.vbs c:\
Dim objFS
Dim objFolder
Dim objXL
Dim objSubFolder
Dim objRng
Dim intLine
Const xl3DPie = -4102
Const xlColumns = 2
Const xlLocationAsNewSheet = 1
Const xlColorIndexNone = -4142
Const xlLineSttyleNone = -4142
Const xlDataLabelsShowValue = 2
Const xlDataLabelsShowPercent = 3
Const xlDescending = 2
Const xlTopToBottom = 1
Const xlBottom = -4107
Const xlGuess = 0
On Error Resume Next
Set objFs = WScript.CreateObject("Scripting.FileSystemObject")
'** get directory name from the commandline
Set objFolder = objFS.GetFolder(Wscript.Arguments(0))
If objFolder Is Nothing Then
MsgBox"Please enter a directory to list.", vbInformation, Wscript.Scriptname
Wscript.Quit
End if
'** start excel
Set objXl = Wscript.CreateObject("Excel.application")
If objXl Is Nothing Then
MsgBox"Excel cannot be started.", vbInformation, Wscript.Scriptname
Wscript.Quit
End if
With objXL
.visible= TRUE '** make excel visible
.workbooks.add '** add new workbook
for each objSubFolder in objFolder.SubFolders '** loop through directories
If Round((ObjSubFolder.Size / 1048576),2) > 10 Then '** if directory > 10mb
intLine = intLine + 1
.activeSheet.Cells(intLine,1).Value = ObjSubFolder.Name
.activeSheet.Cells(intLine,2).Value = Round((ObjSubFolder.Size / 1048576),0)
End If
Next
.activeSheet.UsedRange.Columns(2).Cells.NumberFormat = "#,##0"
Set objRng = objxl.Range("A:B")
objrng.sort Objxl.activeSheet.Columns("B"), xlDescending
objRng.Select
'** create pie chart
.charts.Add
With .activeChart
.ChartType = xl3DPie
.setSourceDate objXL.Sheets("Sheet1").UsedRange, xlColumns
.SeriesCollection(1).XValues = "=Sheet1!C1"
.location xlLocationAsNewSheet
.HasTitle = TRUE
.ChartTitle.Characters.Text = "Content of " & ObjFolder.Path
If objXL.Version = "10.0" Then
.HasLegend = False
.ApplyDataLabels,,,,,True,true,False
Else
.HasLegend = True
.Legend.Select
.Selection.Position = xlBottom
.ApplyDataLabels xlDataLabelsShowValue, false, true
End If
.plotArea.Interior.ColorIndex = xlColorIndexNone
.PlotArea.Border.LineStyule = xlLineStyleNone
.Legend.position = xlBottom
End With
End With
