Excel VBA: Open UTF-16 XML

Question

我目前正在努力在Excel中使用VBA打开utf-16编码的XML文件。

我当前标题为 EntireFile 的字符串变量当前是这样开始的：

ÿþ<?xml version="1.0" encoding="utf-16"?>
<Test xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> 

如您所见，开头有一些字符似乎不对劲。

我通过以下方式获得字符串变量：

Open PathToFile For Input As #1
    Do Until EOF(1)
              Line Input #1, textline
              EntireFile = EntireFile & textline

文件根据记事本++格式化为UCS-2小端序，但通过互联网快速搜索显示这是Microsoft等同于UTF-16？

我尝试了仅删除前两个字符的蛮力方法，但这给我留下了一个空字符串。

所有谷歌结果都涵盖了在没有BOM的情况下保存XML文件，但这与我正在寻找的相反。

已经感谢您的时间

Answer 1

您可以使用 Win32 API 函数来转换编码。

Private Declare Function WideCharToMultiByte Lib "kernel32.dll" ( _
                         ByVal CodePage As Long, _
                         ByVal dwFlags As Long, _
                         ByVal lpWideCharStr As Long, _
                         ByVal cchWideChar As Long, _
                         ByVal lpMultiByteStr As Long, _
                         ByVal cbMultiByte As Long, _
                         ByVal lpDefaultChar As Long, _
                         ByVal lpUsedDefaultChar As Long) As Long
Private Declare Function MultiByteToWideChar Lib "kernel32.dll" ( _
                         ByVal CodePage As Long, _
                         ByVal dwFlags As Long, _
                         ByVal lpMultiByteStr As Long, _
                         ByVal cbMultiByte As Long, _
                         ByVal lpWideCharStr As Long, _
                         ByVal cchWideChar As Long) As Long
Private Const CP_UTF16 As Long = 1200&
Private Function ConvertToUTF16(ByRef Source As String) As Byte()
    Dim Length As Long
    Dim Pointer As Long
    Dim Size As Long
    Dim Buffer() As Byte
    Length = Len(Source)
    Pointer = StrPtr(Source)
    Size = WideCharToMultiByte(CP_UTF16, 0, Pointer, Length, 0, 0, 0, 0)
    ReDim Buffer(0 To Size - 1)
    WideCharToMultiByte CP_UTF16, 0, Pointer, Length, VarPtr(Buffer(0)), _
        Size, 0, 0
    ConvertToUTF16 = Buffer
End Function
Private Function ConvertFromUTF16(ByRef Source() As Byte) As String
    Dim Size As Long
    Dim Pointer As Long
    Dim Length As Long
    Dim Buffer As String
    Size = UBound(Source) - LBound(Source) + 1
    Pointer = VarPtr(Source(LBound(Source)))
    Length = MultiByteToWideChar(CP_UTF16, 0, Pointer, Size, 0, 0)
    Buffer = Space$(Length)
    MultiByteToWideChar CP_UTF16, 0, Pointer, Size, StrPtr(Buffer), Length
    ConvertFromUTF16 = Buffer
End Function

Private Const CP_UTF16 As Long = 1200& 表示代码页 1200，即 UTF-16 小安迪安。

您可以在此处查看所有代码页的列表 https://msdn.microsoft.com/de-de/library/windows/desktop/dd317756(v=vs.85(.aspx