If statements - msgbox

Question

I'm now trying to write an IF statement to say something to the effect of: If file is more than 5 days old, do not run macro. If more than 5 days old, run macro.

I would like to this to be a yes or no dialogue box. Here is my code. Please help. I'm still trying to learn this vba code.

Sub LastModifiedFile()

'Function FileLastModified(strFullFileName As String)
    Dim fs As Object, f As Object, s As String, dtmodpath As String

    dtmodpath = "\\jdshare\pdcmaterials\5_Tools\FTP\Cancelled_Report.txt"
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(dtmodpath)

    's = UCase(strFullFileName) & vbCrLf
    s = f.DateLastModified
    FileLastModified = s

    Set fs = Nothing: Set f = Nothing

Range("E5").Value = FileLastModified

'If FileExists(strFullName) Then
    'MsgBox FileLastModified(strFullName)
    'Else
        'MsgBox "File Older than 5 Years : " & vbNewLine & strFullName
    'End If

'End Function

End Sub

The function you posted on your [original question](https://stackoverflow.com/questions/51108671/why-am-i-getting-compile-error-expected-end-sub/51137608?noredirect=1#comment89261533_51137608) worked fine. Starting a new question with the info given from the other question just gets my back up to be honest. — Darren Bartrup-Cook, Jul 02 '18 at 15:30

Vityata · Answer 1 · 2018-07-02T15:26:46.073

Congrats for using correctly the .DateLastModified property!

Instead of the MsgBox-es call a function. The DateAdd() returns date, which is 5 days before the current date, thus it is easy to compare. This shows a MsgBox() which informs whether the file has more or less than 5 days from the last modification:

Option Explicit

Sub LastModifiedFile()

    Dim fileObject As Object
    Dim file As Object
    Dim modPath As String

    modPath = "\\jdshare\pdcmaterials\5_Tools\FTP\Cancelled_Report.txt"
    Set fileObject = CreateObject("Scripting.FileSystemObject")
    Set file = fileObject.GetFile(modPath)

    If DateAdd("d", -5, Now) < file.DateLastModified Then
        MsgBox "Less than 5 days."
    Else
        MsgBox "More than 5 days."
    End If

End Sub

If you want to put a MsgBox in the whole story with Yes and No, then this should be ok:

Sub LastModifiedFile()

    Dim fileObject As Object
    Dim file As Object
    Dim modPath As String

    modPath = "\\jdshare\pdcmaterials\5_Tools\FTP\Cancelled_Report.txt"
    Set fileObject = CreateObject("Scripting.FileSystemObject")
    Set file = fileObject.GetFile(modPath)

    Dim msgBoxStatement As String

    If DateAdd("d", -5, Now) < file.DateLastModified Then
        msgBoxStatement = "This file is NOT older than 5 days!" & vbCrLf & _
            "Should it be deleted?"
    Else
        msgBoxStatement = "This file is older than 5 days!" & vbCrLf & _
            "Should it be deleted?"
    End If        

    Select Case MsgBox(msgBoxStatement, vbYesNo Or vbQuestion, "Delete?")        
        Case vbYes
            'run the for deletion
        Case vbNo
            'do not run the code for deletion
    End Select

End Sub

Thomas G · Answer 2 · 2018-07-02T15:31:07.577

Use DateDiff function to compute your number of days.

Its not totally clear what you want to do with your Yes/No message box, here's an attempt :

Sub LastModifiedFile()

    Dim fs As Object, f As Object, s As String, dtmodpath As String
    Dim dtLastMod As Date
    Dim intDays As Long

    dtmodpath = "\\jdshare\pdcmaterials\5_Tools\FTP\Cancelled_Report.txt"

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(dtmodpath)
    dtLastMod = f.DateLastModified

    ' Here you compute the number of days between the file last mod date, and the current date
    intDays = DateDiff("d", dtLastMod, Now)

    Set fs = Nothing: Set f = Nothing
    Range("E5").Value = dtLastMod

    If intDays > 5 Then

        If MsgBox("File is " & intDays & " days old, proceed with macro ?", vbYesNo, "Continue?") = vbYes Then
            ' RUN MACRO GOES HERE
        End If
    Else
        MsgBox "File is " & intDays & " days old, cancelling"
    End If

End Sub

If statements - msgbox

2 Answers2