保存用 VBA 编码的 UTF-8 文本文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2524703/
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
Save text file UTF-8 encoded with VBA
提问by Karsten W.
how can I write UTF-8 encoded strings to a textfile from vba, like
如何将 UTF-8 编码的字符串从 vba 写入文本文件，例如
Dim fnum As Integer
fnum = FreeFile
Open "myfile.txt" For Output As fnum
Print #fnum, "special characters: ??ü?" 'latin-1 or something by default
Close fnum
Is there some setting on Application level?
应用程序级别是否有一些设置？
回答by Karsten W.
I found the answer on the web:
我在网上找到了答案：
Dim fsT As Object
Set fsT = CreateObject("ADODB.Stream")
fsT.Type = 2 'Specify stream type - we want To save text/string data.
fsT.Charset = "utf-8" 'Specify charset For the source text data.
fsT.Open 'Open the stream And write binary data To the object
fsT.WriteText "special characters: ??ü?"
fsT.SaveToFile sFileName, 2 'Save binary data To disk
Certainly not as I expected...
肯定不是我预想的...
回答by Má?a - Stitod.cz
You can use CreateTextFile or OpenTextFile method, both have an attribute "unicode" usefull for encoding settings.
您可以使用 CreateTextFile 或 OpenTextFile 方法，两者都有一个对编码设置有用的属性“unicode”。
object.CreateTextFile(filename[, overwrite[, unicode]])
object.OpenTextFile(filename[, iomode[, create[, format]]])
Example: Overwrite:
示例：覆盖：
CreateTextFile:
fileName = "filename"
Set fso = CreateObject("Scripting.FileSystemObject")
Set out = fso.CreateTextFile(fileName, True, True)
out.WriteLine ("Hello world!")
...
out.close
Example: Append:
示例：附加：
OpenTextFile Set fso = CreateObject("Scripting.FileSystemObject")
Set out = fso.OpenTextFile("filename", ForAppending, True, 1)
out.Write "Hello world!"
...
out.Close
See more on MSDN docs
在MSDN 文档上查看更多信息
回答by PhilHibbs
This writes a Byte Order Mark at the start of the file, which is unnecessary in a UTF-8 file and some applications (in my case, SAP) don't like it. Solution here: Can I export excel data with UTF-8 without BOM?
这会在文件的开头写入一个字节顺序标记，这在 UTF-8 文件中是不必要的，并且某些应用程序（在我的情况下是 SAP）不喜欢它。此处的解决方案：我可以在没有 BOM 的情况下使用 UTF-8 导出 excel 数据吗？
回答by Falo
Here is another way to do this - using the API function WideCharToMultiByte:
这是执行此操作的另一种方法 - 使用 API 函数 WideCharToMultiByte：
Option Explicit
Private Declare Function WideCharToMultiByte Lib "kernel32.dll" ( _
ByVal CodePage As Long, _
ByVal dwFlags As Long, _
ByVal lpWideCharStr As Long, _
ByVal cchWideChar As Long, _
ByVal lpMultiByteStr As Long, _
ByVal cbMultiByte As Long, _
ByVal lpDefaultChar As Long, _
ByVal lpUsedDefaultChar As Long) As Long
Private Sub getUtf8(ByRef s As String, ByRef b() As Byte)
Const CP_UTF8 As Long = 65001
Dim len_s As Long
Dim ptr_s As Long
Dim size As Long
Erase b
len_s = Len(s)
If len_s = 0 Then _
Err.Raise 30030, , "Len(WideChars) = 0"
ptr_s = StrPtr(s)
size = WideCharToMultiByte(CP_UTF8, 0, ptr_s, len_s, 0, 0, 0, 0)
If size = 0 Then _
Err.Raise 30030, , "WideCharToMultiByte() = 0"
ReDim b(0 To size - 1)
If WideCharToMultiByte(CP_UTF8, 0, ptr_s, len_s, VarPtr(b(0)), size, 0, 0) = 0 Then _
Err.Raise 30030, , "WideCharToMultiByte(" & Format$(size) & ") = 0"
End Sub
Public Sub writeUtf()
Dim file As Integer
Dim s As String
Dim b() As Byte
s = "??ü?μ@|~{}[]23\ .." & _
" OMEGA" & ChrW$(937) & ", SIGMA" & ChrW$(931) & _
", alpha" & ChrW$(945) & ", beta" & ChrW$(946) & ", pi" & ChrW$(960) & vbCrLf
file = FreeFile
Open "C:\Temp\TestUtf8.txt" For Binary Access Write Lock Read Write As #file
getUtf8 s, b
Put #file, , b
Close #file
End Sub
回答by Bennett Brown
I looked into the answer from Má?a whose name hints at encoding qualifications and experience. The VBA docssay CreateTextFile(filename, [overwrite [, unicode]])creates a file "as a Unicode or ASCII file. The value is True if the file is created as a Unicode file; False if it's created as an ASCII file. If omitted, an ASCII file is assumed." It's fine that a file stores unicode characters, but in what encoding? Unencoded unicode can't be represented in a file.
我查看了 Má?a 的答案，她的名字暗示了编码资格和经验。在VBA文档说，CreateTextFile(filename, [overwrite [, unicode]])创建一个文件“作为Unicode或ASCII文件中的值是真，如果该文件是为Unicode文件创建;如果它作为一个ASCII文件创建False如果省略，一个ASCII文件假设。” 文件存储 unicode 字符很好，但是用什么编码？未编码的 unicode 无法在文件中表示。
The VBA doc pagefor OpenTextFile(filename[, iomode[, create[, format]]])offers a third option for the format:
在VBA文档页的OpenTextFile(filename[, iomode[, create[, format]]])计划书的格式第三种选择：
- TriStateDefault 2 "opens the file using the system default."
- TriStateTrue 1 "opens the file as Unicode."
- TriStateFalse 0 "opens the file as ASCII."
- TriStateDefault 2“使用系统默认值打开文件”。
- TriStateTrue 1 “以 Unicode 格式打开文件”。
- TriStateFalse 0 “以 ASCII 格式打开文件。”
Má?a passes -1 for this argument.
Má?a 为这个参数传递 -1。
Judging from VB.NET documentation(not VBA but I think reflects realities about how underlying Windows OS represents unicode strings and echoes up into MS Office, I don't know) the system default is an encoding using 1 byte/unicode character using an ANSI code page for the locale. UnicodeEncodingis UTF-16. The docs also describe UTF-8 is also a "Unicode encoding," which makes sense to me. But I don't yet know how to specify UTF-8 for VBA output nor be confident that the data I write to disk with the OpenTextFile(,,,1) is UTF-16 encoded. Tamalek's postis helpful.
从VB.NET 文档来看（不是 VBA，但我认为反映了底层 Windows 操作系统如何表示 unicode 字符串并回显到 MS Office 中的现实，我不知道）系统默认是使用 1 字节/unicode 字符的编码，使用 ANSI语言环境的代码页。UnicodeEncoding是 UTF-16。文档还描述了 UTF-8 也是一种“Unicode 编码”，这对我来说很有意义。但我还不知道如何为 VBA 输出指定 UTF-8，也不知道我用 OpenTextFile(,,,1) 写入磁盘的数据是 UTF-16 编码的。Tamalek 的帖子很有帮助。
