Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: List All Files In A Folder

  1. #1
    Join Date
    17th February 2007
    Posts
    24

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th September 2005
    Location
    Hampshire, UK
    Posts
    1,278

    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    17th February 2007
    Posts
    24

    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?

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    18th September 2005
    Location
    Hampshire, UK
    Posts
    1,278

    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    17th February 2007
    Posts
    24

    Re: List All Files In A Folder

    Thanks so very much

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    27th May 2010
    Posts
    1

    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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    2nd March 2010
    Location
    God's Own Country
    Posts
    3,779

    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

Thread Information

Users Browsing this Thread

There are currently 3 users browsing this thread. (0 members and 3 guests)

Possible Answers

  1. List Files In A Folder In A Worksheet
    By holbrookp in forum EXCEL HELP
    Replies: 8
    Last Post: February 24th, 2011, 06:47
  2. Hyperlink List Of All Files In Folder
    By PY_ in forum EXCEL HELP
    Replies: 9
    Last Post: June 7th, 2008, 12:33
  3. Replies: 1
    Last Post: March 21st, 2008, 11:12
  4. List Of Links To All Files In Folder
    By General Specific in forum EXCEL HELP
    Replies: 4
    Last Post: March 15th, 2008, 04:48
  5. vba for making a list of xls files in a folder...
    By mjschukas in forum EXCEL HELP
    Replies: 3
    Last Post: June 16th, 2004, 15:57

Bookmarks

Posting Permissions

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