Convert UTF-8 to ANSI using VBA

Question

I have a VBA Excel code which takes Japanese data from excel sheet compares it with Japanese data in text file and replaces Japanese words with English words. But I am supposed to be able to do this on UTF-8 text file. This code replaces all the Japanese words with weird characters. How do I save without any issue ?

Open sFileName For Input As iFileNum

For n = 1 To lngLastCell
Label5.Caption = n & "/" & lngLastCell
searchtext = MySearch(n)
valuetext = MyText(n)

eplcCount = 0
spltCount = 0

searchpart = Array(searchtext)
valuepart = Array(valuetext)

Do Until EOF(iFileNum)
Line Input #iFileNum, sBuf
sTemp = sTemp & sBuf & vbCrLf

Loop


Close iFileNum

sTemp = Replace(sTemp, searchtext, valuetext)

'iFileNum = FreeFile
Open sFileName For Output As iFileNum
Print #iFileNum, sTemp

Next n 

Code works well with ANSI characters.

Answer 1

The Open function from VBA works on ANSI encoded files only and binary. If you wish to read/write an utf-8 file, you'll have to find another way.

The utf-8 encoding has a larger set of characters than ANSI, thus it's not always possible to convert from utf-8 to ANSI without loss. That said, a String in Excel and VBA is stored as utf-16 (VBA editor still use ANSI), so you only need to convert from utf-8 to utf-16.

With ADODB.Stream :

Public Function ReadFile(path As String, Optional CharSet As String = "utf-8")
  Static obj As Object
  If obj Is Nothing Then Set obj = VBA.CreateObject("ADODB.Stream")
  obj.CharSet = CharSet
  obj.Open
  obj.LoadFromFile path
  ReadFile = obj.ReadText()
  obj.Close
End Function

Public Sub WriteFile(path As String, text As String, Optional CharSet As String = "utf-8")
  Static obj As Object
  If obj Is Nothing Then Set obj = VBA.CreateObject("ADODB.Stream")
  obj.CharSet = CharSet
  obj.Open
  obj.WriteText text
  obj.SaveToFile path
  obj.Close
End Sub