Get FileName of Last Modified Date 1

[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.

I initially placed it in the wrong forum (Version 5 & 6 Forum) and was being re-directed by MIS to here.

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

 

[PHV]

You wanted this ?
Sheets(1).Range("IV2") = fs.FoundFiles(loc)

Just a note: how is fdt assigned a value ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886