MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Open files based on date modified

Posted by Alex on December 09, 1999 9:41 AM

I have a macro that runs on a series of files that are sent to me each week. However, each week if the date modified of any given file is not within the last seven days I would like the macro to skip that file and move onto the next.
How do I look at and analyse dates in VBA in this fashion?
I've got a feeling the FileDateTime Function has got something to do with it but buggered if I can work out how the calculation should work.

Posted by Ivan Moala on December 10, 1999 12:03 AM

You are right about the function.
Try soemthing like;

Sub Tester()
Dim ThisDay As Double
Dim FileDate As Double
Dim Diff As Double

ThisDay = Now
FileDate = FileDateTime("D:\yourDir\yourfile.xls")
Diff = ThisDay - FileDate
If Diff < 7 Then
do something
do something else
End If
End Sub