Mac Excel生成UTF-8

Question

是否可以在Excel 2016 for Mac中使用Visual Basic (VBA)生成UTF-8文件？我需要生成一个编码的XML或TXT文件。

谢谢

Answer 1

不是UTF-8，而是VBA内部使用的VBA UTF-16，因此您可以直接转储字符串数据：

Dim fnum As Integer
fnum = FreeFile()
Open "utf16.txt" For Binary Access Write As fnum

'write UTF-16 Byte Order Marker
'
Put #fnum, 1, &HFE
Put #fnum, 2, &HFF

printLineUtf16 fnum, "Olá Mundo!"

Close #fnum

Function printLineUtf16(fnum As Integer, str As String)

    str = str & ChrW(10)            'append newline

    Dim data() As Byte              'coerce string to bytes
    data = str

    Put #fnum, LOF(fnum) + 1, data  'append bytes to file

End Function

这应该适用于Excel Mac 2011和2016，以及Windows版本。请注意，您不能在编辑器中输入Unicode字符串文字，只能输入简单的重音拉丁字母，如上面的“a”，但从单元格的Value分配的字符串将保留在Unicode中。

Answer 2

经过4年的研究;)，这是我想出的最好的解决方案：

Sub test()
    Dim xmlStr As String
    xmlStr = "<h1 class=""bold"">UTF8 Test -ÄÖÜêçñù-</h1>"
    writeToFile xmlStr, "utf8.html"
End Sub


Function writeToFile(str As String, filename As String)
    'escape double quotes
    str = Replace(str, """", "\\\""")

    ' use Apple Script and shell commands to create and write file
    MacScript ("do shell script ""printf '" & str & "'> " & filename & " "" ")

    ' print file path
    Debug.Print "file path: " & MacScript("do shell script ""pwd""") & "/" & filename
End Function

我还没有找到一种打破Apple沙盒环境的方法。因此，我将所有文件写入Excel沙箱容器路径：

/Users/myuser/Library/Containers/com.microsoft.Excel/Data