How to get the most recently modified file of a ce

Question

Here is the problem:

I have a folder with many text files. I want to find the file I need to parse; however, it has a different name each time so I cannot use the file name. What I know about it is that it is always 39KB (although marginally different each time, so I check for >39000 and <40000). However, there are often several files of that same size in the folder, and I would like to select the MOST recently modified one.

What I have:

If fNewest = "" Then
      Set fNewest = objFile
    ElseIf fNewest.DateLastModified < objFile.DateLastModified Then
      Set fNewest = objFile
    End If

    If (objFile.Size > 39000 and objFile.Size < 40000) Then
      Msgbox fNewest
    End If

While fNewest is returning the path to the file I want (the 39Kb file that was most recentl modified), Msgbox is being called 4 times (which is the number of occurances of a file that is 39Kb in that folder). Does anyone know how I can modify this code to correct this, or a better way to run this check?

My ultimate coal is to have a condition statement(s) as above so Msgbox is replaced with the call to the specific function that takes that file and parses it.

Thanks.

Answer 1

Your nesting is off. The MsgBox should be outside the loop with which you iterate over the files in your folder, and the assignment should be inside the conditional that checks the file size. Try this:

Set fso = CreateObject("Scripting.FileSystemObject")

For Each f In fso.GetFolder("...").Files
  If f.Size > 39000 and f.Size < 40000 Then
    If IsEmpty(newest) Then
      Set newest = f
    ElseIf newest.DateLastModified < f.DateLastModified Then
      Set newest = f
    End If
  End If
Next

If Not IsNull(newest) Then MsgBox newest.Name

Answer 2

Dim newest, fso
Set fso = CreateObject("Scripting.FileSystemObject")

For Each f In fso.GetFolder(".").Files
  If f.Size > 39000 and f.Size < 40000 Then
    If IsEmpty(newest) Then
      Set newest = f
    ElseIf newest.DateLastModified < f.DateLastModified Then
      Set newest = f
    End If
  End If
Next

If Not IsEmpty(newest) Then MsgBox newest.Name

(I just replace IsNull with IsEmpty)