Consulting

Results 1 to 5 of 5

Thread: LastModified = 5 minutes (???)

  1. #1

    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??


    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

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Master
    Joined
    Jan 2005
    Location
    Porto Alegre - RS - Brasil
    Posts
    1,219
    Location
    Try this:


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

    Carlos Paleo.

    To every problem there is a solution, even if I dont know it, so this posting is provided "AS IS" with no warranties.

    If Debugging is harder than writing a program and your code is as good as you can possibly make
    it, then by definition you're not smart enough to debug it.




    http://www.mugrs.org

  3. #3
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Two suggestions

    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

    MsgBox ActiveWorkbook.BuiltinDocumentProperties(12).Value
    returns the LastSaved time.

  4. #4
    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?


    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

  5. #5
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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.

    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.

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •