Reset index of File object for reading several time

Question

Now, I am writing a VBA program. In my program, firstly I need to count all line from a file. I need line count because of creating array for line in file. So, I used this code. It is OK.

'Open file
Set file = fsObject.OpenTextFile(filePath, ForReading)

'Read all line
file.ReadAll

'Get line count
lineCount = file.line

'Close file
file.Close

After getting line count, I want to subtract 2 from it for header and footer(the blank line). I don't know which word will be header. I only know row that they are first row and last row(the blank row).

'Remove header and blank line from line count
lineCount = lineCount - 2

And then, I wanna read that file line by line which are only useful for me and store all line in array. The problem is at that, when reading line by line, It is need to re-open file. Only after re-open, I can read line by line.

Because, "ReadAll" method is readed all line and the index of file object is shown "AtEndOfFile". So, I must re-open it. Please check my code.

'If line count is greater than 0, read again file to get data
If lineCount > 0 Then

    'Re-define array size
    ReDim lineList(lineCount) As String

    'Here I opend it, I don't wanna open. I just want to set index of file object.
    'Re-open file
    Set file = fsObject.OpenTextFile(filePath, ForReading)

    'Read file until end
    Do Until file.AtEndOfStream

        'If current line is not first line(header) or last line(blank line)
        If line <> 0 And line <= lineCount Then

            'Store line into array
            lineList(index) = file.ReadLine

            'Increase array index
            index = index + 1

        Else

            file.ReadLine

        End If

        'Increase line index
        line = line + 1

    Loop

End If

But, I want another way. I don't wanna re-open file. I want to reset the index to the first line of file object. So, I don't need to re-open it.

I already search about it in internet. But, I didn't found any suggestions for that. Please help me. Thanks.

Answer 1

My approach is slightly different than your current approach, I would use the Binary read to read the file and save it in a temporary string, then use Split function to put them in an Array.

This method has one drawback as in the if the length (number of characters) of the file is greater than the size of a String variable then we might have issues but other than that. This is quite different approach.

Public Sub ReadFileData(filePath As String, Optional separatorStr As String = ";@;")
'******************************************************************************
'   Opens a large TXT File, reads the data until EOF on the Source,
'       then stores them in an Array
'   Arguments:
'   ``````````
'       1. The Source File Path - "C:\Users\SO\FileName.Txt" (or) D:\Data.txt
'       2. (Optional) Separator - The separator, you wish to use. Defauls to ';@;'
'*******************************************************************************
    Dim strIn As String, tmpStr As String, lineCtr As Long
    Dim tmpArr() As String

    Open filePath For Input As #1

    Do While Not EOF(1)
        'Read one line at a time.
        Line Input #1, strIn
        tmpStr = tmpStr & Trim(strIn) & separatorStr
        lineCtr = lineCtr + 1
    Loop
    Close #1

    tmpArr = Split(tmpStr, separatorStr)

    Debug.Print "Number of Elements in the Arrays is - " & UBound(tmpArr)
    Debug.Print "Number of Lines Read is - " & lineCtr
End Sub