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.
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