从VBA解析XML无法正常工作 [英] Parse XML from VBA not working

问题描述

我正在尝试使用excel vba解析xml文件.这是我的xml文件的样子:

I am trying to parse xml file using excel vba. This is how my xml file looks like:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<Environment>
    <Variable>
        <Name></Name>
        <Caption>T1</Caption>
        <Type>TEXT</Type>
        <Value>V1</Value>
        <Description></Description>
    </Variable>
        <Variable>
        <Name></Name>
        <Caption>T2</Caption>
        <Type>TEXT</Type>
        <Value>V2</Value>
        <Description></Description>
    </Variable>
        <Variable>
        <Name></Name>
        <Caption>T3</Caption>
        <Type>TEXT</Type>
        <Value>V3</Value>
        <Description></Description>
    </Variable> <Variable>
        <Name></Name>
        <Caption>T4</Caption>
        <Type>TEXT</Type>
        <Value>V4</Value>
        <Description></Description>
    </Variable> <Variable>
        <Name></Name>
        <Caption>T5</Caption>
        <Type>TEXT</Type>
        <Value>V5</Value>
        <Description></Description>
    </Variable>
    </Variable> <Variable>
        <Name></Name>
        <Caption>T6</Caption>
        <Type>TEXT</Type>
        <Value>V6</Value>
        <Description></Description>
    </Variable>
</Environment>

这是我解析它的代码:

Public Function ll()
Dim doc As DOMDocument60
Set doc = New DOMDocument60
doc.Load "E:\web\cc.xml"
Dim Variables As IXMLDOMNodeList
Dim variable As IXMLDOMNode
Set Variables = doc.SelectNodes("/Environment/Variable")
For Each variable In Variables
    Debug.Print variable.SelectNodes("Caption").Item(0).Text
    Debug.Print variable.SelectNodes("Type").Item(0).Text
Next
End Function

但是我无法成功运行它.我正在使用Excel 2013，并且已引用Microsoft XML 6.0.但是执行时什么也没有发生.我尝试使用on错误语句捕获错误，但它返回错误号0.任何人都可以在这里致电给我正在做错的事情.

But I am not able to get this successfully run. I am using Excel 2013 and I have referenced to Microsoft XML 6.0. But nothing happens on execution. I tried to catch error with an on error statement, but it returns error number 0. Can anybody tel what I am doing wrong here.

推荐答案

只有XML格式正确，XML解析器才能工作.如果不是，则 oXMLDOMDocument.load 方法失败.

A XML parser can only work if the XML is well formed. If not, the oXMLDOMDocument.load method fails.

要对此进行检查，您可以像这样使用 oXMLDOMDocument.parseError :

To check this, you could use oXMLDOMDocument.parseError like this:

Public Function ll()

 Dim oDoc As Object
 Set oDoc = CreateObject("MSXML2.DOMDocument")
 oDoc.Load "E:\web\cc.xml"

 Dim oErr As Object

 If oDoc.parseError.ErrorCode <> 0 Then
  Set oErr = oDoc.parseError
  Debug.Print oErr.reason
 End If

 Dim oVariables As Object
 Dim oVariable As Object
 Set oVariables = oDoc.SelectNodes("/Environment/Variable")
 For Each oVariable In oVariables
  Debug.Print oVariable.SelectNodes("Caption").Item(0).Text
  Debug.Print oVariable.SelectNodes("Type").Item(0).Text
 Next

End Function

请参阅: https://msdn.microsoft.com/en-us/library/ms762722%28v=vs.85%29.aspx

这篇关于从VBA解析XML无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！