VBA To Open Latest File in Folder

 

How to Open Newest File in a Folder in Microsoft Excel

 

In case you want a VBA code which will allow you to open the recently saved file in the folder with just a click on macro button.

Question): I have a team of 10 people & I am urgently looking for a macro that will help me in opening the latest file saved by team member so that I do not have to go the path wherein the file is saved.

 

We need to follow the below steps:

  • Click on Developer tab
  • From Code group, select Visual Basic

 

img1

 

  • Enter the following code in the worksheet module

 

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

 

img2

 

In this way, using macro code, we will be able to open the latest file saved by the team member.

 

image 4

Download – How to open newest file in a folder – xlsm



Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>