Why I am losing font attributes when exporting(line by line) to an xml file

[MPW]

Hello,
I am creating an xml from scratch taking information from sheets in a workbook.
The workbook 3rd cell looks correct:

The individual line is added like this:

        hsy = Sheets("Directive Lang").Cells(7, 3).Value2
        strXML = lvl04 & "<heSaidText>" & hsy & "</heSaidText>"
            Print #intFileNum, strXML

This is how it displays in the Watch window:

Here is a sample of my output:

  <ReferenceTexts>
    <ReferenceTextInfo level="0">
      <Language>
        <iso></iso>
        <name>RUSSIAN</name>
        <ldml>ru</ldml>
        <fontFamily>Charis SIL</fontFamily>
        <fontSizeInPoints>10</fontSizeInPoints>
        <heSaidText>o? ??????.</heSaidText>
      </Language>
    </ReferenceTextInfo>
    <ReferenceTextInfo level="1" name="WEB (WEB)">
      <Language>
        <iso>eng</iso>
        <name>English</name>
        <ldml>en</ldml>
        <fontFamily>Charis SIL</fontFamily>
        <fontSizeInPoints>10</fontSizeInPoints>
        <heSaidText>he said.</heSaidText>

Notice the bold lines. The English text displays fine, but the Russian version displays "?" for most characters.

Most of the languages I have worked with display correctly, however some turn out like this.

I already open the final result in NotePad++ to convert it from Ansi to UTF-8, but the text is incorrect before this stage, in the VBA code.

Part 2 of this request is "How do I convert it automatically on the export without the extra step of using NotePad++?

If you have any insight on either of these issues I will be very grateful.

Thanks in advance.

 

[Domenic]

Try using the Stream object from the Microsoft ActiveX Data Objects library, which supports UTF-8. Maybe something like this...

    Dim oStream As Object 'ADODB.Stream
    Set oStream = CreateObject("ADODB.Stream") 'New ADODB.Stream
   
    With oStream
        .Charset = "UTF-8"
        .Mode = 3 'adModeReadWrite
        .Type = 2 'adTypeText
        .Open
       
        hsy = Sheets("Directive Lang").Cells(7, 3).Value2
        strXML = lvl04 & "<heSaidText>" & hsy & "</heSaidText>"
       
        .WriteText strXML
        .SaveToFile "c:\path\filename.txt", 1 'adSaveCreateNotExist
        .Close
    End With

Hope this helps!

 

[MPW]

Ok, I can try this, however I am relatively new to XML in general, I was using:

    Dim xmlDom As MSXML2.DOMDocument60          
    Dim xmlVersion As MSXML2.IXMLDOMProcessingInstruction   

    Set xmlDom = New MSXML2.DOMDocument60

    Set xmlVersion = xmlDom.createProcessingInstruction("xml", "version=" & ChrW(34) & "1.0" & ChrW(34) & " encoding=" & ChrW(34) & "utf-8" & ChrW(34))
    xmlDom.appendChild xmlVersion

Would I use your suggestion with this, or instead of this? Will this require that I re write the whole routine?

When I took this on last year, I really did not know what I was doing. It all does work except the 2 issues I mentioned, but it took a lot of trial and error.

 

[MPW]

Ok, awesome! I switched out the DOMDocument60 with the streamwriter and it worked. Only real adjustment was to add Chr(10) after each line. I do remember trying this method when I was investigating it last year. I think I failed because I had other forces at work. Thanks Domenic for the quick answer!

 

[MPW]

One last comment. The old method I was using had to converted to UTF-8 in Notepad++. The new version that Domenic suggested automatically converted the output to UTF-8 BOM. Nice.

It seemed to load into the program that it is intended for without a problem. Thanks again!

 

[Domenic]

That's great, I'm glad I was able to help. And thanks for your feedback.

By the way, were you using Print #intFileNum, strXML as in your original post or the DomDocument object as in your subsequent post? If the latter, you should have been able to save the document as a UTF-8 file with xmlDoc.Save after you've created your XML code.

Cheers!

 

[MPW]

I was actually using both.
As I stated, I didn't really have a handle on it. I never could get it to save as true xml, probably because I was mixing apples and oranges.

Made me really happy to get it consolidated with your answer.

 

[Domenic]

Okay, that's great then . ..

Cheers!