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'", _
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 & ";" & _
    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
        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
    Set rs = Nothing
    Set cn = Nothing
End Sub

The procedure can be used like this:

Sub TestGetTextFileData()
    Application.ScreenUpdating = False
    GetTextFileData "SELECT * FROM filename.txt", "C:\FolderName", Range("A3")
'    GetTextFileData "SELECT * FROM filename.txt WHERE fieldname = 'criteria'", _
    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.



    This is excellent. It means using ADO method, we can communicate with Text Files or can use it as a Database for Excel VBA Applications?

Leave a Reply to KAMAL C. BHARAKHDA Cancel reply


Terms and Conditions of use
