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