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





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.



Leave a Reply

Your email address will not be published. Required fields are marked *


three + 9 =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>