The procedure below can be used to get an ADO recordset from a text file and fill in the result in a worksheet.

Sub GetTextFileData(strSQL As String, strFolder As String, rngTargetCell As Range)
' example: GetTextFileData "SELECT * FROM filename.txt", "C:\FolderName", Range("A3")
' example: GetTextFileData "SELECT * FROM filename.txt WHERE fieldname = 'criteria'", "C:\FolderName", Range("A3")
Dim cn As ADODB.Connection, rs As ADODB.Recordset, f As Integer
    If rngTargetCell Is Nothing Then Exit Sub
    Set cn = New ADODB.Connection
    On Error Resume Next
    cn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & strFolder & ";Extensions=asc,csv,tab,txt;"
    On Error GoTo 0
    If cn.State <> adStateOpen Then Exit Sub
    Set rs = New ADODB.Recordset
    On Error Resume Next
    rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
    On Error GoTo 0
    If rs.State <> adStateOpen Then
        cn.Close
        Set cn = Nothing
        Exit Sub
    End If
    ' the field headings
    For f = 0 To rs.Fields.Count - 1
        rngTargetCell.Offset(0, f).Formula = rs.Fields(f).Name
    Next f
    rngTargetCell.Offset(1, 0).CopyFromRecordset rs ' works in Excel 2000 or later
    'RS2WS rs, rngTargetCell ' works in Excel 97 or earlier
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

The procedure can be used like this:

Sub TestGetTextFileData()
    Application.ScreenUpdating = False
    Workbooks.Add
    GetTextFileData "SELECT * FROM filename.txt", "C:\FolderName", Range("A3")
'    GetTextFileData "SELECT * FROM filename.txt WHERE fieldname = 'criteria'", "C:\FolderName", Range("A3")
    Columns("A:IV").AutoFit
    ActiveWorkbook.Saved = True
End Sub

Replace filename.txt with the name of the text file you want to get data from.
Use square brackets around the filename if it contains any space characters:
"SELECT * FROM [file name.txt]"
Replace C:\FolderName with the name of the folder where the text file is saved.

The first row in the text file will be used as column headings/field names.
Each column with data must be separated with the list separator character that is used in the regional settings in the Control Panel. In Norway this usually is a semicolon (;), in other countries this can be a comma (,).

See information about using a Schema.ini file to set information about e.g. column data types and separator characters.

You'll find the procedure RS2WS by clicking on this link.

The macro examples assumes that your VBA project has added a reference to the ADO object library. You can do this from within the VBE by selecting the menu Tools, References... and selecting the Microsoft ActiveX Data Objects x.x Object Library.

Like this post