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

Posted on 2013-01-11
Medium Priority
1 Endorsement
Last Modified: 2013-04-14

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?

Question by:qualityip
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38767742
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

Author Comment

ID: 38768168
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.

LVL 93

Expert Comment

by:Patrick Matthews
ID: 38768250
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

LVL 18

Accepted Solution

krishnakrkc earned 2000 total points
ID: 38769837
Couple of functions can be found here

LVL 35

Expert Comment

by:[ fanpages ]
ID: 39079542
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question