Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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:

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

    VB:
    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
    1st March 2010
    Location
    God's Own Country
    Posts
    3,614

    Re: List All Files In A Folder

    VB:
    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 4 users browsing this thread. (0 members and 4 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, 05:47
  2. Hyperlink List Of All Files In Folder
    By PY_ in forum EXCEL HELP
    Replies: 9
    Last Post: June 7th, 2008, 11:33
  3. Replies: 1
    Last Post: March 21st, 2008, 10:12
  4. List Of Links To All Files In Folder
    By General Specific in forum EXCEL HELP
    Replies: 4
    Last Post: March 15th, 2008, 03: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, 14: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