Import/retrieve newest date from file name.

[Totalnovice]

Hello,
My problem is probably very simple for you experienced users. So I have file directory, where at regular basis new html-file is added (picrure below). The files have the date in the file name and also as modified date.

What I'd like to do is to have VBA that retrieves the date of the newest file to excel.

Being a novice with VBA I've just googled and tried to find the solution but haven't managed to find the right one yet. So far I've gotten to point where I can get file names imported to excel with VBA (working somewhat alright). Hopefully I can get some help here. Thanks

-Totalnovice

 

[bebo021999]

Assuming there are 3 files in folder: "E:\TestFolder"
Below code will paste the newest date in A1, and the file name in A2:

Option Explicit
Sub GetNewestDate()
Dim maxD As Long, xFolder As Object, xF As Object, xFile As Object, xDate As String, mFile As String
Set xF = CreateObject("Scripting.FileSystemObject")
Set xFolder = xF.getFolder("E:\TestFolder")
    For Each xFile In xFolder.Files
        xDate = Mid(xFile.Name, 6, 8) + 0
        If xDate > maxD Then
            maxD = xDate
            mFile = xFile.Name
        End If
    Next
Range("A1").Value = maxD
Range("A2").Value = mFile
End Sub

 

[Totalnovice]

Thank you so much. This did the trick almost perfectly. Only minor thing is that it returns the date as yyyymmdd. In the cells it can be changed to dd.mm.yyyy ie. by using DATE and VALUE, but how should this be implemeted in VBA so it would do it automatically?

 

[bebo021999]

Try:

Range("A1").Value = DateSerial(Left(maxD, 4), Mid(maxD, 5, 2), Right(maxD, 2))

 

[Totalnovice]

Perfect. Thanks.