Compare File Date Modified in VBA

Question

So I am writing a code in VBA that opens up all files in a document and copies and pastes the information from each document. The code is set up to open up every document, but itself. My dilemma is that I want the code to open ever document that has been modified after the last day the main file has been modified. Basically I want to compare two dates with one date staying the same and the other changing after every loop (new document every loop). My code is below and any help or suggestion would be much appreciated. Thanks!

Sub LoopThroughDirectory()
Dim MyFile As String
Dim erow
Dim Filepath As String
Dim DateMaster As Date
Dim DateReflections As Date

Filepath = "Path of folder where all the documents are"
MyFile = Dir(Filepath)
DateReflections = FileDateTime(Filepath)
DateMaster = FileDateTime("Filepath of master document I'm comparing to")

Do While Len(MyFile) > 0
 If MyFile = "zmasterfile.xlsm" Then
Exit Sub
 If DateReflections < DateMaster Then
Exit Sub
End If

Workbooks.Open (Filepath & MyFile)
Range("B4:N4").Copy
ActiveWorkbook.Close

erow = Sheet1.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Reflections").Range(Cells(erow, 2), Cells(erow, 14))

MyFile = Dir

Loop
End Sub

score 1 · Answer 1 · answered Mar 10 '16 at 21:38

You shouldn't be exiting the sub in your if statement. You might consider changing you IF statement to something like below:

Sub LoopThroughDirectory()
Dim MyFile As String
Dim erow
Dim Filepath As String
Dim DateMaster As Date
Dim DateReflections As Date

Filepath = "Path of folder where all the documents are"
MyFile = Dir(Filepath)
DateReflections = FileDateTime(Filepath)
DateMaster = FileDateTime("Filepath of master document I'm comparing to")

Do While Len(MyFile) > 0
    DateReflections = FileDateTime(Filepath)
    If MyFile <> "zmasterfile.xlsm" and DateReflections > DateMaster Then

        Workbooks.Open (Filepath & MyFile)
        Range("B4:N4").Copy
        ActiveWorkbook.Close

        erow = Sheet1.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
        ActiveSheet.Paste Destination:=Worksheets("Reflections").Range(Cells(erow, 2), Cells(erow, 14))

    End If

    MyFile = Dir

Loop
End Sub

Good suggestion to refactor – Stephen Lloyd Mar 10 '16 at 21:44 — Stephen Lloyd, Mar 10 '16 at 21:44

score 1 · Answer 2 · answered Mar 10 '16 at 21:39

1

You just need to reset the DateReflections in your loop, using MyFile to build the file path. See below.

If MyFile = "zmasterfile.xlsm" Then
Exit Sub

DateReflections = FileDateTime(Filepath & "\" & MyFile)
If DateReflections < DateMaster Then
Exit Sub
End If

As an aside, if you'd like to just skip the file and continue processing, rather than exiting the sub entirely, replace your Exit Subs with Continue Do

answered Mar 10 '16 at 21:39

nwhaught

1,562
1
15
36

Thanks for the help! Datereflections works, but continue do doesn't and the two document dates aren't comparing correctly. Any thought on that? Thanks! – Ivan Morales Mar 11 '16 at 19:36

Compare File Date Modified in VBA

2 Answers2