how to find end of line in VBA

[anandvarma]

i have " ^ " separated text file, i want to import this file to excel using the follow VBA code


Sub ImportData()
Dim counter As Long
Dim strMyLine As String
Dim myFile As Long
Dim strData() As String

myFile = FreeFile

Open "C:\Documents and Settings\anandavarma\Desktop\test.txt" For Input As #myFile
Do While Not EOF(myFile)
Line Input #myFile, strMyLine
strData = Split(strMyLine, "^")

For counter = LBound(strData) To UBound(strData)
ActiveCell.Offset(0, counter).Value = strData(counter)
Next
ActiveCell.Offset(1, 0).Select
Loop
Close #myFile
Columns.AutoFit
Range("A1").Select
End Sub

what is happening is all data is imported in first row only

please help me

 

[Cindy Ellis]

Could this be a simpler way of accomplishing the same goal? It uses Workbook open (even though it's a text file) and specifies the delimiter as a "^".

    Workbooks.OpenText Filename:= _
        "C:\Documents and Settings\anandavarma\Desktop\test.txt", Origin:=437, StartRow _
        :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=True, OtherChar:="^", TrailingMinusNumbers:=True

Hope that helps,
Cindy

 

[anandvarma]

yes it worked
great

thanks