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 PrewIT / Software Engineering ConsultantCommented:
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

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.
Bill PrewIT / Software Engineering ConsultantCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Bill PrewIT / Software Engineering ConsultantCommented:
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
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.
hindersalivaAuthor Commented:
The error was caused by my Text file not ending where it should, but going beyond the 'last line.
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.