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
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,092
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
 

sshive1

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

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

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

sshive1

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

Well-known Member
Joined
Feb 6, 2007
Messages
3,092
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.
 

Forum statistics

Threads
1,082,137
Messages
5,363,349
Members
400,729
Latest member
Lisa McConachy

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top