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]

And what is ListFiles?
Which is the typical content for column A and column BO?

 

[samahiji]

Listfiles is pdf files in the target folder
column A and column BO contains the partial name of that folder.

The code picks the old pdf file if there are more than one matching criteria.

 

[Anthony47]

ListFile in not a vba internal feature; is it a Function, a Sub, a "what else"?

 

[Fluff]

@samahiji what version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

IIRC ListFile is something that was removed with xl2007, although the OP may have a sub or function with the same name.

 

[Anthony47]

OMG... Yes, now I remeber a command that was removed "years ago", but I seem it was FileSearch...
I guess ListFile is a Sub the OP uses, so there we have to work for getting the newest file; I also was hoping to get some useful info from the content of Cells(i, 67) and Cells(i, 1)

 

[Fluff]

but I seem it was FileSearch...

I think your right, I couldn't quite remember what it was called.

 

[samahiji]

Sorry for confusing you all.
I forget to add the following function that declare the listfile, I'm so sorry!

Function ListFiles(FolderName As String, SearchString As String) As Collection
    Set ListFiles = New Collection
    
    Dim fileName As String
    fileName = Dir(FolderName & "\" & SearchString)
    
    If Len(fileName) = 0 Then Exit Function
    
    Do While fileName <> ""
        ListFiles.Add fileName
        fileName = Dir()
    Loop
End Function

I'm using Excel 2019.

The function above list pdf file with partial file name mentioned by Cells(i, 67), Cells(i, 1):

Set files = ListFiles(Cells(i, 67), Cells(i, 1) & "*.pdf")

The problem when the folder contains more than one pdf file, it picks the old one. I need the most recent file.

 

[Anthony47]

I still don't have the information for filtering based on the date, but can submit the following function that will return a two-dimensions array containing (a) the files found in a specific directory (1st parametre) whose filename contains a specific string (2nd parametre; keep it blank to return "all the files") and (b) eithet the DateCreated or DateLastModified (3rd parametre: 1 or missing for CreatedD; <>1 for LastModified)
The code:

Function FileColl(ByVal myPath As String, myString As String, Optional ByVal PInd As Long = 1) As Variant
'Returns an array of FileName.extension / Created or Modified Date
'Parametres: Path; Search String; 1 (default value)=Created date; <>1=Modified date
Dim CollFiles
Dim FArr(), FInd As Long
'
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set CollFiles = objFSO.GetFolder(myPath).files
'
ReDim FArr(1 To 2, 1 To CollFiles.Count)
For Each myfile In CollFiles
Debug.Print myfile.Name, myfile.DateCreated
    If InStr(1, myfile.Name, myString, vbTextCompare) > 0 Then
        FInd = FInd + 1
        FArr(1, FInd) = myfile.Name
        If PInd = 1 Then
            FArr(2, FInd) = myfile.DateCreated
        Else
            FArr(2, FInd) = myfile.DateLastModified
        End If
    End If
Next
ReDim Preserve FArr(1 To 2, 1 To FInd)
FileColl = Application.WorksheetFunction.Transpose(FArr)
End Function

It return an array, not a collection; so you may call it as follows:

Dim Arr As Variant

Arr = FileColl("C:\PROVA", "XL")

At this point UBound(Arr) will return the number of lines in the array; Arr(1,1) will return the first filename and Arr(1,2) will return the Date of the file (DateCreated, since 3rd param is missing)

I hope you have the information to search for your file and then search for the preferred date

You may even use it in a formula; for example, in D16

=FileColl(B16,B17,B18)

This will return, in column number 2, the DateLastModified (3rd param is <>1)

If your Office Version doesn't support Dynamic Arrays you need to enter the formula in a range of N rows * 2 columns as an array formula (ie using Control Shift Enter, not Enter alone)

MULTI_C30207 (version 1).xlsb
	A	B	C	D	E	F	G
15
16		C:\PROVA		2019-07-20_04506.XLSM	7/20/2019 12:15:07 AM	=FileColl(B16,B17,B18)
17		xl		2019-07-20_05210.XLSM	7/20/2019 12:26:50 AM
18		2		TxOdds_20190423.xlsm	4/23/2019 6:41:00 PM
19				WBB2B.xlsx	10/19/2022 8:16:39 PM
20				WBFoglio1.xlsx	10/19/2022 8:15:37 PM
21				WBFoglio11.xlsx	10/19/2022 8:20:11 PM
22				WBFoglio4.xlsx	10/19/2022 8:18:54 PM
23				WBFoglio5.xlsx	10/19/2022 8:19:10 PM
24				WBFoglio6.xlsx	10/19/2022 8:19:39 PM
25				WBFoglio7.xlsx	10/19/2022 8:20:42 PM
26				WBPlats.xlsx	10/19/2022 8:17:55 PM
27
28
Foglio4

Cell Formulas
Range	Formula
D16:E26	D16	=FileColl(B16,B17,B18)
Dynamic array formulas.

 

[samahiji]

Anthony47
Thanks a lot for your efforts. The code works fine but it takes long time to return the correct file name for few data.
let me explain it very well so you may modified the code accordingly.