Verify Date Stamp of several files in a folder.

[Robeen]

Hi,

I want to verify that all the XLSX files in a folder have today's date - and if they do, run a Macro.

I'm doing something like this . . .:

If DateValue(FileDateTime("C:\Folder\File1.xlsx")) = Date Then
    If DateValue(FileDateTime("C:\Folder\File2.xlsx")) = Date Then
        If DateValue(FileDateTime("C:\Folder\File3.xlsx")) = Date Then
            If DateValue(FileDateTime("C:\Folder\File4.xlsx)) = Date Then
                If DateValue(FileDateTime("C:\Folder\File5.xlsx")) = Date Then
                    DoCmd.RunMacro "Mcr_MacroName"
                End If
            End If
        End If
    End If
End If

This works fine . . . but . . .


How would I do this in a loop - [using the Dir function, perhaps]?
I am having to do this for several different folders in several different parts of the project & the names and numbers of files in these folders can change.
The constant is that they are all .xlsx files.

I'd appreciate help! Thanks!!

[John_G]

Hi -

Yes, the Dir function is what you need to use. The first call to Dir will use a wildcard character to match all the required files. Subequent calls to Dir are used without any arguments; doing that returns the name of the next file that matches the specification; when there are no more matching files, Dir returns an empty string.

Something like this:

Dim FileName as string, AllValid as Boolean
FileName=Dir("C:\Folder\*.xlsx") ' Note use of wildcard character
AllValid = True
Do while FileName <> ""
if DateValue(FileDateTime(FileName)) <> Date then
'
' Exit at first invalid date
'
AllValid = False
exit do
endif
'
' Get next file name, if any
'
Dir
Loop
'
' Run macro if all dates are valid
'
if allvalid then DoCmd.RunMacro "Mcr_MacroName"

Untested, but it should get you started.

John

[Robeen]

Thanks, John_G.

This is working well. Appreciate your help.

I made changes - marked in red.

I had to include the path to prevent a 'file not found' error.

I decided not to use the Boolean as I felt it was redundant - since I'm exiting the function at the first sign of a file not matching today's date.
And - if I have not exited the function, then it means all dates are today so - just run the Macro.

This is what I ended up using in a function:

 
Dim FilePath, FileName As String, AllValid As Boolean
 
FileName = Dir("C:\Folder\*.xlsx") 
FilePath = "C:\Folder\"
 
Do While FileName <> ""
    If DateValue(FileDateTime(FilePath & FileName)) <> Date Then
       'Exit at first invalid date
        Exit Function
    End If
    'Get next file name.
    FileName = Dir
Loop
 
DoCmd.RunMacro "MacroName"

Once again - thanks for your time & help.