Input past end of file VBA excel

Question

I am attempting to read text file (these files are produced by external program, that could not be tweeked) using the following macro.

    While Not EOF(int_current_file)
    Line Input #int_current_file, buf
    If Left(buf, 1) <> "#" Then
        buf1 = Split(buf, "=")
        Print #int_master_file, CInt(Left(buf, 2)) & ";" & CInt(Mid(buf, 3, 4)) & ";" & CInt(Mid(buf, 7, 3)) & ";" & CInt(Mid(buf, 10, 1)) & ";" & CDbl(buf1(1) / 100000000) & ";" & CDate(file_date) & ";" & Mid(file_name, 4, 3)
    End If
    'Line Input #int_current_file, buf
    'Debug.Print Data
Wend

However, at the second line of this file I have the following string:

=01082013=01072013=31072013=06082013=1640=380441=21=000001249=#02IFS86.G84=IFSSS5=7ҐK!Ђi—Љ42ЃЁ4№{¤Хo$]ґ•Хp Ё1‹;±~†ЁRLЌг‰®ґн нќРР^±>_‰

When macro tries to read this line the error 62 occurs Input past end of file.

How can I fix this problem?

Solution

May I interest you in a better way of reading text files in VBA?

This will read the entire text file in ONE GO in an array and then close the file. This way you don't need to keep the file open at all times.

Option Explicit

Sub Sample()
    Dim MyData As String, strData() As String
    Dim i As Long

    '~~> Replace your file here
    Open "C:\MyFile.Txt" For Binary As #1
    MyData = Space$(LOF(1))
    Get #1, , MyData
    Close #1
    strData() = Split(MyData, vbCrLf)

    '
    '~~> Now strData has all the data from the text file
    '

    For i = LBound(strData) To UBound(strData)
        Debug.Print strData(i)
        '
        '~~> What ever you want here
        '
    Next i
End Sub

Comments

[mr.M]

Could you please post comments on this peace of code: MyData = Space$(LOF(1)) Get #1, , MyData?

[Siddharth Rout]

Space$(LOF(1)) creates a string the size of the file. LOF and Space$ is to initialize the string to a given length. By the way, did it work for you?

[SJDS]

Hi, I have been flabergasted by the difficulty of importing a simple text file in VBA!. It seems to be easier to extract data straight from a website than just importing them from a text file. Yours is the 20th suggestion I have tried and it does not work for me either. I'm just trying to important text files as a string so that I can use 'Mid' and 'InStr' functions on the resulting string. Any clarification would be more than welcome. If I try to put strData in a TextBox I get an error: "type mismatch" I'm guessing this is due to the array rather than string type? Any solutions?