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.
Step 2
Insert a module in which we will the VBA code.
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
Step 4
Make a reference to “Microsoft Scripting Runtime” and “Microsoft Activex Data Objects” library by clicking Tools->References in VB editor.
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.
