Excel vba select rows from text file

Question

I am opening a large text file into excel sheet using VBA macro. However, my requirement is to only import few specific rows into the excel sheet which match a particular column values. As an example,

Name      Age
--------------
A1        20
A2        21
A3        20
A4        21
A5        22
A6        22

So, I wanted to import with criteria Age = 20 or 21. However, I do not want to use AutoFilter. I just wanted the vba to select the rows that match my filter and display them and ignore all the others. I used autofilter, but it is loading the whole data and showing only the rows of my interest. The code that I wrote

    With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;" + full_path, Destination:=Range( _
    "A1"))
    .Name = file_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 = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
    End With
    Range("A1").Select

Can you please help?

Raugmor Raugmor · Accepted Answer · 2014-12-08T19:19:35

you can export data using the option get external data > from other sources > from microsoft query where you have to do few more actions to set up your text file as source, but once done you can add it as a connection which includes filters prior to populating onto the spreadsheet. you can read up more about how to set it up here: About using Microsoft Query to retrieve external data

Excel vba select rows from text file

1 Answers