ADO adStateOpen = 0

[Excel Monkey]

I came across this ADO code from Erlandsen Consulting. When I run this, the
sub is exited on the line:

If cn.State <> adStateOpen Then Exit Sub

Does this imply that my connection has failed?

I have referenced Microsoft ActiveX Data Objects 2.8 library. What am I
doing wrong?


'*******************************************
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

 

[Harald Staff]

Yes. Something is wrong in the previous line of code, anything from
misspelling to unavailable data source to whatever. StrFolder sounds like a
folder path, should,'t there be a full file path in that position?
Replace "On error resume next" with "on error goto 0" (zero) for a test, to
remove the error acceptance, and see if a helpful error message appear.

HTH. Best wishes Harald

 

[Excel Monkey]

Apologies - my fault. The error I am getting is:

"Runtime error -2147467259 (80004005)
[Microsoft][OBDC Test Driver]'(unknown)' is not a valid path. Make sure
that the path name is spelled correctly and that you are connected to the
server on which the file resides"

This was occuring as I was including the file name in the directory path.

Thanks for the feedback.

EM