Input end of excel VBA file

Question

I am trying to read a text file (these files are created using an external program that cannot 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, in the second line of this file, I have the following line:

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

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

How can I fix this problem?

Answer 1

May I interest you in a better way to read text files in VBA?

This will read the entire text file in ONE GO in the array, and then close the file. Thus, you do not need to constantly open the file.

 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