» Import data from a text file (ADO) using VBA in Microsoft Excel

VBA macro tip contributed by Erlandsen Data Consulting offering Microsoft Excel Application development, template customization, support and training solutions 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.
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 datwa must be separated with the list separator character that is used in the regional
settings in the Control Panel. I Norway this usually is semicolon (;), in other countries this can be a comma (,).
You'll find the procedure RS2WS by clicking on this link.

The macro example 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 Microsoft
ActiveX Data Objects x.x Object Library.