Fast file search for most recently modified file

[sshive1]

Greetings,

I recently threw together a bit of code that parses text from the most recently modified file in a selected folder. However, the folder keeps track of job tickets at my work, and it can fill up fairly quickly. Right now, 889 .txt files are in the folder to be searched. I have relatively little experience with VBA at maybe 3 months of steady use at work. My implementation takes around 15 seconds to run, and I would like to know if there is a way to speed up this process. This is the implementation currently. I narrowed down the performance bottleneck to this for each loop. I would love to hear thoughts on the matter!

'loop through each file and get date last modified. If largest date then store Filename
    dteFile = DateSerial(1900, 1, 1)
    For Each objFile In myFolder.Files
        If objFile.DateLastModified > dteFile And Right(objFile.Name, 4) = ".txt" Then
            dteFile = objFile.DateLastModified
            strFilename = objFile.Name
        End If
    Next objFile

 

[RedBeard]

Here's the official cheat-sheet: https://blogs.office.com/2009/03/12/excel-vba-performance-coding-best-practices/

 

[Kenneth Hobson]

If your path has a space character, the path will need to be encapsulated in quotes. Modify to suit:

Sub MostRecentFile()
  MsgBox Split(CreateObject("Wscript.Shell").Exec _
    ("cmd /c dir x:\csv\*.csv /o:d /a:-d /b").StdOut.ReadAll, vbLf)(0), _
    vbInformation, "Oldest File"
    
    MsgBox Split(CreateObject("Wscript.Shell").Exec _
    ("cmd /c dir x:\csv\*.csv /o:-d /a:-d /b").StdOut.ReadAll, vbLf)(0), _
    vbInformation, "Newest File"
End Sub

 

[sshive1]

While this is incredibly useful information. I have already attempted any of the applicable tips there, with no noticeable speedup. Thank you for the speedy reply!

 

[sshive1]

While this is incredibly useful information. I have already attempted any of the applicable tips there, with no noticeable speedup. Thank you for the speedy reply!

@RedBeard

 

[sshive1]

If your path has a space character, the path will need to be encapsulated in quotes. Modify to suit:

Sub MostRecentFile()
  MsgBox Split(CreateObject("Wscript.Shell").Exec _
    ("cmd /c dir x:\csv\*.csv /o:d /a:-d /b").StdOut.ReadAll, vbLf)(0), _
    vbInformation, "Oldest File"
    
    MsgBox Split(CreateObject("Wscript.Shell").Exec _
    ("cmd /c dir x:\csv\*.csv /o:-d /a:-d /b").StdOut.ReadAll, vbLf)(0), _
    vbInformation, "Newest File"
End Sub

Kenneth, this is an awesome solution. My only issue is that I get a Bad File Number error when I try to open the found file for reading later. The interesting part is that I generate the exact same path string through my other implementation as this, but get no error. Are you familiar with a similar occurrence? In the following code, the getRecent() function is where I used your suggestion to find the most recent file, and the error occurs when I try to validate the existence of the file with the dir function.

This is the called function with the modified code.

strFilename = Split(CreateObject("Wscript.Shell").Exec _
        ("cmd /c dir ""I:\CNC\CNC ID Grind\*.txt"" /o:-d /a:-d /b").StdOut.ReadAll, vbLf)(0)

getRecent = strFilename

This is the calling function that uses the output:

folder = "I:\CNC\CNC ID Grind\"                                                   ' "S:\Operations\Production Master Spreadsheets\CNC ID Grind\"
    fname = getRecent()
    FilePath = folder & fname
    
    If Dir(FilePath) <> "" Then

Thanks for any help

 

[sshive1]

UPDATE:
I just needed to trim the vbLf character from the output in order to use the filename for opening and reading as a text file. Again, thank you much Kenneth, this implementation is a world of faster than what I had previously.

 

[Kenneth Hobson]

At work so I would have to look into it more tonight. I use:

msgbox len(dir (filepath))=0

Or the more reliable

msgbox createobject ("scripting.filesystemobject").fileexists (filename)

though you do not need to check since it was found earlier.