Consulting

Results 1 to 5 of 5

Thread: Function to get list of Excel files in a folder

  1. #1
    VBAX Regular
    Joined
    Oct 2018
    Posts
    34
    Location

    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:


    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!
    Edmond

  2. #2
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    326
    Location
    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

  3. #3
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,461
    The simplest:

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

  4. #4
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    326
    Location
    @snb
    Wow~great!


    --Okami

  5. #5
    VBAX Regular
    Joined
    Oct 2018
    Posts
    34
    Location
    Thank you so much to both of you for your help!!
    Edmond

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •