Solved

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

Posted on 2013-01-11
5
2,176 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Couple of functions can be found here

Kris
0
 
LVL 35

Expert Comment

by:[ fanpages ]
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now