vlookup from Last Modified File in Folder

Hi

Please help me to correct the below VBA Code.


Step1

I am opening last modified file from folder. ("C:\test\source.xls\) and then selecting Sheet1.

This is my source file for making vlookup formula.

Now in my current workbook D5 i am writing vlookup formuala as below

"=VLOOKUP(RC2&RC3,'[LFile]Sheet1'!R17C3:R301C17,10,0)"

The code is searching for "LFile" by default opening my documents folder.

Please help me to correct the code

Code:

---

Sub Macro7()

Dim wb As Workbook: Set wb = ActiveWorkbook

Dim i As Long

Dim path As String, LFile As String, rng As Range, Ldate As Date

Dim fso  As Object, fld As Object, File As Object, lr As Long, lc As Long

path = "C:\test\source.xls\" ' ! Change "FolderName" to your Actual Folder

'Path = ThisWorkbook.Path & "\FolderName\"

lr = Cells(Rows.Count, 2).End(xlUp).Row: lc = Cells(5, Columns.Count).End(xlToLeft).Column

Set rng = Range(Cells(5, 2), Cells(lr, lc))

Set fso = CreateObject("Scripting.FileSystemObject")

If fso.GetFolder(path).Files.Count > 0 Then

For Each File In fso.GetFolder(path).Files

If File.DateLastModified > Ldate Then

LFile = File: Ldate = File.DateLastModified

End If

Next File

With Workbooks.Open(LFile)

Sheets("Sheet1").Select

End With

End If

ActiveWindow.ActivateNext

Range("D5").Select

ActiveCell.FormulaR1C1 = _

"=VLOOKUP(RC2&RC3,'[LFile]Sheet1'!R17C3:R301C17,10,0)"

Range("D5").Select

Selection.AutoFill Destination:=Range("D5:D6")

End Sub

---