Import data from a text file (ADO)
2002-04-02 ADO 2 312
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 SubThe 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 SubReplace 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.