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?

Siddharth Rout · Accepted Answer · 2013-11-21 18:05:23Z

15

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

answered Nov 21, 2013 at 18:05

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

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

3 Comments

mr.M Over a year ago

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

Siddharth Rout Over a year ago

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 Over a year ago

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?

Collectives™ on Stack Overflow

Input past end of file VBA excel

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related