Sub testit()
    myvar = FileList(ActiveWorkbook.Path)
    If TypeName(myvar) <> "Boolean" Then
        For i = LBound(myvar) To UBound(myvar)
            Cells(i + 1, 1).Value = myvar(i)
        Next
    Else
         MsgBox "No files found"
    End If
End Sub
 
Function FileList(fldr As String, Optional fltr As String = "*.*") As Variant
    Dim sTemp As String, sHldr As String
    If Right$(fldr, 1) <> "\" Then fldr = fldr & "\"
    sTemp = Dir(fldr & fltr)
    If sTemp = "" Then
        FileList = False
        Exit Function
    End If
    Do
        sHldr = Dir
        If sHldr = "" Then Exit Do
        sTemp = sTemp & "|" & sHldr
     Loop
    FileList = Split(sTemp, "|")
End Function