Calculate the row number associated to a byte position inside text file vba-CodePudding

I have a text file that I open to search for a value, using the instr() to get the matched position. From the code below I get the stats below:

InStrPos : 7.775 (the InStr() position)
FileSize : 494.736 (FileSize = FileLen(strFullPath)) --> where Size on disk = 495.616 bytes
RowsInFile : 1.762 (Number of rows inside the file, where this line only has the eof.)
The number of Columns : (494.736 / 1.762) = 280,7809 -> 280 columns with data

I nead to calculate the row number where the byte 7.775, where the mach ocurrence happened. (row 29 in file)

I'm using the code below:

Private Sub ProcessFile(strFullPath As String, ByRef strIndent As String, ByRef varStrings As Variant, ByRef varMatchesFound As Variant, ByRef varFileNames As Variant, ByRef lngFileCount As Long)
On Error Resume Next
Dim objFileSystemObject As Object
Dim strFileContent As String
Dim lngIndex As Long
Dim RowCount As Long
'vars de calculo da linha do Match
Dim RowsInFile  As Long
Dim ColsInFile  As Long
Dim FileSize    As Long 'in bytes
Dim InStrPos    As Long


    lngFileCount = lngFileCount   1
    Debug.Print strIndent & "File: " & Format(lngFileCount, "###,##0 ") & strFullPath

    ' Use late binding throughout this method to avoid having to set any references.
    Set objFileSystemObject = CreateObject("Scripting.FileSystemObject")
    strFileContent = objFileSystemObject.OpenTextFile(strFullPath).Readall()
    If Err.Number = 0 Then
        
        'O processo vai procurar os valores todos em cada ficheiro, e não cada valor em todos os ficheiros.
        ' Check for matched strings by iterating over the strings array.
        For lngIndex = LBound(varStrings) To UBound(varStrings)
            ' Skip zero length strings. --> Redundante, já que a função [RemoveDup_FromArray(varStrings)] limpou todas
            If Len(Trim$(varStrings(lngIndex))) > 0 Then
                ' We have a matched string.
                InStrPos = InStr(1, strFileContent, varStrings(lngIndex), vbTextCompare)
                If InStrPos > 0 Then
                    
                    'get FileLen
                    FileSize = FileLen(strFullPath)
                    
                    'Get the number of lines inside the file
                    With CreateObject("vbscript.regexp")
                        .Global = True
                        '.Pattern = "\b" & varStrings(lngIndex) & "\b"     'By using word boundary (\b), you can specify in the regex pattern that you are searching for complete word(s).        '"\r\n"       '  or .Pattern = "\n"
                        .Pattern = "\r\n" 'vbCrLf               '.Pattern = "\n" ' vbLf, Unix style line-endings
                        RowsInFile = .Execute(strFileContent).Count   1
                    End With
                    
                    Debug.Print InStrPos & vbTab & FileSize & vbTab & RowsInFile
                    
                    
                End If
            End If
        Next
    Else
        Err.Clear
    End If
    Set objFileSystemObject = Nothing
On Error GoTo 0
End Sub

Thank you in advance.

CodePudding user response：

If you know the position in the file content, you could use:

Debug.Print "Line#: " & UBound(Split(Left(strFileContent, InStrPos ), vbCrLf))   1

Or split the file on vbCrLf and loop over the resulting array.