VBScript to List Last Modified File of all Subfolders in a Directory

I created the code in this prior solution to be run from Excel; it produces the result in an Excel worksheet:

https://www.experts-exchange.com/questions/25063189/file-listing-in-Excel-VBA-Script.html?anchorAnswerId=26340696#a26340696

Note that it will not work for 64 bit OS

It looks like this script is going to list all files in all subdirectories. However, I just need to have the last file modified listed with the date that it was modified on.

Thanks!

Ah, I see :)

Option Explicit

' Code based on procedures found at:
' http://www.exceltip.com/show_tip/Files,_Workbook,_and_Worksheets_in_VBA/
' List_files_in_a_folder_with_Microsoft_Scripting_Runtime_using_VBA_in_Microsoft_Excel/446.html

Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias _
    "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long
Private Declare Function SHBrowseForFolder Lib "shell32.dll" Alias _
    "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long

Public Type BROWSEINFO
  hOwner As Long
  pidlRoot As Long
  pszDisplayName As String
  lpszTitle As String
  ulFlags As Long
  lpfn As Long
  lParam As Long
  iImage As Long
End Type

Private Function GetDirectory(Optional Msg) As String
    Dim bInfo As BROWSEINFO
    Dim path As String
    Dim r As Long
    Dim x As Long
    Dim pos As Integer

' Root folder = Desktop
    bInfo.pidlRoot = 0&

' Title in the dialog
    If IsMissing(Msg) Then bInfo.lpszTitle = "Select a folder." Else bInfo.lpszTitle = Msg

' Type of directory to return
    bInfo.ulFlags = &H1

' Display the dialog
    x = SHBrowseForFolder(bInfo)

' Parse the result
    path = Space$(512)
    r = SHGetPathFromIDList(ByVal x, ByVal path)
    If r Then
        pos = InStr(path, Chr$(0))
        GetDirectory = Left(path, pos - 1)
    Else
        GetDirectory = ""
    End If

End Function

Sub DoListFilesInFolder()
    
    Dim CheckPath As String
    Dim Msg As Byte
    Dim Drilldown As Boolean
    
    CheckPath = GetDirectory()
    
    If CheckPath = "" Then
        MsgBox "No folder was selected.  Procedure aborted.", vbExclamation, "StaffSmart Add-In"
        Exit Sub
    End If
    
    Msg = MsgBox("Do you want to list all files in descendant folders, too?", _
        vbInformation + vbYesNo, "Drill-Down")
    If Msg = vbYes Then Drilldown = True Else Drilldown = False
    
    Workbooks.Add ' create a new workbook for the file list
    ActiveWindow.Zoom = 75
    ' add headers
    With Range("A1")
        .Formula = "Folder contents: "
        .Font.Bold = True
        .Font.Size = 12
    End With
    Range("A3").Formula = "Folder:"
    Range("B3").Formula = "File Name:"
    Range("C3").Formula = "File Size:"
    Range("D3").Formula = "File Type:"
    Range("E3").Formula = "Date Created:"
    Range("F3").Formula = "Date Last Accessed:"
    Range("G3").Formula = "Date Last Modified:"
    Range("H3").Formula = "Attributes:"
    Range("A3:H3").Font.Bold = True
    ListFilesInFolder CheckPath, Drilldown
    ' list all files included subfolders

    Range("a4").Select
    ActiveWindow.FreezePanes = True
    Range("a3").Sort Key1:=Range("A4"), Order1:=xlAscending, Key2:=Range("B4"), _
        Order2:=xlAscending, Header:=xlYes
    Range("H3").AddComment
    With Range("H3").Comment
        .Visible = False
        .Text Text:="The file attribute can have any of the following values " & _
            "or any logical combination of the following values:" & Chr(10) & _
            "0 = Normal file. No attributes are set. " & Chr(10) & _
            "1 = Read-only file" & Chr(10) & _
            "2 = Hidden file" & Chr(10) & _
            "4 = System file" & Chr(10) & ""
        .Text Text:=Chr(10) & "8 = Disk drive volume label " & Chr(10) & _
            "16 = Folder or directory " & Chr(10) & _
            "32 = File changed since last backup " & Chr(10) & _
            "64 = Link or shortcut " & Chr(10) & _
            "128 = Compressed file" & Chr(10) & Chr(10) & _
            "For example, 3 = Read-only and Hidden", Start:=200
        .Shape.Height = 200
        .Shape.Width = 144
    End With
    
    Range("a1") = Range("a1").Value & CheckPath & IIf(Drilldown, " (with descendants)", _
            " (without descendants)")
    Range("a3").Select
    
    ActiveWindow.LargeScroll Up:=100
    
    MsgBox "Done", vbOKOnly, "StaffSmart Add-In"
    
End Sub

Private Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)

' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:\FolderName\", True

    Dim FSO As Object 'Scripting.FileSystemObject
    Dim SourceFolder As Object 'Scripting.Folder
    Dim SubFolder As Object 'Scripting.Folder
    Dim FileItem As Object 'Scripting.File
    Dim r As Long
    Dim LastDate As Date
    Dim LastFileItem As Object 'Scripting.File
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
    
    For Each FileItem In SourceFolder.Files
        If FileItem.DateLastModified > LastDate Then
            LastDate = FileItem.DateLastModified
            Set LastFileItem = FileItem
        End If
    Next
    
    r = Range("A65536").End(xlUp).Row + 1
        
    ' display file properties
    If Not LastFileItem Is Nothing Then
    Cells(r, 1).Formula = LastFileItem.ParentFolder.path '& FileItem.Name
    Cells(r, 2).Formula = LastFileItem.Name
    Cells(r, 3).Formula = LastFileItem.Size
    Cells(r, 4).Formula = LastFileItem.Type
    Cells(r, 5).Formula = LastFileItem.DateCreated
    Cells(r, 6).Formula = LastFileItem.DateLastAccessed
    Cells(r, 7).Formula = LastFileItem.DateLastModified
    Cells(r, 8).Formula = LastFileItem.Attributes
    End If
    
' If "descendant" folders also get their files listed, then sub calls itself recursively

    If IncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
            ListFilesInFolder SubFolder.path, True
        Next SubFolder
    End If
    
    Columns("A:H").AutoFit
    
    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing
    
End Sub

Select allOpen in new window

A question I have contributed to previously, "What is the best way to find a needle in a haystack?" (12 April 2005), was specifically looking for files created on the run-time system date within a specific folder (c:\windows):

[ https://www.experts-exchange.com/questions/21385046/What-is-the-best-way-to-find-a-needle-in-a-haystack.html ]

This code could be changed (easily) to interrogate the "Date Last Modified" (?objFile.DateLastModified) rather than the "Date Created" (objFile.DateCreated) property of each file, & with the list of files (presently) being sorted into descending order, the first file read from the resultant recordset would be the latest file modified within the specific folder.

The routine could be enhanced to search through all sub-folders from the named starting folder & the file with the latest "Date Last Modified" in each folder could be presented to the user as the routine iterated through every sub-folder.

Also for reference, zorvek's "Folder-File-List.xls" workbook:
[ http://www.zorvek.com/downloads/Folder-File-List.xls ].

(Examples of similar "file/folder searching" routines exist in the archive questions at Experts-Exchange.com)

BFN,

fp.