I am looking to build a script to run against a network folder containing a number of files / folders and export out preferably to a spreadsheet the last modified date of the files.

Being the main folder contains a number of sub folders which contain additional folders and files, I may have to run something on each folder, but figured if I could get in an Excel format I could do the sorting in the spreadsheet itself.

I have written a few scripts to remove or move files older then a certain date, but never one to just report the date to a spreadsheet.

Any help would be greatly appreciated

techmerlin.. do you want to run a vbs that outputs to a .csv or do you want to run some vba within excel itself?

In PowerShell:

Get-ChildItem <path> -Recurse |Where-Object {!$_.PSIsContainer} |Select-Object @{Name="Name";Expression={$_.VersionInfo.FileName}}, LastWriteTime |Export-Csv test.csv -NoTypeInformation

Karl

I would use the FileSystemObject in a VBScript program because it is faster than WMI. I would output in a comma delimited format. The output can then be redirected to a text file that can be read by Excel. In the example below the .Path property is the full path and name of each file. If you prefer, you can output .Name, which is just the file name.

Option Explicit

Dim strFolder, objFSO, objFOlder

' Specify main folder.
strFolder = "c:\MyFolder"

' Retrieve folder object.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strFolder)

' Enumerate files.
Call GetFiles(objFolder)

Sub GetFiles(ByVal objParent)
  ' Enumerate files in folder.
  Dim objFile, objChild

  For Each objFile In objParent.Files
    Wscript.Echo objFile.Path & "," & objFile.DateLastModified
  Next

  ' Recurse through nested folders.
  For Each objChild In objParent.SubFolders
    Call GetFiles(objChild)
  Next
End Sub

As usual, such a script should be run at a command prompt so the output can be redirected to a text file. For example, if the VBScript program is saved in EnumFiles.vbs, the command could be:

cscript //nologo EnumFiles.vbs > Files.csv

This assumes you are in the folder where the file EnumFiles.vbs is saved. Otherwise you must specify the full path to the file. The file Files.csv is created in the current folder.

Richard Mueller

Richard,

 

this might work for what I need, let me play around with it. I did not think about running the script in a command prompt output to the file.. good call..!!

NastyMatt,

 

Sorry, I was looking to run a VB script to output to excel.... shoudl have said that :-)

It's not too much work to have the script write directly to Excel. For example:

Option Explicit

Dim strFolder, objFSO, objFolder
Dim strExcelPath, objExcel, objSheet, intRow

Const xlExcel7 = 39

' Specify main folder.
strFolder = "c:\MyFolder"

' Retrieve folder object.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strFolder)

' Specify spreadsheet to be created.
strExcelPath = "c:\MyFolder\Files.xls"

' Create workbook.
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add

' Bind to worksheet.
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

' Enumerate files.
intRow = 0
Call GetFiles(objFolder)

' Save spreadsheet, close workbook, and quit Excel.
objExcel.ActiveWorkbook.SaveAs strExcelPath, xlExcel7
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit

' Alert user.
Wscript.Echo "Done"

Sub GetFiles(ByVal objParent)
  ' Enumerate files in folder.
  ' Variable intRow must have global scope.
  Dim objFile, objChild

  For Each objFile In objParent.Files
    intRow = intRow + 1
    objSheet.Cells(intRow, 1).Value = objFile.Path
    objSheet.Cells(intRow, 2).Value = objFile.DateLastModified
  Next

  ' Recurse through nested folders.
  For Each objChild In objParent.SubFolders
    Call GetFiles(objChild)
  Next
End Sub

 

Richard Mueller

Richard,

one thing further, this does look like it is doing what I need, could this be modified to only grab files older then a certain date, or number of days (e.g. 365 days, or 1 year)??

 

thanks

Yes. I assume you want to restrict the output based on the DateLastModified property of the file object, but you could also use the DateCreated property. You would just add an "If" statement in the "For Each/Next" structure. For example:

  For Each objFile In objParent.Files
    If (DateDiff("d", objFile.DateLastModified, Now()) > 365) Then
      intRow = intRow + 1
      objSheet.Cells(intRow, 1).Value = objFile.Path
      objSheet.Cells(intRow, 2).Value = objFile.DateLastModified
    End If
  Next

Richard Mueller

Richard,

SO it works great, but seems I am hitting the 16384 row limit in Excel which is causing me grief... I will have to fool around wiht it a bit and see if I can figure a different way to do this.

 

 

Excel 2007 has a limit of over 1,000,000 rows. Another option might be to use more columns, maybe 2 or 3 files per row. For example (not tested):

 intCol = 1
 For Each objFile In objParent.Files
  If (DateDiff("d", objFile.DateLastModified, Now()) > 365) Then
   Select Case intCol
    Case 1
     intRow = intRow + 1
     objSheet.Cells(intRow, 1).Value = objFile.Path
     objSheet.Cells(intRow, 2).Value = objFile.DateLastModified
    Case 2
     objSheet.Cells(intRow, 3).Value = objFile.Path
     objSheet.Cells(intRow, 4).Value = objFile.DateLastModified
    Case 3
     objSheet.Cells(intRow, 5).Value = objFile.Path
     objSheet.Cells(intRow, 6).Value = objFile.DateLastModified
     intCol = 0
   End Select
   intCol = intCol + 1
  End If
 Next

Richard Mueller

Hi, can this be modified to only interrogate one file in the location, say ntuser.dat?