vba to list the most recent file in a folder

[samahiji]

Hi
I have the following code to search and list certain file in folder, however, it picks the old one!
I need the vba to pick the most recent file.

Sub OH()
    Dim i As Integer
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To FinalRow
    Dim files As Collection
    
    Set files = ListFiles(Cells(i, 67), Cells(i, 1) & "*.pdf")

    Dim fileName As Variant
    Dim AdobeCommand As String
    Const cAdobeReaderExe As String = "C:\Program Files (x86)\Adobe\Reader 11.0\Reader\AcroRd32.exe"
   

   
    For Each fileName In files
    

    Cells(i, 68) = Cells(i, 67) & fileName
  
    Next
    Next
End Sub

 

[Anthony47]

Hummm... Now I am confused
How did you determine, in the example you gave, that Box-1854-main.jpg is the latest file, considering that "late" and "recent" imply the concept of timing (date & hour)?

Considering that you used ListFiles(Cells(i, 67), Cells(i, 1) & "*.pdf"), could you show us which information are in column 67 and column 1, maybe that will help?

 

[samahiji]

Cells(i, 67) = path name "C:\Mary\Boxes" and Cells(i, 1) = "Box-1854"
From date time modified I should know that Box-1854-main.jpg has the recent modified date.

 

[Anthony47]

Is the path name "C:\Mary\Boxes" common to all the files that you need to investigate, or does it change row by row? Do you know which subfolder are under that path name, and how many level can be found, or is it unpredictable?
Is it true that you basically need to search under path name "C:\Mary\Boxes" and all its subfolders (at any level of depth) all the files that contain the string specified in column A, and select the one with the most recent "Modified date"? If so, how often do you need to run such a process?

 

[Anthony47]

We are on different time-zones, at this pace the discussion might last a couple of weeks...
Let me try to move ahead with the following code:
a) it assumes that the starting path is common to all the files to search
b) it will search any file in the starting directory and all its subdirectories (this is not a short process, that's why having a common starting path helps)
c) if the filename matches one of those to be searched then the DateLastModified is tested and the most recent is stored, along with the current full Path & Filename
At the end the results are returned in an array that can be written on the sheet (as I do in the example) or it can be used for other uses.
The code, to be copied in a new, empty, vba standard module:

Option Explicit
Dim myFso As Object, ccAll As Long    'Strictly on top of the Module

Function RecurSearch(ByVal ccDir As String, myFilter As Variant, ByRef cStore As Variant) As String
Dim myItm, myInd As Long
Dim cExPos As Long, cFName As String, fPos
'
If myFso Is Nothing Then Set myFso = CreateObject("Scripting.FileSystemObject")
If myFso Is Nothing Then Beep
DoEvents
On Error GoTo Mahh
For Each myItm In myFso.GetFolder(ccDir).files
'    ccAll = ccAll + 1
    cExPos = InStrRev(myItm.Name, ".", , vbTextCompare)
    If cExPos = 0 Then cFName = myItm.Name Else cFName = Left(myItm.Name, cExPos - 1)
    fPos = Application.Match(cFName, myFilter, False)
    If Not IsError(fPos) Then
        If myItm.DateLastModified > cStore(fPos, 2) Then
            cStore(fPos, 1) = myFso.GetAbsolutePathName(ccDir) & "\" & myItm.Name
            cStore(fPos, 2) = myItm.DateLastModified
        End If
    End If
Mahh:
Resume Bohh
Bohh:
Next myItm
For Each myItm In myFso.GetFolder(ccDir).SubFolders
    Call RecurSearch(myItm, myFilter, cStore)
Next myItm
End Function



Sub Example()
Dim strFile As String
Dim StrDir As String, I As Long
Dim FileArr(), Searched
Dim DumpPos As String
'
Sheets("Sheet1").Select                 '<<< The working sheet
'
StrDir = Range("B2").Value              '<<< The start path
'Get list of files to search:
Searched = Range(Range("A2"), Range("A1").Offset(1000, 0).End(xlUp)).Value     '<<<
'Resize the array
ReDim FileArr(1 To UBound(Searched), 1 To 2)
'Collect list of files:
Call RecurSearch(StrDir, Searched, FileArr)
'
'>>>>> List of files is now ready in array FileArr:
'1: Full path & name of file        2:DateLastModified
'
'Example:
'   FileArr(2,1) is the file full name
'   Filearr(2,2) is the file DateLastModified
'
'Example 2, Dump list:
DumpPos = "C2"            '<<< The list will be written starting from here, 2 columns
Range(DumpPos).Resize(UBound(FileArr), 2) = FileArr
End Sub

The lines marked <<< need to be customized according your situation
Then run Sub Example



My test condition are outlined in the XL2BB minishet below (and the results are correct); the C:\PROVA file tree is in the image

MULTI_C30207.xlsm
	A	B	C	D	E
1	List of Files	Start Path	Returned values
2	File-23	C:\PROVA
3	File-234		C:\PROVA\BabaSub_File-123\File-234.pdf	15/02/2023 10:56
4	File-2345		C:\PROVA\myStation_files\File-2345.XLSM	15/02/2023 11:15
5	File-23456
6	File-234567
7	File-2345678
8
Sheet1