Finding Last Created / Most Recent File with VBA not referencing Microsoft Scripting Runtime (The Answer)

[Legacy 185660]

Hey everyone! I was looking around for a way to find the last created or last modified file in folder and couldn’t find a way to do it without using the Microsoft Scripting Runtime reference, after a lot of searching to figure out how to reference the dll automatically, I realized that I could do this the same way I send emails through outlook. I found using objects works best and doesn’t require you to manually reference anything, so I experimented a bit with those and it worked perfectly!
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Granted it is probably not as efficient as using the reference, but I would still rather have this then explain to every co-worker what to do<o></o>

Sub mostRecentCreatedFile()
 
    'Folder path you want to search
    Dim strFolderPath As String: strFolderPath = "C:\Users\salim.nanji\AppData\Local\Temp"
 
    Dim fileSystem As Object: Set fileSystem = CreateObject("Scripting.FileSystemObject")
 
    Dim folder As Object: Set folder = fileSystem.GetFolder(strFolderPath)
    Dim file As Object
 
    Dim strFilename As String
    Dim fileDate As Date
 
    'Loop through all files in directory
    For Each file In folder.Files
 
        'Find newest file that is also an HTML or XML (Report/Excel)
        'If file.DateCreated > fileDate And (file.Type = "HTML Document" Or file.Type = "XML") Then
 
        'Finds newest file of any type
        If file.DateCreated > fileDate Then
            fileDate = file.DateCreated
            strFilename = file.Name
        End If
    Next file    'Displays last created file name
    MsgBox strFilename
 
End Sub

This code was made to go through my temp folder and gather up some reports, so I used file.Type to make sure I only pulled types I needed.


This can easily be changed to last modified by changing .DateCreated to .DateLastModified

Enjoy!

 

[Legacy 185660]

As a funtion (will return file name)

Function mostRecentFile(strFolderPath As String)
    
    Dim fileSystem As Object: Set fileSystem = CreateObject("Scripting.FileSystemObject")
    Dim folder As Object: Set folder = fileSystem.GetFolder(strFolderPath)
    Dim file As Object
    
    Dim strFileName As String
    Dim fileDate As Date
    
    'Loop through all files in directory
    For Each file In folder.Files
    
        'Finds newest file of any type
        'If file.DateCreated > fileDate Then
        
        'Find newest file that is also an HTML or XML (Report/Excel)
        If file.DateCreated > fileDate And (file.Type = "HTML Document" Or file.Type = "XML") Then
            fileDate = file.DateCreated
            strFileName = file.Name
        End If
    Next file
    'Displays last created file name
    mostRecentFile = strFileName
    
End Function