Much ADO about text files

[beedistinct]

Am at my wits' end. what is wrong with the code below? It is giving me an error
"ADODB Connection --> Operation is not allowed when the object is closed."

Public Sub GetTextFileData()
 Dim cn As ADODB.Connection, rs As ADODB.Recordset, f As Integer
 Dim strSQL As String, StrFolder As String
 Dim rngTargetcell As Range
 
 On Error GoTo ErrorHandler
  
  strSQL = "SELECT * FROM TestADO.txt"
  StrFolder = ActiveWorkbook.Path
  Set rngTargetcell = Range("A3")
  
    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;FMT=Delimited(|)"
    
   'If cn.State <> adStateOpen Then Exit Sub
    
    Set rs = New ADODB.Recordset
    On Error Resume Next
    rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
    
    ' 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
    
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    
ErrorHandler:
    ' clean up
    If Not rs Is Nothing Then
        If rs.State = adStateOpen Then rs.Close
    End If
    Set rs = Nothing
    
    If Not cn Is Nothing Then
        If cn.State = adStateOpen Then cn.Close
    End If
    Set cn = Nothing
    
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub

 

[sky1in5]

is your text file close?

 

[beedistinct]

is your text file close?

yes. The text file is closed.

I want to retrieve data from it while it is closed.