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
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 NextRichard 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 NextRichard Mueller
Hi, can this be modified to only interrogate one file in the location, say ntuser.dat?