Create a text file with a list of all files inside a folder

Question

I'd like to create a .txt file inside the same folder to enlist all the files and to resume all the contents of the files (txt files)

I tried with the next code to programming this action but it has not work,

The input variable as a string for the directory must be taken from a dialog box

Any ideas to improve this?

Dim Dir1 As String

Dir1 = InputBox("Input the location of the files")
Order1 = "type *.b*>>mat.txt"
Order2 = "dir/b>lista.txt"

Call Shell("cmd.exe /S /K" & Order1, vbNormalFocus)
Call Shell("cmd.exe /S /K" & Order2, vbNormalFocus)

End Sub

http://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba — Synoon, Nov 26 '15 at 10:57
Sounds like homework... search trough SO or use google, there are many examples out there if you split your task into subtasks (Loop trough files in directory, Create txt file in directory, read content of txt file etc.) — Xavjer, Nov 26 '15 at 10:59

score 0 · Answer 1 · answered Nov 26 '15 at 11:11

Use Following Sub: It will give you list of all files in folder you will select. Select any cell in excel. Trigger the sub. It will prompt to select a folder. Select Any folder. Get list of all file of that folder.

    Sub AllFiles()
        Dim xRow As Long
        Dim xDirect$, xFname$, InitialFoldr$
        InitialFoldr$ = "C:\" '<<< Startup folder to begin searching from

        With Application.FileDialog(msoFileDialogFolderPicker)
            .InitialFileName = Application.DefaultFilePath & "\"
            .Title = "Please select a folder to list Files from"
            .InitialFileName = InitialFoldr$
            .Show
                If .SelectedItems.Count <> 0 Then
                  xDirect$ = .SelectedItems(1) & "\"
                   xFname$ = Dir(xDirect$, 7)

                    Do While xFname$ <> ""
                      ActiveCell.Offset(xRow) = xFname$
                       xRow = xRow + 1
                      xFname$ = Dir
                    Loop
                End If
        End With
End Sub

Perfect, I only have to implement the other function "type.." for the MS-DOS command but I'll operate in the same way you did, very Smart, thanks a lot! — Marcos Busto, Nov 26 '15 at 15:44
I have also ms batch code to get files name in a text file. It is only two line code. Do you need that? — Harun24hr, Nov 27 '15 at 03:11

score 0 · Answer 2 · answered Nov 26 '15 at 11:22

Using the Dir method :

Sub Dir_Use()
Dim i As Long
Dim FileList()
Dim FSO As Object
Dim oFile As Object
Dim strPath As String

strPath = "c:\test"
Set FSO = CreateObject("Scripting.FileSystemObject")
FileList = Read_FilesNames_From_Folder(strPath)
Set oFile = FSO.CreateTextFile(strPath)

For i = LBound(FileList) To UBound(FileList)
    oFile.WriteLine FileList(i)
Next i

oFile.Save
oFile.Close

Set FSO = Nothing
Set oFile = Nothing

End Sub

Function Read_FilesNames_From_Folder(InitilFolderPath As String) As Variant

With Application
    .EnableEvents = False
    .DisplayAlerts = False
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

Dim FileName As String, _
    FolderPath As String, _
    Results()
ReDim Results(0)

With Application.FileDialog(msoFileDialogFolderPicker)
    .InitialFileName = InitilFolderPath
    If .Show = True Then
        FolderPath = .SelectedItems(1)
    Else
        Exit Function
    End If
End With

FileName = Dir(FolderPath & "*.xlsx")

Do While FileName <> ""
    Results(UBound(Results)) = FileName
    ReDim Preserve Results(UBound(Results) + 1)
    FileName = Dir
Loop
ReDim Preserve Results(UBound(Results) - 1)

With Application
    .EnableEvents = True
    .DisplayAlerts = True
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With

Read_FilesNames_From_Folder = Results
End Function

Or with your initial code, something like this to select folder via FileDialogFolderPicker :

Sub test_Marcos_Busto()
Dim Dir1 As String

With Application.FileDialog(msoFileDialogFolderPicker)
    .InitialFileName = "c:\test"
    If .Show = True Then
        Dir1 = .SelectedItems(1)
    Else
        Exit Sub
    End If
End With

'You don't use Dir1 afterwards... So, I'm not sure where to go afterwards
Order1 = "type *.b*>>mat.txt"
Order2 = "dir/b>lista.txt"

Call Shell("cmd.exe /S /K" & Order1, vbNormalFocus)
Call Shell("cmd.exe /S /K" & Order2, vbNormalFocus)


End Sub

@MarcosBusto : Glad I could help, but please take a minute to take the tour and see how you can accept answers and vote on posts : http://stackoverflow.com/tour — R3uK, Nov 26 '15 at 15:43

Create a text file with a list of all files inside a folder

2 Answers2