List files in folder and subfolder with path to .txt file

Question

I have an excel sheet that has a cell that contains the path to a directory, i want a macro that searches the directory and any sub directories and lists the files in a .txt file, with the full path of each file.

This is currently what i have found that looks like it should find the files except the path is hard-coded and it doesn't do anything with the results.

Any ideas how i can change it to fit my needs?

Sub LoopThroughFiles()
Dim StrFile As String
StrFile = Dir("C:\Work\NCL\nCLs\histogram_addition\TestData\Input\RTE\")
Do While Len(StrFile) > 0
    Debug.Print StrFile
    StrFile = Dir
Loop
End Sub

http://stackoverflow.com/questions/9827715/get-list-of-subdirs-in-vba — Siddharth Rout, Nov 26 '13 at 14:17
Change `Dir("C:\Work\NCL\nCLs\histogram_addition\TestData\Input\RTE\")` to `Dir(Range("A1").Value)` (modify to the correct cell reference. This code prints the results in the Immediate pane in the VBE. You could modify it to create a text file and print each path to a new line in the file. — David Zemens, Nov 26 '13 at 14:48
I'm new to VBA just checked the immediate pane and its empty, any ideas why? — manish449, Nov 26 '13 at 15:09

score 8 · Accepted Answer · edited Nov 05 '14 at 05:53

Here's a method cobbled together from the FileSystemObject() examples using a recursive call. Apply a sort to the results if needed. You can also filter by .txt extension using other FileSystemObject() methods:

    Sub Sample()
        ShowFolderList ("C:\temp")
    End Sub

    Sub ShowFolderList(folderspec)
        Dim fs, f, f1, fc, s, sFldr
        Set fs = CreateObject("Scripting.FileSystemObject")
        Set f = fs.GetFolder(folderspec)
        Set fc = f.SubFolders
         For Each f1 In fc
            If Right(f1, 1) <> "\" Then ShowFolderList f1 & "\" Else ShowFolderList f1
        Next
        Set fc = f.Files
        For Each f1 In fc
            Debug.Print folderspec & f1.Name
        Next
    End Sub

Write to file:

    Option Explicit

    Dim file As Object
    Dim fs As Object

    Sub go()
        Set fs = CreateObject("Scripting.FileSystemObject")
        Set file = fs.OpenTextFile("C:\temp2\results3.txt", 2, True) ' 2=ForWriting, replace
        ShowFolderList "C:\temp\"
        file.Close
        MsgBox "done"
    End Sub

    Sub ShowFolderList(folderspec)
    On Error GoTo local_err
        Dim f, f1, fc, s, sFldr
        Set f = fs.GetFolder(folderspec)
        Set fc = f.SubFolders
         For Each f1 In fc
            If Right(f1, 1) <> "\" Then ShowFolderList f1 & "\" Else ShowFolderList f1
        Next
        Set fc = f.Files
        For Each f1 In fc
            file.writeline folderspec & f1.Name
        Next
     local_exit:
        Exit Sub
     local_err:
        MsgBox Err & " " & Err.Description
        Resume local_exit
        Resume
    End Sub

That works spot on thanks. How do i get that to print to a text file — manish449, Nov 26 '13 at 16:15
It is worth learning how to use FileSystemObject(). You can add a reference to Windows Scripting Runtime to enable intellisense. — rheitzman, Nov 26 '13 at 23:06
Thanks I've still got a lot to learn when it comes to vba, unfortunately I don't get much time to investigate. fingers crossed this will change soon. Thanks for help — manish449, Nov 27 '13 at 09:20
+ 1 :) I have edited your code. `Go` is a reserved word and sometimes when the sub is run, will give you an error message `Expected "To"`. — Siddharth Rout, Nov 05 '14 at 05:53

List files in folder and subfolder with path to .txt file

1 Answers1

Linked