Function to get list of Excel files in a folder

Hello everyone,

I am trying to get a list of the excel files I have in a folder

I have found this function on google:

Code:

---

PublicFunction GetFilesDir(ByVal sPath AsString, _OptionalByVal sFilter AsString)AsString()

    'dynamic array for names
    Dim aFileNames()AsString
    ReDim aFileNames(0)

    Dim sFile AsString
    Dim nCounter AsLong

    If Right(sPath,1)<>"\"Then
        sPath = sPath &"\"
    EndIf

    If sFilter =""Then
        sFilter ="*.*"
    EndIf

    'call with path "initializes" the dir function and returns the first file
    sFile = Dir(sPath & sFilter)

    'call it until there is no filename returned
    DoWhile sFile <>""

        'store the file name in the array
        aFileNames(nCounter)= sFile

        'subsequent calls without param return next file
        sFile = Dir

        'make sure your array is large enough for another
        nCounter = nCounter +1
        If nCounter > UBound(aFileNames)Then
            'preserve the values and grow by reasonable amount for performance
            ReDimPreserve aFileNames(UBound(aFileNames)+255)
        EndIf

    Loop

    'truncate the array to correct size
    If nCounter < UBound(aFileNames)Then
        ReDimPreserve aFileNames(0To nCounter -1)
    EndIf

    'return the array of file names
    GetFilesDir = aFileNames()
 EndFunction

---

Pretty dumb question, but how do I call the function? I have a type mismatch error every time I try.


Thank you all for your help!

Hi Edmond!
something like below:

Code:

---

Sub test()
Dim path$
path = "C:\Users\mdl1202\Desktop\EXCEL SAMPLE"  'input path what you want
MsgBox Join(GetFilesDir(path, "*.xls"), Chr(10))
End Sub

Public Function GetFilesDir(ByVal sPath As String, Optional ByVal sFilter As String) As String()
    'dynamic array for names
    Dim aFileNames() As String
    ReDim aFileNames(0)
    Dim sFile As String
    Dim nCounter As Long
    If Right(sPath, 1) <> "\" Then
        sPath = sPath & "\"
    End If
    If sFilter = "" Then
        sFilter = "*.*"
    End If
    'call with path "initializes" the dir function and returns the first file
    sFile = Dir(sPath & sFilter)
    'call it until there is no filename returned
    Do While sFile <> ""
        'store the file name in the array
        aFileNames(nCounter) = sFile
        'subsequent calls without param return next file
        sFile = Dir
        'make sure your array is large enough for another
        nCounter = nCounter + 1
        If nCounter > UBound(aFileNames) Then
            'preserve the values and grow by reasonable amount for performance
            ReDim Preserve aFileNames(UBound(aFileNames) + 255)
        End If
    Loop
    'truncate the array to correct size
    If nCounter < UBound(aFileNames) Then
        ReDim Preserve aFileNames(0 To nCounter - 1)
    End If
    'return the array of file names
    GetFilesDir = aFileNames()
End Function

---

The simplest:

Code:

---

Sub M_snb()
  msgbox createobject("wscript.shell").exec("cmd /c Dir G:\OF\*.xls /b/a-d").stdout.readall
End Sub

---

@snb
Wow~great!:bow:


--Okami

Thank you so much to both of you for your help!!