Excel vba解析复杂XML
Excel vba解析复杂XML
提问于 2017-05-13 03:27:37
我需要从xml文件中获取数据并将其加载到Excel工作表中。我能够加载xml文件并在messagebox中获取元素数据。这将用VBA为Excel 2013编写
xml文件结构
<?xml version="1.0" encoding="utf-8" ?>
<Response >
<Status>10</Status>
<callMessage>Success.</callMessage>
<PullR version="1">
<responseStatus>1000</responseStatus>
<responseMessage>Success.</responseMessage>
<Options>
<Option>
<header>
<need1>text1</need1> ---- "COLUMN1"
<need2>
<![CDATA[text2]]>
</need2> ---- "COLUMN2"
<need 3>this text3 together with need 2</need3>"COLUMN2"
<need4>
<![CDATA[Text4]]>
</need4> ---"COLUMN4"
</header>
<Details> - ""this one could be 1 to 10 child element (detail) this sample has 2 ""
<detail>
<need5>text5</need5> " COLUMN5"
<need6>date6</need6>
<need7>time7</need7>
<need8>
<![CDATA[text8]]>
</need8>
</detail>
<detail> ---- ""this should be on second row in excel sheet ""
<need5>text5</need5> --- "COLUMN5 ROW +1"
<need6>date6</need6> ---- "COLUMN6 ROW +1"
<need7>time7</need7>
<need8>
<![CDATA[text8]]>
</need8>
</detail>
</Details>
</Option>
</Options>
</PullR>
</Response>我减少了xml文件的子元素--还有更多，当它为下一个选项祖先元素写入第二行时，我被困在了细节父元素部分。单元格中的日期将是我输入的文本(原子值)字段，与xml文件中的日期完全相同。
Dim Options As IXMLDOMNodeList
Dim Option As IXMLDOMNode
Set Options = rosDoc.SelectNodes("/Response/PullR/Options/Option")
For Each Option In Options
MsgBox Trip.SelectNodes("header/need1").Item(0).Text
MsgBox Trip.SelectNodes("Details/Detail/need5").Item(0).Text
MsgBox Trip.SelectNodes("Details/Detail/need5").Item(1).Text
Next这段代码工作得很好，但是need5是可变的，大多数情况下有2项，但有时可能会有更多的方法，在选项中对need5进行计数。
回答 2
Stack Overflow用户
回答已采纳
发布于 2017-05-13 08:22:57
VBA支持xpath。这是从xml文件中处理数据的最简单方法。
下面是一些示例代码，您可以开始使用这些代码：
Dim doc As DOMDocument
Set doc = New DOMDocument
doc.Load "C:\x.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
NextStack Overflow用户
发布于 2017-05-14 00:30:12
再次感谢大卫·G我想出了办法
这是我的最后一段代码，也许有人能帮我清理一下，因为我得写几遍need5
Dim Options As IXMLDOMNodeList
Dim Option As IXMLDOMNode
Set Options = rosDoc.SelectNodes("/Response/PullR/Options/Option")
For Each Option In Options
MsgBox Trip.SelectNodes("header/need1").Item(0).Text
MsgBox Trip.SelectNodes("Details/Detail/need5").Item(0).Text
IF not Trip.SelectNodes("Details/Detail/need5").Item(1) is nothing then
MsgBox Trip.SelectNodes("Details/Detail/need5").Item(1).Text
IF not Trip.SelectNodes("Details/Detail/need5").Item(2) is nothing then
MsgBox Trip.SelectNodes("Details/Detail/need5").Item(2).Text
'etc........
Next页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/43948915
复制相关文章
相似问题
腾讯云开发者
