Excel VBA - read text file problem

I've followed a tutorial here
'http://www.homeandlearn.org/open_a_text_file_in_vba.html

I want to open a text file 'config.csv' and read a small amount of data into a sheet. This code works. But there's a problem with the Do Until condition. It goes beyond the EOF and crashes when trying to read row_number 4 (my sample data has 3 rows. See attached).

I tried a Do and a If EOF(1) Then Exit Do but that didn't make any difference.

What am I missing please?

Sub OpenTextFile()

    Dim FilePath As String
    
    FilePath = ThisWorkbook.Path + "\Config.csv"
    
    Open FilePath For Input As #1
    
    row_number = 0
    
    Do Until EOF(1)
    
        Line Input #1, LineFromFile
        
        LineItems = Split(LineFromFile, ",")
        
        ActiveCell.Offset(row_number, 0).Value = LineItems(0)
        ActiveCell.Offset(row_number, 1).Value = LineItems(1)
        ActiveCell.Offset(row_number, 2).Value = LineItems(2)
        
        row_number = row_number + 1
    
    Loop

    Close #1

End Sub

Open in new window

Config.csv
hindersalivaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewCommented:
Try this:

Sub OpenTextFile()

    Dim FilePath As String
    Dim FileNum As Int
    
    FilePath = ThisWorkbook.Path + "\Config.csv"
    FileNum = FreeFile
    
    Open FilePath For Input As #FileNum
    
    row_number = 0
    
    Do While Not EOF(FileNum)
    
        Line Input #FileNum, LineFromFile
        
        LineItems = Split(LineFromFile, ",")
        
        ActiveCell.Offset(row_number, 0).Value = LineItems(0)
        ActiveCell.Offset(row_number, 1).Value = LineItems(1)
        ActiveCell.Offset(row_number, 2).Value = LineItems(2)
        
        row_number = row_number + 1
    
    Loop

    Close #FileNum

End Sub

Open in new window


»bp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hindersalivaAuthor Commented:
Bill, sorry no. I get the same error.
0
Bill PrewCommented:
Please attach the Config.csv file here so we can see it please.

Also what line of code is giving an error, and copy and paste the error box that is displayed please.


»bp
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Bill PrewCommented:
One typo in my prior post, since you ran it I assume you caught it, but should have been:

   Dim FileNum As Integer

I just ran a small test here with a test data file I made that looks like below, and it loaded fine into the active sheet, and then ended fine, no error.

A,B,C
AA,BB,CC
AAA,BBB,CCC\

Open in new window


»bp
0
hindersalivaAuthor Commented:
Bill, yes I caught the 'Integer' typo.

You have a '\' at the end of the last line. I tried that and no change in results, I'm afraid.

BUT!!! I got it. In my Text file I had a few more carriage returns below the last line. So the EOF(1) was more than the actual end of file should have been. Doh! Deleted those and it worked.

Thanks for your help, Bill.
0
hindersalivaAuthor Commented:
The error was caused by my Text file not ending where it should, but going beyond the 'last line.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.