• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2753
  • Last Modified:

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


I need a vbscript that will list the last modified file of each subfolder in a directory. We need to list the file name, directory, and date last modified. This needs to either be in an excel file or a csv file.

I'm currently using the File Manager excel spreadsheet from here: http://www.learnexcelmacro.com/2011/11/how-to-get-list-of-all-files-in-a-folder-and-sub-folders/

This lists all files in the subdirectories with the date last modified but i'd like to only list the file in each directory that was modified last.

Any ideas?

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


Note that it will not work for 64 bit OS
qualityipAuthor Commented:
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.

Patrick MatthewsCommented:
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

  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)
        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

    ActiveWindow.FreezePanes = True
    Range("a3").Sort Key1:=Range("A4"), Order1:=xlAscending, Key2:=Range("B4"), _
        Order2:=xlAscending, Header:=xlYes
    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)")
    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
    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
    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing
End Sub

Open in new window

Couple of functions can be found here

[ fanpages ]IT Services ConsultantCommented:
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):

[ http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_21385046.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)



Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now