使用 VBA 将 UTF-8 转换为 ANSI
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45301892/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert UTF-8 to ANSI using VBA
提问by PHP Enthu
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 ?
我有一个 VBA Excel 代码，它从 excel 表中获取日语数据，将其与文本文件中的日语数据进行比较，并将日语单词替换为英语单词。但我应该能够在 UTF-8 文本文件上执行此操作。这段代码用奇怪的字符替换了所有的日语单词。如何保存没有任何问题？
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.
代码适用于 ANSI 字符。
采纳答案by Florent B.
The Openfunction from VBA works on ANSIencoded files only and binary. If you wish to read/write an utf-8file, you'll have to find another way.
OpenVBA 中的函数ANSI仅适用于编码文件和二进制文件。如果你想读/写一个utf-8文件，你必须找到另一种方式。
The utf-8encoding has a larger set of characters than ANSI, thus it's not possible to convert from ANSIto utf-8without loss. That said, a Stringin Excel and VBA is stored as utf-16(VBA editor still use ANSI), so you only need to convert from utf-8to utf-16.
该utf-8编码具有较大的一组比的字符ANSI，因此它不可能从转换ANSI到utf-8无损失。这就是说，一个String在Excel和VBA存储为utf-16（VBA编辑器仍然使用ANSI），所以你只从需要转换utf-8到utf-16。
With ADODB.Stream:
与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
