Do Until EOF does not loop properly

[mtenorio]

Hi guys!

I am working on an application where a text file is opened then some lines are deleted based on a criteria.

I am using the following code to read the text per line:

If FileName(root_loc, period, wsc_code) = "" Then
    MsgBox "There is no e-file found for the period M" & period & "."
    
Else
    'Opens the e-file
    Open FileName(root_loc, period, wsc_code) For Input As #1
    row_number = 0
    Do Until EOF(1)
    'reads the text line per line until the end of file
        Line Input #1, LineFromFile
        
        row_number = row_number + 1
    Loop
    Close #1
    
    Debug.Print row_number
    
 
End If

Supposedly I would get 58 for the Debug.Print row_number since 58 is the number of lines on the text file, but instead I get 1.


But when I try Debug.Print LineFromFile, the whole file gets printed..

Do you guys have an idea why I am getting 1 only for the number of lines?

Thanks a lot!

 

[Domenic]

That's probably because a line feed character (vbLf) is being used as an end of line marker, instead of a carriage return (vbCr) or carriage return and line feed (vbCrLf). One alternative would be to use the FileSystemObject. Otherwise, the code could be amended as follows...

    [COLOR=darkblue]Dim[/COLOR] my_string [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] my_lines [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] LineFromFile [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] row_number [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=green]'[/COLOR]
    '
    '
    [COLOR=green]'[/COLOR]
    
        [COLOR=darkblue]Open[/COLOR] Filename(root_loc, Period, wsc_code) [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Input[/COLOR] [COLOR=darkblue]As[/COLOR] #1
            my_string = [COLOR=darkblue]Input[/COLOR](LOF(1), #1)
        [COLOR=darkblue]Close[/COLOR] #1
        
        my_lines = Split(my_string, vbLf)
        
        row_number = 0
        [COLOR=darkblue]For[/COLOR] i = [COLOR=darkblue]LBound[/COLOR](my_lines) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](my_lines)
            LineFromFile = my_lines(i)
            row_number = row_number + 1
        [COLOR=darkblue]Next[/COLOR] i
        
        Debug.Print row_number
    
    [COLOR=green]'[/COLOR]
    '
    '
    [COLOR=green]'[/COLOR]

Hope this helps!

 

[mtenorio]

Hello Domenic!

I've tried this and it worked perfectly fine! thanks a lot!!!

 

[Domenic]

You're very welcome!

 

[Ileska]

That was super helpful, thanks a lot Domenic!!!