utf 8-保存用VBA编码的文本文件UTF-8
我怎样才能从VBA将UTF-8编码的字符串写入文本文件,例如
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
在应用程序级别上有设置吗?
Karsten W. asked -07-19T12:42:52Z
5个解决方案
71 votes
我在网上找到了答案:
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
当然不是我所期望的...
Karsten W. answered -07-19T12:43:10Z
16 votes
您可以使用CreateTextFile或OpenTextFile方法,它们都具有“ unicode”属性,可用于编码设置。
object.CreateTextFile(filename[, overwrite[, unicode]])
object.OpenTextFile(filename[, iomode[, create[, format]]])
示例:覆盖:
CreateTextFile:
fileName = "filename"
Set fso = CreateObject("Scripting.FileSystemObject")
Set out = fso.CreateTextFile(fileName, True, True)
out.WriteLine ("Hello world!")
...
out.close
示例:追加:
OpenTextFile Set fso = CreateObject("Scripting.FileSystemObject")
Set out = fso.OpenTextFile("filename", ForAppending, True, 1)
out.Write "Hello world!"
...
out.Close
在MSDN文档上查看更多信息
Máťa - Stitod.cz answered -07-19T12:43:43Z
8 votes
这会在文件的开头写入字节顺序标记,这在UTF-8文件中是不必要的,并且某些应用程序(在我的情况下为SAP)不喜欢它。这里的解决方案:我可以使用没有BOM的UTF-8导出Excel数据吗?
PhilHibbs answered -07-19T12:44:03Z
6 votes
这是执行此操作的另一种方法-使用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 = "äöüßµ@€|~{}[]²³\ .." & _
" 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
Falo answered -07-19T12:44:23Z
2 votes
我调查了来自Máťa的答案,他的名字暗示了编码资格和经验。 VBA文档说UnicodeEncoding创建文件“作为Unicode或ASCII文件。如果将文件创建为Unicode文件,则值为True;如果将其创建为ASCII文件,则为False。如果省略,则假定为ASCII文件。” 文件存储Unicode字符很好,但是用什么编码呢? 未编码的unicode不能在文件中表示。
UnicodeEncoding的VBA文档页面为该格式提供了第三个选项:
TriStateDefault 2“使用系统默认值打开文件。”
TriStateTrue 1“以Unicode打开文件”。
TriStateFalse 0“以ASCII文件形式打开文件。”
玛雅(Máťa)为此参数传递-1。
从文档(不是VBA,但我认为反映了有关底层Windows OS如何表示unicode字符串并回显到MS Office的现实,我不知道),系统默认值是使用ANSI的1字节/ Unicode字符的编码 语言环境的代码页。 UnicodeEncoding是UTF-16。 该文档还描述了UTF-8也是一种“ Unicode编码”,这对我来说很有意义。 但是我还不知道如何为VBA输出指定UTF-8,也不确定我用OpenTextFile(,,, 1)写入磁盘的数据是UTF-16编码的。 Tamalek的帖子很有帮助。
Bennett Brown answered -07-19T12:45:11Z