VBScript Error 9 in AtEndOfStream Loop

Question

I am using a script like this to loop through some data in a text file:

Do Until objFile.AtEndOfStream
    'Check if it is not a blank line; otherwise skip it
    If Len(Trim(objFile.ReadLine)) > 0 Then
        'Some code
    end if
Loop

I print out the values every time through and it gets all the way to end of the file (I can see that it prints every value) but when it reaches the end it always errors out. I used on error resume next and printed out Err.Number and I get error number 9 which is Subscript out of range.

The issue with the file I'm looping through is that the last line is blank. I know this because I've opened the file, removed the final blank line and then run the code and it works fine. This is an automated process though (the file is automatically sent to the FTP and the process runs by an automated task every day) so I can't go in and manually be getting rid of the last line every day. I included that If Len(Trim(objFile.ReadLine)) > 0 Then to take this blank line into account and it works fine on the lines with data but it still throws an error at the end of file.

I could have the loop end when it reaches a blank line but I would like to keep a condition in there in the case that a blank line shows up in the middle of the code for some reason down the line. I figure it'll be cleaner to figure out if it truly is the last line of the file and then end the loop.

Any ideas? Thanks for your help!

Please show the rest of your code as well as the complete, unaltered error message. It's not possible that the code you posted would raise a subscript out of range error. — Ansgar Wiechers

paxdiablo paxdiablo · Accepted Answer · 2015-01-22T06:06:46

First of all, I'd like to ask you how you're actually processing that line you read in since you don't store it anywhere. You read it and pass it to Trim() for checking, but there's no way to get at that line afterwards.

If you have yet another ReadLine within the loop (thinking that's going to be the same line), that's going to be an issue since you might be reading the "blank" line within the if statement. It'll also mean you're throwing away every second line.

It seems to me you should be using something like:

Do Until objFile.AtEndOfStream
    thisLine = objFile.ReadLine
    If Len(Trim(thisLine)) > 0 Then
        ' Some code using thisLine, NOT another ReadLine.
    End If
Loop

Another thing (once that issue is clarified), are you sure that the final line contains only spaces? I think that's all trim() gets rid of. It may be that there's other white space in there causing you problems, such as TAB, for example.

One way to find out would be to take a copy of an offending file and remove all but the last couple of lines. Then you can insert debug statements into your code to see what's happening:

Do Until objFile.AtEndOfStream
    thisLine = objFile.ReadLine

    trimLine = Trim(thisLine)
    MsgBox ("[" & trimLine & "] " & CStr(Len(trimLine))

    If Len(Trim(thisLine)) > 0 Then
        MsgBox ("Processing it...")

        ' Some code using thisLine, NOT another ReadLine.
    End If
Loop

VBScript Error 9 in AtEndOfStream Loop

1 Answers