How can I detect the end of a text file (EOF) in vbscript?
<p>I'm using VBscript to sort the information in a text file to an excel sheet. I am able to do so, but only one line of the text is being read, and the loop control doesn't seem to go to the next line of the text file. I am using <strong>AtEndOfStream</strong> for this, but as I said, I am getting only one line of output. Can someone help me figure out how to execute the program till the end of the file?</p>

<p>Here is the code:</p>

<pre><code>Set objExcel = CreateObject("Excel.Application")            'Bind to the Excel object
objExcel.Workbooks.Add                                      'Create a new workbook.
Sheet = 1                                                   'Select the first sheet
Set objSheet = objExcel.ActiveWorkbook.Worksheets(Sheet)    'Bind to worksheet. 
objSheet.Name = "ErrorSpreadsheet"                          'Name the worksheet
strExcelPath = "c:\scripts\ErrorSpreadsheet.xlsx"           'Set the save location

objSheet.Range("A1:E1").Font.Bold = True
objExcel.Columns(5).AutoFit()
objSheet.Cells(1, 1).Value = "Date"                         'Row 1 Column 1 (A)
objSheet.Cells(1, 2).Value = "Time"                         'Row 1 Column 2 (B)
objSheet.Cells(1, 3).Value = "Category"                     'Row 1 Column 3 (C)
objSheet.Cells(1, 4).Value = "Error"                        'Row 1 Column 4 (D)
objSheet.Cells(1, 5).Value = "Index"                        'Row 1 Column 5 (E)

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("c:\scripts\ErrorMsg.txt",1)

i = 0
r = 2
c = 1
j = 0
Do While NOT objFile.AtEndOfStream 
    Redim Preserve arrFileLines(10)
    arrFileLines(i) = objFile.ReadLine
    text = arrFileLines(j)
    'Dim a() 
    a = Split(text,";")
    For Each line In a
        objSheet.Cells(r,c).Value = a(i)
        i = i + 1
        c = c + 1
    Next
    j = j + 1
    r = r + 1
Loop


objFile.Close
objExcel.ActiveWorkbook.SaveAs strExcelPath
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
</code></pre>

<p>I've written the code now in such a way that I do not get any errors. In my excel file, I have both the heading line as well as one line of the split text file, but not any of the following lines. The text file is written like this:</p>

<pre><code>       9/23;12:00;cat;error;236
       9/24;12:30;cat2;error;897
       9/24;4:06;cat5;error;23
       9/25;3:45;cat1;error;54
       9/26;1:09;cat6;error;18
</code></pre>

<p>So the output I'm getting in excel is <a href="http://i.stack.imgur.com/xZRQs.jpg" rel="nofollow">Excel Output</a></p>

<p>Can someone help me figure out how to get to the end of the text file?</p>