none
VBScript to get number of files by type and size of files by type

    Question

  • We would like to run a script on a folder that would:

    1 - List folders/subfolders and report the size of each one - We've done that

    2 - Count the number of files for common file types (e.f. .doc, .xls, ppt etc ) specified in maybe an array) in each folder and report thte total number for each folder and the size

    3 - Output results to excel

    E.G Root folder has 2 folders each has 2 sub folders. They contain a mix of files doc, xls, ppt, txt etc. Script runs and reports the following in Excel

    Folder1/sub-foldera - Total size 245MB - DOC 23 Files - 34MB - XLS 134 Files - 134MB etc etc

    Folder1/sub-folderb

    Folder2/sub-foldera

    Folder1/sub-folderb

    I already have a script to output to excel just need somehelp with the file type/number/sizes

     

    Sub CheckFolder(objCurrentFolder)
    
    	on error resume next
    
      For Each objFolder In objCurrentFolder.SubFolders
    
       FolderSize = objFolder.Size
    
       Tmp = (FormatNumber(FolderSize, 2, , , 0)/1024)/1024
    
       ObjXL.ActiveSheet.Cells(icount,2).Value = objFolder.Path
    
       ObjXL.ActiveSheet.Cells(icount,1).Value = Tmp
    
       icount = icount + 1
    
      Next
    
    
    
      'Recurse through all of the folders
    
      For Each objNewFolder In objCurrentFolder.subFolders
    
        CheckFolder objNewFolder
    
      Next
    
      
    
    End Sub
    
    
    
    Dim sCurPath
    
    Dim response
    
    sCurPath = CreateObject("Scripting.FileSystemObject").GetAbsolutePathName(".")
    
    response = msgbox("Current directory is: " & sCurPath & "Run Script?", 4, "Folder Calculator")
    
    if response = vbNo then
    
    WScript.Quit 
    
    end if
    
     outputfile = "Z:\foldersize_" & Day(now) & Month(now) & Year(now) & ".xls"
    
    
    
     Set fso = CreateObject("scripting.filesystemobject")
    
     if fso.fileexists(outputfile) then fso.deletefile(outputfile)
    
    
    
    'Create Excel workbook
    
     set objXL = CreateObject( "Excel.Application" )
    
     objXL.Visible = False
    
     objXL.WorkBooks.Add
    
    
    
    'Counter 1 for writing in cell A1 within the excel workbook
    
     icount = 1
    
    
    
    'Run checkfolder
    
     CheckFolder (FSO.getfolder(sCurPath))
    
    
    
    
    
    
    
    'Lay out for Excel workbook 
    
     objXL.Range("A1").Select
    
     objXL.Selection.EntireRow.Insert
    
     objXL.Selection.EntireRow.Insert
    
     objXL.Selection.EntireRow.Insert
    
     objXL.Selection.EntireRow.Insert
    
     objXL.Selection.EntireRow.Insert
    
     objXL.Selection.EntireRow.Insert
    
     objXL.Selection.EntireRow.Insert
    
     objXL.Selection.EntireRow.Insert
    
     objXL.Selection.EntireRow.Insert
    
    
    
     objXL.Columns(1).ColumnWidth = 21
    
     objXL.Columns(2).ColumnWidth = 21
    
     objXL.Columns(1).NumberFormat = "#,##0.0"
    
     objXL.Range("A1").NumberFormat = "d-m-yyyy"
    
     objXL.Range("A1:B9").Select
    
     objXL.Selection.Font.Bold = True
    
     objXL.Range("A1:B5").Select
    
     objXL.Selection.Font.ColorIndex = 5
    
     objXL.Range("B2").Select
    
     objXL.Selection.Font.Italic = True
    
     objXL.Selection.Font.Size = 16
    
     ObjXL.ActiveSheet.Cells(1,2).Value = "FolderSize and Folder Structure" 
    
     ObjXL.ActiveSheet.Cells(1,1).Value = Day(now) & "-" & Month(now) & "-"& Year(now)
    
     ObjXL.ActiveSheet.Cells(2,1).Value = UCase(rootfolder)
    
     ObjXL.ActiveSheet.Cells(3,1).Value = "Folders are separated by --> so they can be seen more easily"
    
     objXL.ActiveSheet.Cells(5,1).Value = "Total FolderSize (MB)"
    
     objXL.Range("B5").Select
    
     objXL.Selection.HorizontalAlignment = -4108
    
     objXL.Selection.Font.ColorIndex = 3
    
     objXL.Selection.FormulaR1C1 = "=SUM(R[5]C[-1]:R[6000]C[-1])"' ADD UP FILE SIZES
    
     objXL.ActiveSheet.Cells(6,1).Value = "COST @ 62.5p per MB ="
    
     objXL.Range("B6").Select
    
     objXL.Selection.HorizontalAlignment = -4108
    
     objXL.Selection.Font.ColorIndex = 3
    
     objXL.Selection.NumberFormat = "£#,##0.0"
    
     objXL.ActiveCell.FormulaR1C1 = "=SUM(R[-1]C*0.625)"
    
     objXL.Range("A9:B9").Select
    
     objXL.Selection.Font.Bold = True
    
     ObjXL.ActiveSheet.Cells(9,1).Value = "Total (MB)"
    
     ObjXL.ActiveSheet.Cells(9,2).Value = "Folder"
    
     
    
     objXL.Range("B:B").Replace rootfolder, ""' get rid of the \\IRF etc bit 
    
     objXL.Range("B:B").Replace "\", "-->" ' make it a bit easier to spot the folders
    
    
    
    'Finally close the workbook
    
     ObjXL.ActiveWorkbook.SaveAs(outputfile)
    
     ObjXL.Application.Quit
    
     Set ObjXL = Nothing
    
    
    
    'Message when finished
    
     Set WshShell = CreateObject("WScript.Shell")
    
     Finished = Msgbox ("Script executed successfully, results can be found in " & Chr(10) _
    
          & outputfile & "." & Chr(10) & Chr(10) _
    
          & "Do you want to view the results now?", 65, "Script executed successfully!")
    
     if Finished = 1 then WshShell.Run "excel " & outputfile
    
    
    Tuesday, November 02, 2010 1:49 PM

