Open Multiple .txt Files with Same Settings

[diderooy]

I have dozens of .txt files that I want to import into Excel so that they can be formatted, merged, referenced, etc. There are a few columns of data that are formatted in a way that imports them as dates instead of as numbers by default. I've figured out how to import them with the correct delimiters and the appropriate column headers (changing certain columns to text works like a charm!).

I'm wondering how I can pull files all at once, without having to manually change those settings manually each time. Is there VBA code to, say, pull all .txt files in a folder into a single excel document, all as separate worksheets, with the same import wizard settings?

 

[tyger0951]

Record a macro when you import a txt file. If they all have the same settings than you can just take the macro and change the file path and paste location for each txt file.


Gonna look something like this. But you'll want to record your a macro while you import a txt file
to get all the correct settings for your files.

Sub PullTxt()


Worksheets.Add 'Make the sheet for the data from txt file to go into




With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\Tyger\Desktop\test2.txt", Destination:=Range("$A$1"))
        ' Update path
        .Name = "test2" ' update connection name
        .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 = True
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    
    
    Worksheets.Add 'Make the sheet for the data from txt file to go into




With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\Tyger\Desktop\test3.txt", Destination:=Range("$A$1"))
        ' Update path
        .Name = "test3" ' update connection name
        .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 = True
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With






End Sub