Snasui Excel Forum

Code: Select all

Sub ListAllFileNames()
Dim oFSO As Object
Dim ofolder As Object
Dim ofile As Object
Dim i As Integer
Dim LResult As Date
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set ofolder = oFSO.GetFolder("D:\KPI 2022")
For Each ofile In ofolder.Files
Cells(i + 2, 1) = ofile.Name
i = i + 1
LResult = FileDateTime(ofile)
Cells(i + 1, 2) = LResult
Next ofile
End Sub

Code: Select all

Sub ListAllFileNames()
    Dim oFSO As Object
    Dim ofolder As Object
    Dim ofile As Object
    Dim i As Integer
    Dim LResult As Date
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set ofolder = oFSO.GetFolder("D:\KPI 2022")
    
    With ActiveSheet
        If .Range("a2").Value = "" Then
            i = 2
        Else
            i = .Range("a" & .Rows.Count).End(xlUp).Row + 1
        End If
        For Each ofile In ofolder.Files
            If Application.CountIfs(.Range("a:a"), ofile.Name) = 0 Then
                Cells(i, 1) = ofile.Name
                Cells(i, 2) = FileDateTime(ofile)
                i = i + 1
            End If
        Next ofile
    End With
End Sub