Answers

  • It wasn't as easy as I thought it was going to be, but this works. Let me know if you need an explanation.

     

     

    Dim fso, folder, files, NewsFile,sFolder
    Set fso = CreateObject("Scripting.FileSystemObject")
    sFolder = "C:\temp"
    Set folder = fso.GetFolder(sFolder)
    Set files = folder.Files
    TotalMatches = 0
    Dim arrFileTypes()
     
    For Each folderIdx In files
    	wscript.echo "================================================"
    	Matches = 0
    	wscript.echo "File Name: " & folderIdx.Name
    	pieces = Split(folderIdx, ".")
    	idxLast = UBound( pieces )
    	fileextension = pieces(idxLast)
    	wscript.echo "File Extension: " & fileextension
    	For Each filetype in arrFileTypes
    		wscript.echo "filetype = " & filetype 
    		If filetype = fileextension Then
    			wscript.echo "Match Found (Line 20)"
    			Matches = Matches + 1
    		End If
    	Next
    	If Matches = 0 Then
    		wscript.echo "Matches = 0"
    		wscript.echo "Adding to array: " & fileextension
    		wscript.echo "TotalMatches = " & TotalMatches
    		ReDim Preserve arrFileTypes(TotalMatches)
    		arrFileTypes(TotalMatches) = fileextension
    		wscript.echo UBound(arrFileTypes)
    		TotalMatches = TotalMatches + 1
    	End If
    Next
    wscript.echo "================================================"
    wscript.echo UBound(arrFileTypes)
    wscript.echo "================================================"
    
    
    For Each filetype in arrFileTypes
    	strComputer = "."
    	Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    	Set colFiles = objWMIService.ExecQuery ("Select * from CIM_Datafile Where Extension = '" & filetype & "' AND path = '\\temp\\'")
    	wscript.echo "There are " & colFiles.Count & " with the extension " & filetype
    Next

     

     

    Justin Russell | Newtek Web Hosting

    Thursday, November 04, 2010 12:00 AM

All replies

  • It wasn't as easy as I thought it was going to be, but this works. Let me know if you need an explanation.

     

     

    Dim fso, folder, files, NewsFile,sFolder
    Set fso = CreateObject("Scripting.FileSystemObject")
    sFolder = "C:\temp"
    Set folder = fso.GetFolder(sFolder)
    Set files = folder.Files
    TotalMatches = 0
    Dim arrFileTypes()
     
    For Each folderIdx In files
    	wscript.echo "================================================"
    	Matches = 0
    	wscript.echo "File Name: " & folderIdx.Name
    	pieces = Split(folderIdx, ".")
    	idxLast = UBound( pieces )
    	fileextension = pieces(idxLast)
    	wscript.echo "File Extension: " & fileextension
    	For Each filetype in arrFileTypes
    		wscript.echo "filetype = " & filetype 
    		If filetype = fileextension Then
    			wscript.echo "Match Found (Line 20)"
    			Matches = Matches + 1
    		End If
    	Next
    	If Matches = 0 Then
    		wscript.echo "Matches = 0"
    		wscript.echo "Adding to array: " & fileextension
    		wscript.echo "TotalMatches = " & TotalMatches
    		ReDim Preserve arrFileTypes(TotalMatches)
    		arrFileTypes(TotalMatches) = fileextension
    		wscript.echo UBound(arrFileTypes)
    		TotalMatches = TotalMatches + 1
    	End If
    Next
    wscript.echo "================================================"
    wscript.echo UBound(arrFileTypes)
    wscript.echo "================================================"
    
    
    For Each filetype in arrFileTypes
    	strComputer = "."
    	Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    	Set colFiles = objWMIService.ExecQuery ("Select * from CIM_Datafile Where Extension = '" & filetype & "' AND path = '\\temp\\'")
    	wscript.echo "There are " & colFiles.Count & " with the extension " & filetype
    Next

     

     

    Justin Russell | Newtek Web Hosting

    Thursday, November 04, 2010 12:00 AM
  • Thank you

    I am trying this now and will update in due course

    Monday, November 08, 2010 1:41 PM
  • Hi Jusin,

     

    I ran the script but it doesn't really do what I want, although it gives me some ideas.


    I want a report (in excel) of the number of file types in the folder, (including subfolders) and the total size of the results:

    e.g Folder ->

    134 doc ->14.5MB

    156 xls -> 26MB

    267 ppt -> 156MB

    and so on.

    The script I'm using will report the size of folders with a recursive funtion, so can that be adapted to incude getting the size of file types I wonder?

    Wednesday, November 10, 2010 8:43 PM