LastModified = 5 minutes (???)

Is there a way to set the LastModified to minutes?? So I only open files that were modified 5 minutes or more ago??

Code:

---

Sub FindClientExcelFiles()
  Dim FS As Office.FileSearch
  Dim vaFileName As Variant
  Dim startdir
  Dim enddir
  Dim Foo As Object
  Dim iCount As Long
  Dim newname As Variant
  Dim fsoObj As Object, TheDate As String
TheDate = Format(Date, "YYYYMMDD")
startdir = "C:\Temp\1"
  enddir = ("C:\Temp\" & TheDate & "\")
Set fsoObj = CreateObject("Scripting.FileSystemObject")
    With fsoObj
    If Not .FolderExists(enddir) Then
        .CreateFolder (enddir)
    End If
End With
  Set FS = Application.FileSearch
  With FS
    'Clear old search criteria
    .NewSearch
    'Directory to search
    .LookIn = startdir
    'Include sub folders in search
    .SearchSubFolders = True
    'Look for Excel files
    .FileType = msoFileTypeExcelWorkbooks
    'Doesn't matter when last modified
    .LastModified = msoLastModifiedAnyTime
    iCount = .Execute
    'List the files in the FoundFiles collection
    For Each vaFileName In .FoundFiles
Set Foo = Workbooks.Open(vaFileName)
    Application.DisplayAlerts = False
    Foo.SaveAs enddir & Foo.Name
    Foo.Close
    Application.DisplayAlerts = True
    Kill vaFileName
Next vaFileName
  End With
End Sub

---

Try this:

Code:

---

.LastModified = TimeSerial(Hour(Time), Minute(Time) - 5, Second(Time))

---

Two suggestions

Code:

---

For Each vaFileName In .FoundFiles
            If FileDateTime(vaFileName) > Now() - 5 / (24 * 60) Then
                Set Foo = Workbooks.Open(vaFileName)
                Application.DisplayAlerts = False
                Foo.SaveAs enddir & Foo.Name
                Foo.Close
                Application.DisplayAlerts = True
                Kill vaFileName
            End If
        Next vaFileName

---

or investigate BuiltInDocumentProperties. In my case

Code:

---

MsgBox ActiveWorkbook.BuiltinDocumentProperties(12).Value

---

returns the LastSaved time.

The FileDateTime line is not working. I added the below code to my macro and it does nothing when i run it. I have files in the "startdir" with time stamps from yesterday but nothing happens. Any ideas?

Code:

---

For Each vaFileName In .FoundFiles
    If FileDateTime(vaFileName) > Now() - 5 / (24 * 60) Then
        Set Foo = Workbooks.Open(vaFileName)
        Application.DisplayAlerts = False
        Foo.SaveAs enddir & Foo.Name
        Foo.Close
        Application.DisplayAlerts = True
        Kill vaFileName
    End If
Next vaFileName

---

Ooops!
I think I mixed up my < and > signs. Try the following, I've added a few debug.print lines so you can see what's happening in the Immediate window.

Code:

---

Debug.Print Now() - 5 / (24 * 60)
        For Each vaFileName In .FoundFiles
            Debug.Print FileDateTime(vaFileName)
            If FileDateTime(vaFileName) < Now() - 5 / (24 * 60) Then
                Debug.Print vaFileName & " - True"
                Set Foo = Workbooks.Open(vaFileName)
                Application.DisplayAlerts = False
                Foo.SaveAs enddir & Foo.Name
                Debug.Print enddir & Foo.Name
                Foo.Close
                Application.DisplayAlerts = True
                Kill vaFileName
            Else
                Debug.Print vaFileName & " - False"
            End If
        Next vaFileName

---

The following variation with attached function should let you accomplish the same task without opening the files.

Code:

---

For Each vaFileName In .FoundFiles
            If FileDateTime(vaFileName) < Now() - 5 / (24 * 60) Then
                MyFile = FName(vaFileName)
                Source = startdir & "\" & MyFile
                Dest = enddir & MyFile
                FileCopy Source, Dest
                Kill Source
            End If
        Next vaFileName
    End With
End Sub
Function FName(FileName)
    j = Len(FileName)
    For i = j To 1 Step -1
        If Mid(FileName, i, 1) = "\" Then
            FName = Right(FileName, j - i)
            Exit For
        End If
    Next
End Function

---