VBA

[FAHADMAR]

Dears,

Could you please help me with what to insert inside a macro to let it open the latest save file.

Your kind support,

 

[Logit]

Option Explicit

Sub NewestFile()

    Dim MyPath As String

    Dim MyFile As String

    Dim LatestFile As String

    Dim LatestDate As Date

    Dim LMD As Date

    MyPath = "C:\Users\Documents\"

    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"

    MyFile = Dir(MyPath & "*.xls", vbNormal)

    If Len(MyFile) = 0 Then

        MsgBox "No files were found...", vbExclamation

        Exit Sub

    End If

    Do While Len(MyFile) > 0

        LMD = FileDateTime(MyPath & MyFile)

        If LMD > LatestDate Then

            LatestFile = MyFile

            LatestDate = LMD

        End If

        MyFile = Dir

    Loop

    Workbooks.Open MyPath & LatestFile

End Sub

 

[Monty]

Your code is almost there. You just need to initialize `LatestDate` with an early date before comparing it in the loop. Add the following line before the loop:

LatestDate = DateSerial(1900, 1, 1)

This ensures that `LatestDate` starts with a date far in the past, allowing any file's date to be greater than it. Your updated code should look like this:

Option Explicit

Sub NewestFile()
    Dim MyPath As String
    Dim MyFile As String
    Dim LatestFile As String
    Dim LatestDate As Date
    Dim LMD As Date

    MyPath = "C:\Users\Documents\"

    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"

    MyFile = Dir(MyPath & "*.xls", vbNormal)

    If Len(MyFile) = 0 Then
        MsgBox "No files were found...", vbExclamation
        Exit Sub
    End If

    ' Initialize LatestDate with an early date
    LatestDate = DateSerial(1900, 1, 1)

    Do While Len(MyFile) > 0
        LMD = FileDateTime(MyPath & MyFile)
        If LMD > LatestDate Then
            LatestFile = MyFile
            LatestDate = LMD
        End If
        MyFile = Dir
    Loop

    Workbooks.Open MyPath & LatestFile
End Sub

This modification ensures that `LatestDate` is properly initialized before comparing it in the loop.

 

[Debaser]

Your code is almost there. You just need to initialize `LatestDate` with an early date before comparing it in the loop. Add the following line before the loop:

No, you don't actually. Did you try the code?