PDA

View Full Version : [SOLVED] LastModified = 5 minutes (???)



brianlois
03-04-2005, 02:52 PM
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

Paleo
03-04-2005, 04:22 PM
Try this:



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

mdmackillop
03-04-2005, 04:42 PM
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.

brianlois
03-07-2005, 07:23 AM
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

mdmackillop
03-07-2005, 01:51 PM
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