CODEWITHSUNDEEP
excelvbamacos
Mark
Mark

Reputation: 39

Mac Excel generate UTF-8

Is it possible to generate a UTF-8 file using Visual Basic (VBA) in Excel 2016 for Mac? I need to generate an XML or TXT file that is encoded.

Thanks

Upvotes: 2

Views: 3084

Answers (2)

Philipp Waller
Philipp Waller

Reputation: 604

After 4 years of research ;), this is the best solution I came up with:

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

I have not found a way to break out of the Apple sandbox environment. Therefore, I write all files to the Excel sandbox container path:

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

Upvotes: 1

Micropig
Micropig

Reputation: 81

Not UTF-8, but UTF-16 is what VBA uses internally, so you can dump string data directly:

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

This should work in Excel Mac 2011 and 2016, and in Windows versions. Note that you won't be able to type Unicode string literals in the editor, just simple accented Latin letters like the "á" above, but strings assigned from the Value of a cell will be preserved in Unicode.

Upvotes: 4

Related Questions