计算文本文件vba中与字节位置相关联的行号

Question

我有一个文本文件，我打开它来搜索一个值，使用instr()来获得匹配的位置。从下面的代码中，我得到了下面的统计数据:

• instpos: 7.775 (InStr() position)
• FileSize: 494.736 (FileSize = FileLen(strFullPath))——>
• RowsInFile: 1.762(文件内的行数，其中这一行只有eof)

• 列数:(494.736/1.762)= 280,7809 ->/ul>我需要计算字节7.775所在的行号，即mach发生的地方。(行29日在文件)

  我使用下面的代码:

  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).        '"rn"       '  or .Pattern = "n"
  .Pattern = "rn" '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
  

  提前谢谢你。

Answer 1

如果你知道文件内容的位置,您可以使用:

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

或者在vbCrLf上分割文件并循环生成的数组。