get path of latest file in folder with macro

hi there,

Here`s the part of the code I want to change:

ActiveWorkbook.Worksheets.Add.Name = "Hilfstabelle"

Range("B1").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP('C:\Users\famichalsk\Desktop\ExportSAP[export.XLSX]Sheet1'!R[1]C[10],'C:\Users\famichalsk\Desktop\ExportSAP\
[export.XLSX]Sheet1'!C[10],1,FALSE)"

Generally the problem is that the part in beetween the brackets [] should change when you have a file in the specific folder, which has a current date. I already tried to check all the files with:

...all the variables...

strPath = "C:\Users\famichalsk\Desktop\ExportSAP"

' Use Microsoft Scripting runtime.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)

' Check date on each file in folder.
For Each objFile In objFolder.Files
If objFile.DateLastModified > varDate Then
varDate = objFile.DateLastModified
strName = objFile.Name
End If
Next 'objFile

...

and then paste the variable strName in the [] brackets, but it didn`t work out how I actually imagined...


I hope you could understand the way I explained it and you can figure out where the problem is and how I can solve it.

Thanks so far,

Fabian

Thank you...this one worked out perfectly...you solved my Problem