Find latest file creation date in folder 1

[MikeCDPQ]

I would like to find the name of the file with the most recent creation date in a given folder.

I grabbed this code on this site which takes me a step closer but I am still missing something. How do I get the max date out of that.

Here's the code:

Dim oFSO As Object, oFolder As Object, oFile As Object
Dim sPath As String

sPath = "G:\my path\"

Set oFSO = CreateObject("Scripting.FileSystemObject")
If oFSO.FolderExists(sPath) Then
Set oFolder = oFSO.GetFolder(sPath)
For Each oFile In oFolder.Files
If LCase$(Right$(oFile.Name, 4)) = ".xls" Then
Debug.Print oFile.Name, oFile.DateCreated
End If
Next oFile


Set oFolder = Nothing
Else
msgbox "Cannot find " & sPath
End If
Set oFSO = Nothing

Thanks for any help.

Mike

 

[vladk]

Put it in the sorted listbox (hidden) and retrieve the last or the first item

 

[bjd4jc]

Or modify your code to

    Dim oFSO As Object, oFolder As Object, oFile As Object
    Dim sPath As String
    Dim HoldName As String
    Dim HoldDate As Date
    
    sPath = "C:\"
    
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    If oFSO.FolderExists(sPath) Then
        Set oFolder = oFSO.getfolder(sPath)
        For Each oFile In oFolder.Files
            If LCase$(Right(oFile.Name, 4)) = ".xls" Then
[b]                If oFile.datecreated > HoldDate Then
                    HoldDate = oFile.datecreated
                    HoldName = oFile.Name
                End If
            End If
[/b]
        Next
        
        Debug.Print HoldName, HoldDate
    Else
        MsgBox "Cannot find " & sPath
    End If

 

[MikeCDPQ]

Thanks bjd4jc

It works beautifully.

You're a life saver

Thanks a bunch !

Mike

 

[zqtqzq]

if the file is a text file how can i open the individual text file to read the content

 

[bjd4jc]

Dim FileStream as TextStream
Dim FileContents as String

If LCase$(Right(oFile.Name, 4)) = ".txt" Then
    Set FileStream = oFSO.OpenTextFile(ofile.Path)
    FileContents = FileStream.ReadAll
End If