Importing Tab Delimited Data into Excel

The following code imports tab delimited data into excel using VBA. The file imported is a text file. You can import other types of files such as .xml and .csv, and also database tables and queries from Access projects, Access database files, ODBC, other Excel files, and others. The easiest way to import them with VBA is to first record an import with the Macro Recorder.
(click here to learn how to record a macro)

Sub Macro1()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\tab-file.txt", _
        .Name = "tab-file"
        .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)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Return from Tab Delimited Data into Excel to VBA Code Samples

Return to our Homepage