Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2008
    Posts
    189

    Question Unanswered: How to list all the files in a folder?

    Hello,
    I would like to know how in VBA to list all the files that are on a specific folder.

    Like doing a "DIR *.*", and getting the result on some collection or array or something like that.

    Also, is there any way to recognize if is it a folder or a file the content of the path?

    Thanks in advance for your help.

    Saludos,
    pepemosca

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy. If you go to www.vbaexpress.com and register (great site - many fantastic coders and Excel experts), they have many articles, some of them already have the code written for you to use.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Apr 2008
    Posts
    189
    Great reference!
    Thank you for your suggestion.

    BTW, I've found the solution here
    http://vbaexpress.com/kb/getarticle.php?kb_id=733
    http://vbaexpress.com/kb/getarticle.php?kb_id=781
    http://vbaexpress.com/kb/getarticle.....php?kb_id=837

    I just post the links, to leave a reference at dBforums, but as you said: you must be registered at VBA Express to be able to see the code.

    All the abobe are for Excel VBA 2003
    Last edited by pepemosca; 06-16-08 at 12:27.

  4. #4
    Join Date
    Apr 2008
    Posts
    189
    Oh, too bad.
    The "Application.FileSearch" doesn't work with Excel 2007

    I need to make a new Google Search :P

  5. #5
    Join Date
    Apr 2008
    Posts
    189
    Here is some code that works:
    http://www.ozgrid.com/forum/showpost...50&postcount=5

    Code:
    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

Posting Permissions

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