Solved

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

Posted on 2013-01-11
5
2,348 Views
1 Endorsement
Last Modified: 2013-04-14
Hello,

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?

Thanks!
1
Comment
Question by:qualityip
5 Comments
 
LVL 92

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:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_25063189.html#a26340696

Note that it will not work for 64 bit OS
0
 

Author Comment

by:qualityip
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.

Thanks!
0
 
LVL 92

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

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

Open in new window

0
 
LVL 18

Accepted Solution

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

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

BFN,

fp.
0

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

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!
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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 …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

679 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