Converting UTF-8 to ANSI using VBA
I have VBA Excel code that takes Japanese data from an excel sheet, compares it to Japanese data in a text file, and replaces Japanese words with English words. But I have to do it in a UTF-8 text file. This code replaces all Japanese words with strange characters. How to save without any problem?
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
The code works well with ANSI characters.
source to share
The function Open
from VBA only works with ANSI
encoded files and binaries. If you want to read / write a file utf-8
, you'll have to find another way.
The encoding utf-8
has a larger character set than ANSI
, therefore it is impossible to convert from ANSI
to without conversion utf-8
. However, it String
is stored as in Excel and VBA utf-16
(VBA editor still uses ANSI
), so you only need to convert from utf-8
to utf-16
.
From 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 stream = VBA.CreateObject("ADODB.Stream")
obj.CharSet = CharSet
obj.Open
obj.WriteText text
obj.SaveToFile path
obj.Close
End Sub
source to share