Fast file search for most recently modified file

sshive1

New Member
Joined
Mar 4, 2016
Messages
8
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!

Code:
'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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
If your path has a space character, the path will need to be encapsulated in quotes. Modify to suit:
Code:
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
 
Upvote 0
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! :)
 
Upvote 0
If your path has a space character, the path will need to be encapsulated in quotes. Modify to suit:
Code:
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.

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:

Code:
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 :)
 
Upvote 0
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.
 
Upvote 0
At work so I would have to look into it more tonight. I use:
Code:
msgbox len(dir (filepath))=0
Or the more reliable
Code:
msgbox createobject ("scripting.filesystemobject").fileexists (filename)
though you do not need to check since it was found earlier.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top