CODEWITHSUNDEEP
excelvbaloopsif-statement
JMCC
JMCC

Reputation: 11

File Looping Macro Stops After First Success

I am hoping someone can assist me with my macro. It is relatively basic, but I cannot seem to figure out why it will not continue the loop.

The macro should look through a set folder for all TXT files. Those files with a modified date within the past week should indicate a success and go on to other steps.

The issue I run into is that the first file that has been modified with the past week triggers the next step, but then the macro ends and does not continue to find other recently modified files.

The example below is simplified to open a msgbox, but the full macro runs additional processes per TXT file.

If someone could provide some guidance it would be greatly appreciated.

Sub OpenAllFiles()
    Dim MyFile As Variant
    Dim MyFolder As String
    Dim FileCount As Integer

    MyFolder = "C:\Input Files\"
    MyFile = Dir(MyFolder)

    Do While (MyFile <> "")
        If Format(FileDateTime(MyFolder & MyFile), "YYYYMMDD") > Format(Now - 6, "YYYYMMDD") Then    
            MsgBox ("Recent File Found.  " & MyFile & " modified " & FileDateTime(MyFolder & MyFile))
            Exit Sub   
        End If
        MyFile = Dir
    Loop
End Sub

Upvotes: 0

Views: 528

Answers (2)

Jur Pertin
Jur Pertin

Reputation: 564

Either you comment out Exit Sub or remove it.

Sub OpenAllFiles()
    Dim MyFile As Variant
    Dim MyFolder As String
    Dim FileCount As Integer

    MyFolder = "C:\Input Files\"
    MyFile = Dir(MyFolder)

    Do While (MyFile <> "")
        If Format(FileDateTime(MyFolder & MyFile), "YYYYMMDD") > Format(Now - 6, "YYYYMMDD")     Then    
            MsgBox ("Recent File Found.  " & MyFile & " modified " & FileDateTime(MyFolder & MyFile))
            'Exit Sub   
        End If
        MyFile = Dir
    Loop
End Sub

Upvotes: 1

AnalystCave.com
AnalystCave.com

Reputation: 4984

Remove the "Exit Sub" from the If

Upvotes: 0

Related Questions