IntelliSense and XML file. Characters problems (encoding?)

[All That For That]

Hello everybody,

I'm using ExcelDna.IntelliSense64.xll with external XML.

My problem is that the characters are poorly displayed even if I add the header: encoding = UTF-8.

Is anybody resolve this IntelliSense problem?

Thank you in advance for your help.

[Govert van Drimmelen]

Could you post a small example xml file that I can test?

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/exceldna/57d4eb9a-8183-44af-8e7d-9f69d4194e73n%40googlegroups.com.

[All That For That]

I tried to add that 1srt line in the XML:

• <?xml version="1.0" encoding="UTF-8"?>

but it did nothing more (or less).

Here 2 exemples. One in French the other in Spanish.

French:

Spanish:

Thank you.

[Govert van Drimmelen]

When I test with these files as external xml files, I don't see a problem.

Can you confirm whether you see the problem with a loose .xml file?

I think the problem happens when we read the xml file to write it into the CustomParts.

(This is the code from the VBASample here: IntelliSense/VBASamples at master · Excel-DNA/IntelliSense )

```````````````````````````

' Copy THE NEW xml file
  If Len(Trim(Dir(CountryFileName))) > 0 Then
    ' Replace the old file
    FileCopy CountryFileName, FunctionPath & UsedFileName
    iFile = FreeFile
    Open FunctionPath & UsedFileName For Input As #iFile
    ' Load the new XLL content
    FileContent = Input(LOF(iFile), iFile)
    Close #iFile
   
' TODO:  About encoding problem !!
'      FileContent = "<?xml version=""1.0"" encoding=""UTF-8""?>" & vbCrLf & FileContent

    ' Add the new XML file in CustomXMLParts
    Temp = FileContent
    ThisWorkbook.CustomXMLParts.Add Temp

`````````````````````

Instead of just using the VBA Input function, I think one needs to use the Windows API to convert properly from UTF-8 to the Windows strings.

Here is some code that does this

````````````````````

Private Declare PtrSafe Function MultiByteToWideChar Lib "kernel32" ( _
    ByVal CodePage As Long, ByVal dwFlags As Long, _
    lpMultiByteStr As Any, ByVal cbMultiByte As Long, _
    lpWideCharStr As Any, ByVal cchWideChar As Long) As Long

Function ReadFileUTF8(FilePath As String) As String
    Dim bytes() As Byte
    Dim fileNo As Integer
    Dim fileLen As Long
    Dim requiredSize As Long
    Dim result As String
   
    fileNo = FreeFile
    Open FilePath For Binary Access Read As #fileNo
    fileLen = LOF(fileNo)
   
    If fileLen > 0 Then
        ReDim bytes(0 To fileLen - 1)
        Get #fileNo, , bytes
    End If
    Close #fileNo
   
    ' Determine the required size for the destination Unicode string
    requiredSize = MultiByteToWideChar(65001, 0, bytes(0), fileLen, ByVal 0&, 0)
   
    If requiredSize > 0 Then
        result = String$(requiredSize, vbNullChar)
        MultiByteToWideChar 65001, 0, bytes(0), fileLen, ByVal StrPtr(result), requiredSize
    Else
        result = ""
    End If
   
    ReadFileUTF8 = result
End Function

`````````````````````````

Then the CustomParts update function becomes something like this

````````````````````````````

Sub EmbedIntelliSense()

    Dim strFilename As String
    strFilename = "<path to .IntelliSense.xml file>"
   
    Dim strFileContent As String
    strFileContent = ReadFileUTF8(strFilename)
    Debug.Print strFileContent

    ThisWorkbook.CustomXMLParts.Add strFileContent

End Sub

````````````````````````````

-Govert

[All Vero]

Hi @Govert
Thanks to care about the subject.

I did not see this message yesterday night on my phone. Sorry.

As I wrote in the other one, I am going to test the code today.

Thanks again.

I will let you know as soon as I di it.

AL7

[All Vero]

HI Govert,

After manually rename file default XML country file, I tried your code using Sub EmbedIntelliSense() and ReadFileUTF8
It doesn't change. I tried also to use the Refresh DNA function with the same above Sub call. Still the same.

So ...
I did that:

1.          If exist, delete the actual XML in  CustomXMLParts
2. FileCopy <CountryFileName>, <DefaultFileName>
3. FileContent = ReadFileUTF8(<DefaultFileName>)
4. ThisWorkbook.CustomXMLParts.Add FileContent
5. Rep = RefreshDNA() 'Your DNA REFRESH Function

AND … 

It works!

Every thing is good. Even encoding.

♪♫ Ô HAPPY DAY !! ♪♫ 😁