VBA script always returns Input past end of file error

Question

I have a csv file as such:

hello,world,this
is,an,example,
of,the,csv,file

The first column will always be unique.

I am getting the user to enter a string which matches an item in the first column, and the script returns the whole line:

Open "C:\file1.csv" For Input As #file_number
Do While (EOF(1) Or found = False)
    Line Input #file_number, raw_line
    pos = InStr(raw_line, fireName)
    If pos = Not 0 Then
        strData() = Split(raw_line, ",")
        found = True
    End If
Loop
Close #file_number
If found = False Then
    MsgBox "Could not find it"
    Exit Sub
End If
'REST OF THE CODE

But it always sends the message "could not find it" and exits. By default, found is a boolean value and is false.

I know it can detect the file as when I changed the read file name, it created a new one (as it does not exist).

EDIT: I changed the And to an Or and now I get the error: Run-time error 62: input past end of file

Probably not creating any issues (since you presumably only have one file open), but EOF(1) checks a hard-coded file number and you open it as #file_number. It should be EOF(file_number). — Comintern
– Comintern, Commented Apr 16, 2015 at 0:55

TPS · Accepted Answer · 2015-04-16 15:56:13Z

2

It looks like a simple error in your Do While loop. You Check for the EOF instead of not the EOF, as such the loop will never execute since it starts at the BOF.

Something like this should work (my syntax may be slightly off as I haven't used VBA in a while)

Open "C:\file1.csv" For Input As #file_number
While Not EOF(file_number) And found <> False
    Line Input #file_number, raw_line
    pos = InStr(raw_line, fireName)
    If pos <> 0 Then
        strData() = Split(raw_line, ",")
        found = True
    End If
Wend
Close #file_number
If found = False Then
    MsgBox "Could not find it"
    Exit Sub
End If

edited Apr 16, 2015 at 15:56

answered Apr 15, 2015 at 21:01

TPS

2,1473 gold badges24 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2883071 Over a year ago

When I try it this way, even if the word is not found, it does not send the msgbox

user2883071 Over a year ago

I added a Not to the If pos = statement, it makes more sense now, but now it always returns a could not find it message.

user2883071 · Accepted Answer · 2015-04-16 17:07:22Z

0

The correct way of typing If pos != 0 in VBA is

If pos <> 0

Now it works

edited Apr 16, 2015 at 17:07

answered Apr 16, 2015 at 15:53

user2883071

1,0001 gold badge25 silver badges53 bronze badges

Collectives™ on Stack Overflow

VBA script always returns Input past end of file error

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related