Importing Multiple .txt files

[MrLookout9]

Hello and thank you for your expertise.

Here is my situation. I have approx. 500 text files all residing in one folder. This folder only contains text files. Each text file has approx. 100 rows of data, tab delimited.

I would like to import every row from each file into one sheet in excel.

I think this can be done with a macro, but would be open to a different approach.

Thanks,

Adam

 

[guerillaunit]

Sub test1()
 
Dim objfile As Variant
Dim objfolder As Variant
Dim objfs As Variant
Dim lastactiverow As Long
 
Set objfs = CreateObject("scripting.filesystemobject")
Set objfolder = objfs.getfolder(urfolderdestination)
lastactiverow = 1
 
For Each objfile In objfolder.Files
    If Right(objfile.Name, 4) = ".txt" Then
        With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;urfolderdestination" & objfile.Name, Destination:= _
            Range("A" & lastactiverow))
            .Name = "test"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
        lastactiverow = ActiveSheet.UsedRange.Rows.Count + 1
    Else
    End If
Next objfile
End Sub

Change the instances of "urfolderdestination" to your actual folder path

 

[MrLookout9]

Thank you this worked perfectly!