Function to get list of Excel files in a folder

[Edmond]

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:

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!

[大灰狼1976]

Hi Edmond!
something like below:

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

[snb]

The simplest:

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

[大灰狼1976]

@snb
Wow~great!


--Okami

[Edmond]

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