EXCEL-VBA, ADO not connecting to a TXT file

C

coco

Could you give me a good link or sample about how to implement a EXCEL-VBA
with ADO.
I mean, using EXCEL-VBA (forms, modules and classes) , to dynamically
compare values in Excel and a TXT file.
thanks

Coco
pd. I already tried and it did not work,
I thought that was about "Project References" but I think I did it correctly.
Now is showing VBA Project - References

"Microsoft ADO Ext. 2.8 for DDL and Security" (last priority)

and this is the copy of what I did:


Sub TestGetTextFileData()
GetTextFileData "SELECT * FROM mex1.txt", "c:\temp", Range("A3")
End Sub


Sub GetTextFileData(strSQL As String, strFolder As String, rngTargetCell As
Range)
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

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
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
 
M

michelxld

Hello

you have to activate the "Microsoft ActiveX Data Object x.x Library
and not the "Microsoft ADO Ext. 2.8 for DDL and Security"



Regards
miche
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top