Loop Through Files in Folder and check DateLastModified

mar050703 · Sep 21st 2016

Hi All

I am at a loss.

I have a piece of code that loops through files in a given folder, and does stuff.

However, before any of that I would like to Loop through the same folder and check that the DateLastModified date (Month only) is the same as the current month.

If a file is not (ie it is older) then I do not want any of the other loop to work (for any of the files), that is there will be no changes. All files need to have a datelastmodified of current month. (An all or nothing idea).

My Current Code is:

Code

MyPath = Sheets("Validate").Range("H1") 'Path where file can be found
MyExt = "*.xlsx"
MyFile = Dir(MyPath & MyExt)
Filename = Sheets("Validate").Range("H2")
SvPath = Sheets("Validate").Range("H4") 
Set MyName = Sheets("Selection").Range("D1") 'Name of .xls file to open
Set BU = Sheets("Selection").Range("E1") 


Do While MyFile <> ""
    Set wb = Workbooks.Open(Filename:=MyPath & MyFile)
'do stuff...

Display More

Thanks

**cytop** · Sep 21st 2016

Re: Loop Through Files in Folder and check DateLastModified

A simple function to loop through the folder using the FileDateTime function along with the DIR function to get a list of all files to make sure everything is dated this month should do it. Pseudo-code (in that it was typed freehand, is untested and may not be syntactically correct) ...

Code

