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

Users are saying about us...

  1. Hello,

    Can you please advise if you have any code which allow the user to manually key in the file name on the msg box and import the data directly to another excel sheet?

    Thanks,
    Lawrence

  2. Hi, thank you for your post!

    I’m wondering if there is any way to find and attach to an email (or just open, as in this example), say 5, most recently created/saved files? What condition (and where) to put in order to reach my goal?

    I would like to make it with “DIR Statement” and loop – this is crucial: DIR and Loop(s).

    with very warm regards
    Wojciech Szajnar

Leave a Reply to Neeta gupta Cancel 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>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube