Folder of text files into excel sheet

Not tested.

Sub GetData2()
    Dim WS As Worksheet, NextRow As Long
    Dim Data() As Byte
    Dim FolderPath As Variant, Lines As Variant, oFile As Variant
    Dim oFiles As Object, oFolder As Object, oShell As Object
    Dim Text As String

    Set WS = Worksheets("Sheet1")

    Set oShell = CreateObject("Shell.Application")

    ' Add fixed path to the folder.
    FolderPath = "C:\Users\Owner\Documents"

    ' Let User choose the Folder.
    'Set oFolder = oShell.BrowseForFolder(0&, "Please Select the Folder with the Note Files.", 17)
    'If oFolder Is Nothing Then Exit Sub Else FolderPath = oFolder.Self.Path

    Set oFolder = oShell.Namespace(FolderPath)

    Set oFiles = oFolder.Items

    oFiles.Filter 64, "*.csv;*.txt"

    For Each oFile In oFiles
        Open FolderPath & "\" & oFile For Binary Access Read As #1
        ReDim Data(LOF(1))
        Get #1, , Data
        Close #1

        Text = StrConv(Data, vbUnicode)

        With WS
            NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
            .Range("A" & NextRow) = oFile
            .Range("B" & NextRow) = Text
        End With
    Next oFile

Copy the code into a standard module.

Change path as required to point to text files. While the cursor is inside the code, press F8.