Public Function AllFilesDatedThisMonth(FilePath) as Boolean


    Dim fName as String
    
    '// FilePath should NOT have a terminating '/'
    fName = DIR(FilePath)


    '// If no files found, then exit. The function value will default to 'False'
    If fName = vbNullString then Exit Function


    Do While fName <> vbNullstring
       
        If Month(FileDateTime(FilePath & "\" & fName)) <> Month(Date) Then
           AllFileDatedThisMonth = False
           Exit Function
        End If


        fName = Dir


    Loop


    '// Gets to here, then all are dated same month. 
    AllFilesDatedThisMonth = True

Display More

You add a check before the main loop

Code

If not AllFilesDatedThisMonth() then Exit sub '// (Or whatever)

mar050703 · Sep 21st 2016

Re: Loop Through Files in Folder and check DateLastModified

Hi Cytop
Thanks for that, I managed to get the following:

Code

Dim fs, f
Dim filemoddate As String
Set fs = CreateObject("Scripting.filesystemobject")
Set f = fs.getfile(MyPath & MyFile)
filemoddate = f.datelastmodified




Do While MyFile <> ""
    If filemoddate <> Format(DateSerial(1, Month(Date) - 1, 1), "mmm") Then
        MsgBox MyFile & " Has a Date of " & Format(filemoddate, "mmm")
        MsgBox "Please Download all the latest Reports", vbInformation
        Exit Sub
    End If
MyFile = Dir




MyName.Value = ""




Loop
MsgBox "All File Ok"
Exit Sub

Display More

I think this works too.

**cytop** · Sep 21st 2016

Re: Loop Through Files in Folder and check DateLastModified

It'll work - my method is just me preferring to rely on inbuilt VBA functions rather than an external library. One less layer of complication to break.

mar050703 · Sep 21st 2016

Re: Loop Through Files in Folder and check DateLastModified

Hi Cytop

I have just been doing some testing, and I am not sure my code is working as I expected.

I have modified my code

Code

'Check Modified Date - if Not Current Month - Do Nothing
Do While MyFile <> vbNullString
    If filemoddate <> Format(DateSerial(1, Month(Date), 1), "mmm") Then
        MsgBox "Not All Files are for " & Format(Date, "mmmm"), vbCritical
        MsgBox "Please Download all the latest Reports", vbInformation 
        Exit Sub
    End If
    MyFile = Dir




    MyName.Value = ""
Loop
'Do if Check is Ok"
MsgBox "All Files are for " & Format(Date, "mmm"), vbInformation
Exit Sub

Display More

So i have removed all the current files, and created a new one (therefore should continue to "Do if Check Ok"), but it does the failing code.

Also, if there is more than one file, and say the second file does not have a Date Modified of Current Month (Sept) then code (which I have removed)

Code

MsgBox MyFile & " Has Failed", vbinformation

gives me the name of the first file in the folder, not the failed one.

Thanks

mar050703 · Sep 21st 2016

Re: Loop Through Files in Folder and check DateLastModified

I think the error is datelastmodified.

I have just created a new workbook test, that gives me the Date modified of all Files.

While Windows Explorer shows a Date modified of "09", the message I get says "08"

According to Windows Explorer the Date Created is also "09"

**cytop** · Sep 21st 2016

Re: Loop Through Files in Folder and check DateLastModified

Of course, me saying "it will work" was on the same basis as my code - untested, but in general it would work (except for the logic errors :))

Never mind... going back to my preferred method using native VBA functions, the following will list all files in a directory where the month is not equal to the current month. Optionally, you can pass it a File Spec ("*.XLS?") to limit the checking to just Excel workbooks, for example.

It has expanded a little, but that's only to do with listing the 'invalid' files. It will list up to 15 'invalid' files. If there are more, it will append an "and x other(s)..." message to the output

Code

Public Function AllFilesDatedThisMonth(FPath As String, Optional fMask As String = "*.*") As Boolean


   Dim fName            As String
   Dim ReturnVal As Boolean
   Dim CountBad As Long
   Dim BadFileList As String
   
   '// Default to True
   ReturnVal = True


   '// FilePath should NOT have a terminating '/'
   fName = Dir(FPath & "/" & fMask)


   '// If no files found, then exit. The function value will default to 'False'
   If fName = vbNullString Then 
      MsgBox "No files found...", vbExclamation
      Exit Function
   End If


   Do While fName <> vbNullString


      If Month(FileDateTime(FPath & "\" & fName)) <> Month(Date) Then
         
         If CountBad < 16 Then
            BadFileList = BadFileList & Format(FileDateTime(FPath & "\" & fName), "Mmm") & _
            vbTab & fName & vbCrLf
         End If
         
         CountBad = CountBad + 1
         ReturnVal = False
      End If


      fName = Dir


   Loop


   If BadFileList <> vbNullString Then
      
      BadFileList = "The following files have an invalid date:" & vbCrLf & vbCrLf & _
      BadFileList & _
      IIf(CountBad > 15, vbCrLf & "and " & CStr(CountBad - 15) & " other(s)...", vbCrLf) & vbCrLf & _
      vbCrLf & "Please download the latest reports and run this procedure again."
      
      MsgBox BadFileList, vbExclamation
      
   End If
   
End Function

Display More

You call it using something like

Code

If Not AllFilesDatedThisMonth("c:\temp") Then Exit Sub

where you pass the directory to check.

If you want to limit the checking to a certain file mask, then use

Code

If Not AllFilesDatedThisMonth("c:\temp", "*.XLS?") Then Exit Sub

mar050703 · Sep 21st 2016

Re: Loop Through Files in Folder and check DateLastModified

Thanks.

My question is though - why would

Code

Set f = fs.getfile(MyPath & MyFile)
filemoddate = f.datelastmodified

not return the date according to Windows Explorer?

If I am honest, I am not sure how to impliment your first Function into My Original Code Also, I need it to look at DateLastModified, as the file is overwritten each month (therefore the Date Created does not change)

Thanks

mar050703 · Sep 21st 2016

Re: Loop Through Files in Folder and check DateLastModified

Ok,

After searching the excellent OzGrid, I have come up with this:

Code

Dim FSO As Scripting.FileSystemObject
    Dim SourceFolder As Scripting.Folder
    Dim FileItem As Scripting.File
    Dim MyPath As String
    Dim r As Long
    Dim fname As Scripting.File 'File being Checked
    
    Set FSO = New Scripting.FileSystemObject
    MyPath = "C:\Users\Desktop\Consolidate\Test\Sept\" 'live MUST change
    
    Set SourceFolder = FSO.GetFolder(MyPath) ' Place in here the directory of interest
    
    For Each fname In SourceFolder.Files
        Do While Format(fname.DateLastModified, "mm") <> Format(Now, "mm")
            If Format(fname.DateLastModified, "mm") <> Format(Now, "mm") Then
                MsgBox "File " & fname.Name & " Not All Ok"
                Exit Sub
            End If
        Loop
         
    Next fname
     MsgBox "All Ok"
End Sub

Display More

I believe this to work, on my new test sheet. Now to impliment onto live (GULP!!!)

Thanks again

apo · Sep 22nd 2016

Re: Loop Through Files in Folder and check DateLastModified

Hi..

Give this a try just for fun... i 'think' it does the job..

Code

Private Sub CommandButton1_Click()
    Dim smonth As Date, myPath As String
    myPath = "C:\Text\"
    smonth = CDate(1 * DateSerial(Year(Date), Month(Date), 1))
    If UBound(Filter(Split(CreateObject("wscript.shell").exec("cmd /c forfiles /P " & myPath & " /D -""" & smonth & """").stdout.readall, vbCrLf), ".")) <> -1 Then
        MsgBox "No Good": Exit Sub
    End If
End Sub

Loop Through Files in Folder and check DateLastModified

Participate now!

Share