VBA reading in text file and parsing by line breaks (carriage returns)

[kkmoslephour]

Hi guys, I am a little stuck here. I am trying to read in a text file and parse it by line breaks however I cannot read the content of the parsed line breaks, it just shows up as 'Empty' when I watch them.

Option Explicit

Sub ParseText()

Dim myFile As String, text As String, textline As String, Lastrow As Integer, i As Integer, Dim data() As String

myFile = Application.GetOpenFilename("Text Files (*.txt), *.txt")


If myFile <> "False" Then
    MsgBox "Opening " & myFile
Else
    MsgBox "Invalid File Path!", vbCritical, vbNullString
    Exit Sub
End If

Open myFile For Input As #1

Do While Not EOF(1)
    Line Input #1, textline
    data = Split(textline, vbCrLf)
    text = text & textline
    MsgBox textline
Loop

However when I parse by space " " using

[COLOR=#000000][FONT=Arial][B]data = Split(textline, " ")[/B][/FONT][/COLOR]

I can sucessfully see the contents of each array in my console window. So i am wondering why I cannot parse by line breaks.

 

[marcnz]

Thanks John_W! I tried everything: vbNewLine, vbCrLf, vbCr...
I did not try vbLf!
That was the one

 

[SamDsouza]

Hello I get subscript out of Range Error using the above code what i've changed here
myFile = "C\ABC\Text.txt"
and i get error at MsgBox data(0) ie after reading Each line of text file. but in End the Line is Blank and after the blank line there is vbcrlf
Any Guidance from anyone shall appreciate

Option Explicit

Sub ParseText()

Dim myFile As String, text As String, textline As String, Lastrow As Integer, i As Integer, Dim data() As String

''''''''''''myFile = Application.GetOpenFilename("Text Files (*.txt), *.txt")
myFile = "C\ABC\Text.txt"

If myFile <> "False" Then
    MsgBox "Opening " & myFile
Else
    MsgBox "Invalid File Path!", vbCritical, vbNullString
    Exit Sub
End If

Open myFile For Input As #1

Do While Not EOF(1)
    Line Input #1, textline
    data = Split(textline, vbLf)
    '''''''''text = text & textline
    '''''''''MsgBox textline
MsgBox data(0)
Loop
Close #1
End Sub

SamD
160

 

[Rick Rothstein]

In this line of code...

myFile = "C\ABC\Text.txt"

Your drive letter (the first "C") needs a colon after it....

myFile = "C:\ABC\Text.txt"

Also, since you are specifying your file/path name directly instead of picking it from the "Open File" dialog box, you can remove this part of the code which is there just to show you the file you picked from the dialog box or exit the program if you cancelled out of the dialog box without selecting any file...

If myFile <> "False" Then
    MsgBox "Opening " & myFile
Else
    MsgBox "Invalid File Path!", vbCritical, vbNullString
    Exit Sub
End If

 

[SamDsouza]

Rick
Path without colon would give another error. Its Typo Error when posting #12. Sorry. Anyways thanks for the correction
As per your suggestion removed the part of the code

If myFile <> "False" Then
    MsgBox "Opening " & myFile
Else
    MsgBox "Invalid File Path!", vbCritical, vbNullString
    Exit Sub
End If

After removing the above part of the code Still getting Subscript Out of Range Error at MsgBox data(0) ie after Each line of text file. but in End the Line is Blank and after the blank line there is vbcrlf. What i observed here is that after reading each line of text file and at the end of line there seems to be empty line and after empty line there is vbcrlf. Below structure of myFile = "C:\ABC\Text.txt"

19-09-2020
Reference Number NZ26X201249Y75140
Amt 2000.00

28-09-2020
Reference Number MD00X1485Y47366
Amt 8600.00

10-10-2020
Reference Number YN27220X1562M9109
Amt 7650.00

SamD
161