How to save XML file in UTF-8 encoding?

[simurq]

I have a module which writes XML data to a text file and saves it as .PLN file which in fact is a tagged XML file. However, when validating the file against XSD schema I get this error:

Reason: Switch from current encoding to specified encoding not supported.

In other words, instead of saving the file in UTF-8 without BOM, as required, the resulting file is encoded in UCS-2 Little Indian. Below is a part of VBA code in question. I also attach a link to my XSD schema, if it helps.

    ...
    ...

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set A = fso.CreateTextFile(myFile, Overwrite:=True, Unicode:=True)
    
    [COLOR=#008000]'* XML tags are replaced with square brackets to make the code viewable in MrExcel.[/COLOR]
    A.WriteLine ("[?xml version="1.0" encoding="UTF-8"?]")
    A.WriteLine ("[SimBase.Document Type=""AceXML"" version=""1,0""]")
    A.WriteLine ("    [Descr]AceXML Document[/Descr]")
    ...
    ...

Can you please help to correct this saving behaviour?

Thank you!

Rustam

 

[pgc01]

Hi Rustam

1 -

As I wrote in a post not long ago, I don't think that you can save directly from vba in utf-8.
I posted a solution using a stream here:

http://www.mrexcel.com/forum/excel-questions/838672-creating-utf-8-encoded-text-file.html#2


2 -

I think you forgot to double the double quotes in the double quoted string in the first write statement.

I guess that you need instead of

A.WriteLine ("[?xml version="1.0" encoding="UTF-8"?]")

to double the inside quote characters:

A.WriteLine ("<?xml version=""1.0"" encoding=""UTF-8""?>")

Hope this helps

 

[simurq]

Thank you, pgc01!!! (Sorry, I don't know your name...)

Yes, ADO seems the optimal solution in my case. I've tried this solution as well but running each line of text (and even the entire file as a string) through the ToUTF8() function didn't appeal to me...

Btw, do I need to ask users to add reference to ADO Library manually or simply saving the file with proper referencing is enough?

Rustam

 

[pgc01]

Hi

I don't think you'll have any problem, unless someone has an old pc with an old excel version.

If you have problems with the references, one solution is not to use them and to use late binding in the code:

Sub Test_utf_8()
Dim st As Object ' ADODB.Stream
Dim sPathname As String

sPathname = "c:\tmp\test_utf-8.txt"

' create a stream object
Set st = CreateObject("ADODB.Stream") 'New ADODB.Stream

' set properties
st.Charset = "utf-8"
st.Type = 2 ' adTypeText

' open the stream object and write some text
st.Open
st.WriteText "This is a test"

' save
st.SaveToFile sPathname, 2 ' adSaveCreateOverWrite

End Sub