Convert UTF-8 to ANSI using VBA
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 possible to convert from ANSI
to utf-8
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
PHP Enthu
Updated on June 29, 2022Comments
-
PHP Enthu almost 2 years
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.