0

I have Excel VBA code which search a string (English text) in text file (.txt/notepad) and then gives me the line number and complete value of line of matched result. The code works perfectly fine for all the files which are in English. but I want it to work for Japanese characters also because sometime i have to search text in Japanese files (text file containing data in Japanese language)

As per my understanding the issue is with line s.readline where vba is unable to read Japanese text as it is hence can't compare in next step to find match, I know we have to use UTF-8 and encoding or decoding must be done for Japanese character but not sure how to do that. Please help on this issue

Global searchallfiles As Integer

Public Type SearchResults
    LineNumber As Long
    CharPosition As Long
    StrLen As Long
    s 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, vbTextCompare)
                If pos > 0 Then
                    sr.CharPosition = pos
                    sr.LineNumber = l
                    LastColumnofsheet4Real = Sheet4.Cells(searchallfiles, Columns.Count).End(xlToLeft).Column
                    Sheet4.Cells(searchallfiles, LastColumnofsheet4Real + 1).Value = l
                    Sheet4.Cells(searchallfiles, LastColumnofsheet4Real + 2).Value = s
                    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 MyTextStringSearch()
    
  Application.DisplayAlerts = False
  Application.ScreenUpdating = False
  
    Dim MySearch() As SearchResults, i As Long
    
    Dim searchstringtxt As String
        
    searchstringtxt = Sheet1.Cells(6, 4).Value
   
    LastofTextFilelist = Sheet4.Range("B" & Rows.Count).End(xlUp).Row
   
   For searchallfiles = 2 To LastofTextFilelist
        
    MySearch = GetSearchResults(Sheet4.Cells(searchallfiles, 2).Value, searchstringtxt)
          
   Next searchallfiles
      
End Sub
Gaurav
  • 1
  • 3
  • 1
    You can try do define the format on the _opentextfile_ method (see https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/opentextfile-method). I would try unicode at first. – Shrotter Jun 16 '23 at 07:16
  • @Shrotter tried to open file using With fso.OpenTextFile(FileFullName, ForReading, TristateFalse) not working – Gaurav Jun 16 '23 at 09:00
  • have you checked (e.g. using watch window) if these special character is read correct and the search fails? – Shrotter Jun 16 '23 at 09:24
  • while reading itself (when storing the .readline value in "s" it is showing kind of garbage value ) when I do for English text it stores correct value , shows garbage when Japanese value comes , looking for UTF-8 solution which will give me correct text. – Gaurav Jun 16 '23 at 09:38
  • `fso.OpenTextFile(FileFullName, ForReading, False, TristateTrue)` opens the file as Unicode. With `TristateFalse` opens the file as ASCII… [Solving the Unicode, UTF8, UTF16 and Text Files conundrum in VBA](https://francescofoti.com/2020/01/solving-the-unicode-utf8-utf16-and-text-files-conundrum-in-vba/) could help… – JosefZ Jun 16 '23 at 20:42
  • @JosefZ tried that , Japanese character still shows garbage , I did checked the link trying to integrate that in above code can't find the fit yet – Gaurav Jun 17 '23 at 07:15

0 Answers0