Get FileName of Last Modified Date

[simunjan62]

Dear VBA expert,

I am stuck with this how to get the filename of the LastModifiedDate and input the filename to a cell. I managed to open it from C:\Data, copy 2 sheets over but was unable to place the filename in cell IV2. I hope someone can help me on this. Any help is greatly appreciated.

Sub GetLastModifiedFileName()
Dim fs As FileSearch, ws As Worksheet, i As Long, loc As Long, fdt As Double
Dim wsh As Worksheet
Set fs = Application.FileSearch
    With fs
        .SearchSubFolders = False
        .FileType = msoFileTypeExcelWorkbooks
        .LookIn = "C:\Data"
        If .Execute > 0 And .FoundFiles.Count > 0 Then
            loc = 0
            	For i = 1 To .FoundFiles.Count
               		If FileDateTime(.FoundFiles(i)) > fdt Then loc = i
            	Next i
           	Workbooks.Open .FoundFiles(loc)
                Else
            Exit Sub
        End If
    End With
       
    Sheets(Array("Region", "Output")).Select
    Sheets(Array("Region", "Output")).Copy After:=Workbooks("TEMPLATE.xls").Sheets(1)  
    Windows("TEMPLATE.xls").Activate
    Sheets(1).Range("IV3") = Environ("Username")
    Sheets(1).Range("IV4") = Now()
End Sub

 

[strongm]

Allow me to point you to forum707

 

[simunjan62]

Thanks. I have posted it to forum 707 now.