VBA To Open Latest Excel Workbook

In this post we will see how to open the latest excel file from the folder using VBA code.

The problem can be broken into three parts i.e. first we will create a list of excel; l file in the folder and then we will sort the last modified date value for those files in the folder and ultimately we will open the latest excel file in the folder using VBA code.




Step 1

Open VB editor from Develop the workbook in which we will write the code to open the most recent excel file from a folder.

Open developer tab to write VBA to open latest excel file

Step 2

Insert a module in which we will the VBA code.

Insert a module in the VB editor

Step 3

Now double click the newly created module to open that and paste the below code in the module.

Sub OPen_Latest_File()

   Dim rs As ADODB.Recordset

   Dim fs As FileSystemObject

   Dim Folder As Folder

   Dim File As File

   ‘create a recordset to store file info

   Set rs = New ADODB.Recordset

   rs.Fields.Append “FileName”, adVarChar, 100

   rs.Fields.Append “Modified”, adDate

   rs.Open

   ‘build the list of files and sort

   Set fs = New FileSystemObject

   Set Folder = fs.GetFolder(“D:\Excel Website”)

   For Each File In Folder.Files

      rs.AddNew

      rs(“FileName”) = File.Path

      rs(“Modified”) = File.DateLastModified

   Next

   rs.Sort = “Modified DESC”

   ‘process the first 2 files

   rs.MoveFirst

   Set wb2 = Workbooks.Open(rs.Fields(“FileName”).Value)

End Sub




VBA code to open most recent file

Step 4

Make a reference to “Microsoft Scripting Runtime” and “Microsoft Activex Data Objects” library by clicking Tools->References in VB editor.

Enable reference to Microsoft scripting runtime to open latest excel file from a folder

This code requires the folder location from which we want to open the most latest excel file using VBA.

Now run the code by pressing F5 or by pressing play button in the VB editor and this code will open the most recent excel file.

Hope this helped.



Share The Knowledge

Random Posts

  • Return Font Name And Font Size In VBA

    return font size i excel return font name inn excel, vba to find font size, vba to find font name, get font size in excel, get font name in excel

    Share The Knowledge
  • Unit Conversion In Excel

    Let’s see how to do unit conversion in excel. We will see how we could leverage a simple convert function […]

    Share The Knowledge
  • Sort By Column/Rearrange Columns In Excel

    Let’s see how to sort by column in excel. There are several instances when we require the data to be […]

    Share The Knowledge
  • Split Date And Time In Excel

    We will see in this post how to segregate date part and time part from a datetime value in a […]

    Share The Knowledge