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.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
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?