Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: List all files in a folder

  1. #1
    Guest

    Default

    I am writing a macro to get a list of subfolders and files in a folder and link the cells to the corresponding files. Here is the macro


    Sub ShowAllList(folderspec)
    Dim fs, f, f1, f2, f3, fc, fd, fe
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(folderspec)
    Set fc = f.Files
    Set fd = f.subfolders
    i = 1
    For Each f1 In fc
    Cells(i, 1) = f1.Name
    ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 1), Address:=f1
    i = i + 1
    Next
    For Each f2 In fd
    Cells(i, 1) = f2.Name
    i = i + 1
    Set fe = f2.Files
    For Each f3 In fe
    Cells(i, 1) = f2.Name
    Cells(i, 2) = f3.Name
    ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 2), Address:=f3
    i = i + 1
    Next
    Next
    End Sub

    Sub listallfiles()
    ShowAllList ("h:kcy")
    End Sub


    It works if in there is only one level of subfolders (which is usually not the case). I want to rewrite it so that it can handle arbitray numbers of levels of subfolders. Can anybody help? Thank you.

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    Have you tried the FileSearch Object?



  3. #3
    Guest

    Default

    Example of what dave suggested;

    Sub List_Files()
    Dim cDir as string
    cDir = "C:Downloads"

    With Application.FileSearch
    .NewSearch
    .FileType = msoFileTypeAllFiles
    .LookIn = cDir
    .SearchSubFolders = True
    .Execute
    For i = 1 To .FoundFiles.Count
    Cells(i, 1) = .FoundFiles(i)
    Next

    End sub


    Ivan

  4. #4
    Guest

    Default

    Yes

  5. #5
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Should have logged in 1st
    for the last 2 posts

  6. #6
    Board Regular Bruno's Avatar
    Join Date
    Feb 2002
    Location
    Flanders
    Posts
    484
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Nice code Ivan
    There's only a END WITH missing before END SUB
    Let's build the code a bit more...
    Be sure there is a Userform1 with a Listbox1

    Sub List_Files()
    Dim cDir As String
    cDir = "C:Downloads"

    UserForm1.ListBox1.Clear
    With Application.FileSearch
    .NewSearch
    .FileType = msoFileTypeAllFiles
    .LookIn = cDir
    .SearchSubFolders = True
    .Execute
    For i = 1 To .FoundFiles.Count
    Cells(i, 1) = .FoundFiles(i)
    UserForm1.ListBox1.AddItem .FoundFiles(i)
    Next
    End With
    'show the list
    UserForm1.Show
    ' as an example open the selected file
    Workbooks.Open FileName:=UserForm1.ListBox1.Value
    End Sub





    _________________


    Bruno


    [ This Message was edited by: Bruno on 2002-03-06 23:14 ]

  7. #7
    Guest

    Default

    On 2002-03-06 22:33, Anonymous wrote:
    Example of what dave suggested;

    Sub List_Files()
    Dim cDir as string
    cDir = "C:Downloads"

    With Application.FileSearch
    .NewSearch
    .FileType = msoFileTypeAllFiles
    .LookIn = cDir
    .SearchSubFolders = True
    .Execute
    For i = 1 To .FoundFiles.Count
    Cells(i, 1) = .FoundFiles(i)
    Next

    End sub


    Ivan
    Thank you.
    But can I link the file names to the files, list details such as file sizes etc.

    I tried the following (I don't understand FileSearch well):

    Sub ShowFileList(folderspec)
    Dim fs, f, f1, s, sf
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(folderspec)
    Set sf = f.Files
    i = ActiveCell.Row
    For Each f1 In sf
    Cells(i, 1) = f1.Path
    Cells(i, 2) = f1.Size
    Cells(i, 3) = f1.DateLastModified
    ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 1), Address:=f1
    i = i + 1
    Next
    Cells(i, 1).Select
    End Sub

    Sub Showdd(folderspec)
    Dim fs, f, f1, s, sf
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(folderspec)
    ShowFileList (f)
    Set sf = f.SubFolders
    For Each f1 In sf
    Showdd (f1)
    Next
    End Sub


    Sub ShowAll()
    Cells(1, 1).Select
    Showdd ("h:kcy")
    End Sub

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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