How to return Line number and its position number and length of string searched From Text File

[SamDsouza]

Hello

Actually i would like to return Line number and the position number of cursor with length of string searched From Text File

I tried the following code from
StackOverflow thread 23369908

Basically the above thread showed the position of string searched for and as on one line only

Where as i would like msgbox to show "String Searched" Amt
in line 3 Position 1 Lenght 3
in line 7 Position 1 Lenght 3
in line 11 Position 1 Lenght 3

Example "String Searched" 8600
in line 7 Position 5 Lenght 4

Example "String Searched" 0X
in line 6 Position 21 Lenght 2
in line 10 Position 24 Lenght 2

Example "String Searched" 09
in line 1 Position 9 Lenght 2
in line 4 Position 9 Lenght 2
in line 10 Position 33 Lenght 2

Structure of "C:\ABC\TextFile.txt"

Date 19-09-2020
Reference Number NZ26X201249Y75140
Amt 2000.00

Date 28-09-2020
Reference Number MD00X1485Y47366
Amt 8600.00

Date 10-10-2020
Reference Number YN27220X1562M9109
Amt 7650.00

Option Explicit
Public myFile As String
Public textLine As String

Private Sub UserForm_Initialize()
myFile = "C:\ABC\TextFile.txt"

Open myFile For Input As #1
Do Until EOF(1)
Line Input #1, textLine
TextBox2.text = TextBox2.text & textLine & vbCrLf
Loop
Close #1
End Sub

Private Sub CommandButton1_Click()
Call REVSrchStr_Load
End Sub


Private Sub RevSrchStr_Load()

   Dim myFile As String
   Dim text As String
   Dim textLine As String
   Dim posIAV As Integer
   Dim Ctr As Integer
   Dim Ctr2 As Integer
   Dim data() As String

   myFile = "C:\ABC\TextFile.txt"
   Ctr = 0
   Ctr2 = 0

   Open myFile For Input As #1

   Do While Not EOF(1)
   Line Input #1, textLine
   text = text & textLine & vbCrLf

   ' Increment Ctr since you're on the line now
   Ctr = Ctr + 1

   ' Check the current line to see if it contains the string we're looking for
   ' If it does, set Ctr2 to be the current line number
   If textLine Like TextBox1.text Then
      Ctr2 = Ctr
   End If

   Loop

   Close #1

   posIAV = InStr(text, TextBox1.text)
   MsgBox (posIAV) & " in number Line : " & Ctr
End Sub

Your help will be appreicated
SamD
162

 

[Legacy 456155]

You will need a reference to Microsoft Scripting Runtime. Returns multiple occurrences in the same line as well. The example uses the default compare method which is case sensitive.
Example file Search Text File.xlsm in this folder.

This example would perform a case insensitive search.
MySearch = GetSearchResults("C:\ABC\TextFile.txt", "m", vbTextCompare)

In Static Module:

Public Type SearchResults
    LineNumber As Long
    CharPosition As Long
    StrLen As Long
End Type

Function GetSearchResults( _
  FileFullName As String, _
  FindThis As String, _
  Optional CompareMethod As VbCompareMethod = vbBinaryCompare) _
  As SearchResults()
  
    Dim fso As New FileSystemObject, s As String, pos As Long, l As Long, sr As SearchResults, ret() As SearchResults, i As Long
    
    With fso.OpenTextFile(FileFullName)
        Do Until .AtEndOfStream
            l = l + 1
            s = .ReadLine
            pos = 1
            Do
                pos = InStr(pos, s, FindThis, CompareMethod)
                If pos > 0 Then
                    sr.CharPosition = pos
                    sr.LineNumber = l
                    sr.StrLen = Len(FindThis)
                    ReDim Preserve ret(i)
                    ret(i) = sr
                    i = i + 1
                    pos = pos + 1
                End If
            Loop Until pos = 0
        Loop
    End With
    GetSearchResults = ret
End Function


Sub Example()
    Dim MySearch() As SearchResults, i As Long
    
    MySearch = GetSearchResults("C:\ABC\TextFile.txt", "0X")
    For i = 0 To UBound(MySearch)
        With MySearch(i)
            Debug.Print "in line " & .LineNumber, "Position " & .CharPosition, "Length " & .StrLen
        End With
    Next
End Sub

 

[SamDsouza]

Fantastic Dataluver just perfect ?

Thanks for the Folder Link

Last but not the least Will it be possible to get the Main String displayed
for eg i searched "AT"
Main string to be displayed as DATE
so
in Line 1, Position 1, Length 4, in Main String DATE
in Line 5, Position 1, Length 4, in Main String DATE
in Line 5, Position 1, Length 4, in Main String DATE

I searched for "6"
in Line 2, Position 21, Length 1, in Main String NZ26X201249Y75140
in Line 6, Position 31, Length 1, in Main String MD00X1485Y47366
in Line 6, Position 32, Length 1, in Main String MD00X1485Y47366
in Line 7, Position 6, Length 1, in Main String 8600.00
in Line 10, Position 28, Length 1, in Main String YN27220X1562M9109
in Line 11, Position 6, Length 1, in Main String 7650.00

SamD
163

 

[SamDsouza]

I tried the following

Option Explicit

Public Type SearchResults
    LineNumber As Long
    CharPosition As Long
    StrLen As Long
    srchdStr As String
End Type

Function GetSearchResults( _
  FileFullName As String, _
  FindThis As String, _
  Optional CompareMethod As VbCompareMethod = vbBinaryCompare) _
  As SearchResults()

    Dim fso As New FileSystemObject, s As String, pos As Long, l As Long, sr As SearchResults, ret() As SearchResults, i As Long
  
    With fso.OpenTextFile(FileFullName)
        Do Until .AtEndOfStream
            l = l + 1
            s = .ReadLine
            pos = 1
            Do
                pos = InStr(pos, s, FindThis, CompareMethod)
                If pos > 0 Then
                    sr.CharPosition = pos
                    sr.LineNumber = l
                    sr.StrLen = Len(FindThis)
                    sr.srchDstr = Split(s, " ")(1)
                    ReDim Preserve ret(i)
                    ret(i) = sr
                    i = i + 1
                    pos = pos + 1
                End If
            Loop Until pos = 0
        Loop
    End With
    GetSearchResults = ret
End Function


Sub Example()
    Dim MySearch() As SearchResults, i As Long
  
    MySearch = GetSearchResults("C:\ABC\TextFile.txt", "6", vbTextCompare)
  
    For i = 0 To UBound(MySearch)
        With MySearch(i)
            Debug.Print "in line " & .LineNumber, "Position " & .CharPosition, "Length " & .StrLen & " In Main String " & .srchdStr
        End With
    Next
End Sub

unfortunately The following sr.srchDstr = Split(s, " ")(1) gave the following results
in Line 2, Position 21, Length 1, in Main String Number
in Line 6, Position 31, Length 1, in Main String Number
in Line 6, Position 32, Length 1, in Main String Number
in Line 7, Position 6, Length 1, in Main String 8600.00
in Line 10, Position 28, Length 1, in Main String Number
in Line 11, Position 6, Length 1, in Main String 7650.00

Wish the Split function could execute in the following manner Split(s, " ")(2) but Not possible only
SamD
164

 

[SamDsouza]

Hello

I've worked it out for what i desired as per #4 of this thread

Thanks
SamD
165