Public Sub intoSheet()
    Dim fso As Object
    Dim Ordner As Object
    Dim Datei As Object
    Dim Spalte As Long
    Dim Zeile As Long
    Dim Dateiname As String
    Dim Ordnerpfad As String
    Ordnerpfad = "PFAD eintragen"
    Dim Tabelle As Worksheet
    Set Tabelle = ThisWorkbook.Sheets("TABELLE eintragen")
    Spalte = 1
    Zeile = 1
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set Ordner = fso.GetFolder(Ordnerpfad)
    For Each Datei In Ordner.Files
        If Right(Datei.Name, 4) = ".txt" Then
            Dateiname = Datei.Path
            Tabelle.Cells(Zeile, Spalte).Value = Dateiname
            If Not holeDaten(Ordnerpfad, Datei.Name, Tabelle.Cells(2, Spalte)) Then
                Tabelle.Cells(2, Spalte).Value = "'<-----Fehler----->"
            End If
            Spalte = Spalte + 1
        End If
    Next Datei
    Set Ordner = Nothing
    Set fso = Nothing
End Sub

Public Function holeDaten(ByVal strOrdner As String, ByVal strDatei, ByVal Zelle As Range) As Boolean
    On Local Error GoTo holeDatenERR
    Dim bRet As Boolean
    Dim strConn As String
    Dim qtText As QueryTable
    bRet = False
    strConn = "TEXT;" & strOrdner & "\" & strDatei
    Set qtText = ActiveSheet.QueryTables.Add(Connection:=strConn, Destination:=Zelle)
    With qtText
        .Name = strDatei
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 65001
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1)
        .TextFileFixedColumnWidths = Array(1024)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    ActiveSheet.QueryTables(qtText.Name).Delete
    bRet = True
holeDatenOUT:
    holeDaten = bRet
    Exit Function
holeDatenERR:
    bRet = False
    Resume holeDatenOUT
End Function

Good Luck