qitjch
02-09-2016, 08:08 AM
Hello again,
I am trying to use VBA to determine if all files in a given directory have a Date Last Modified attribute of today. I only want the MsgBox to show up if any of the files in the folder are older than today.
Here is what I have so far, but this current setup provides me with two issues. First, I am not sure how to set this up with the MsgBox outside of the loop. Secondly, the DateAdd statement seems to be causing an issue due to the hour/minutes portion of the date.
For example, I have 4 files in my folder right now. 3 of the 4 files have last modified dates of 2/8/16 9:00am. The 4th file has a last modified date of 2/8/2016 11am. I ran this Macro at 10am EST and when I did so, the first 3 MsgBox notifications display correctly telling me the file is out of date. The 4th however, tells me the file is "up to date." I believe this is due to the fact that DateAdd("d", -1, Now) seems to be a literal 24 hours ago. Is there anyway to set this up so that it only considers the m/d/yy portion of the date and not the hour/minute aspect?
Sub ListFilesInFolder()
Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder
Dim FileItem As Scripting.File
Dim r As Long
Set FSO = New Scripting.FileSystemObject
Set SourceFolder = FSO.GetFolder("\\svusindfile1\IndyManufacturing\Finance\Daily PPV Reporting\Email Attachments\GL Detail\") ' Place in here the directory of interest
For Each FileItem In SourceFolder.Files
If FileItem.DateLastModified < DateAdd("d", -1, Now) Then
MsgBox "GL Text Files are not up to date. Please delete files from GL Detail folder and save new text files to that location."
Else
MsgBox "GL Files up to date"
End If
Next FileItem
End Sub
I am trying to use VBA to determine if all files in a given directory have a Date Last Modified attribute of today. I only want the MsgBox to show up if any of the files in the folder are older than today.
Here is what I have so far, but this current setup provides me with two issues. First, I am not sure how to set this up with the MsgBox outside of the loop. Secondly, the DateAdd statement seems to be causing an issue due to the hour/minutes portion of the date.
For example, I have 4 files in my folder right now. 3 of the 4 files have last modified dates of 2/8/16 9:00am. The 4th file has a last modified date of 2/8/2016 11am. I ran this Macro at 10am EST and when I did so, the first 3 MsgBox notifications display correctly telling me the file is out of date. The 4th however, tells me the file is "up to date." I believe this is due to the fact that DateAdd("d", -1, Now) seems to be a literal 24 hours ago. Is there anyway to set this up so that it only considers the m/d/yy portion of the date and not the hour/minute aspect?
Sub ListFilesInFolder()
Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder
Dim FileItem As Scripting.File
Dim r As Long
Set FSO = New Scripting.FileSystemObject
Set SourceFolder = FSO.GetFolder("\\svusindfile1\IndyManufacturing\Finance\Daily PPV Reporting\Email Attachments\GL Detail\") ' Place in here the directory of interest
For Each FileItem In SourceFolder.Files
If FileItem.DateLastModified < DateAdd("d", -1, Now) Then
MsgBox "GL Text Files are not up to date. Please delete files from GL Detail folder and save new text files to that location."
Else
MsgBox "GL Files up to date"
End If
Next FileItem
End Sub