Announcement

Collapse
No announcement yet.

List All Files In A Folder

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • List All Files In A Folder



    I want to run a macro to list the names of all files in a folder in a worksheet. I want the file names along with extensions. Please help

  • #2
    Re: List All Files In A Folder

    Hi

    The following will give you the full path & file name:

    Code:
    Sub ListAllFiles() 
    Dim fs As FileSearch, ws As Worksheet, i As Long 
    Set fs = Application.FileSearch 
    With fs 
        .SearchSubFolders = False ' set to true if you want sub-folders included 
        .FileType = msoFileTypeAllFiles 'can modify to just Excel files eg with msoFileTypeExcelWorkbooks 
        .LookIn = "C:\"  'modify this to where you want to serach 
        If .Execute > 0 Then 
            Set ws = Worksheets.Add 
            For i = 1 To .FoundFiles.Count 
                ws.Cells(i, 1) = .FoundFiles(i) 
            Next 
        Else 
            MsgBox "No files found" 
        End If 
    End With 
    End Sub

    Comment


    • #3
      Re: List All Files In A Folder

      Thanks a lot. That is of help

      But is it possible to list the the filename alone without the full path or the file extension?

      Comment


      • #4
        Re: List All Files In A Folder

        Sure:

        Code:
        Sub ListAllFiles()
        Dim fs As FileSearch, ws As Worksheet, i As Long
        Set fs = Application.FileSearch
        With fs
            .SearchSubFolders = False ' set to true if you want sub-folders included
            .FileType = msoFileTypeAllFiles 'can modify to just Excel files eg with msoFileTypeExcelWorkbooks
            .LookIn = "C:\"  'modify this to where you want to serach
            If .Execute > 0 Then
                Set ws = Worksheets.Add
                For i = 1 To .FoundFiles.Count
                    ws.Cells(i, 1) = Mid$(.FoundFiles(i), InStrRev(.FoundFiles(i), "\") + 1)
                Next
            Else
                MsgBox "No files found"
            End If
        End With
        End Sub

        Comment


        • #5
          Re: List All Files In A Folder

          Thanks so very much

          Comment


          • #6
            Re: List All Files In A Folder


            Hello team! this code not work in excel 2007!, the object filesearch is blocked.
            I can this that other way?,
            Greetings ALFATRON

            Comment


            • #7


              Re: List All Files In A Folder

              Code:
              Sub ListAllFile()
              
                  Dim objFSO As Object
                  Dim objFolder As Object
                  Dim objFile As Object
                  Dim ws As Worksheet
                  
                  Set objFSO = CreateObject("Scripting.FileSystemObject")
                  Set ws = Worksheets.Add
                  
                  'Get the folder object associated with the directory
                  Set objFolder = objFSO.GetFolder("C:\")
                  ws.Cells(1, 1).Value = "The files found in " & objFolder.Name & "are:"
                  
                  'Loop through the Files collection
                  For Each objFile In objFolder.Files
                      ws.Cells(ws.UsedRange.Rows.Count + 1, 1).Value = objFile.Name
                  Next
                  
                  'Clean up!
                  Set objFolder = Nothing
                  Set objFile = Nothing
                  Set objFSO = Nothing
              
              End Sub
              Cheers,

              S M C

              Click To Read: How To Use Tags In Your Threads/Posts
              Please take time to read Forum Rules before posting
              Message To Cross Posters

              Comment

              Working...
              X