Search code examples
vbaexcelutf-8ansi

Convert UTF-8 to ANSI using VBA


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.


Solution

  • 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