Excel vba解析复杂XML [英] Excel vba Parse Complex XML

问题描述

我需要从xml文件中获取数据并将其加载到Excel工作表中.我能够加载xml文件并在消息框中获取元素数据.这将用VBA for Excel 2013编写

I need to get data from an xml file and load it to an Excel worksheet. I'm able to load the xml file and get element data in messagebox. This will written in VBA for 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文件的子元素，还有更多，我停留在详细信息父元素部分，因为当它写第二行时，它为下一个Option祖先元素写了need1. 单元格中的日期将是我输入的与xml文件完全相同的文本(原子值)字段.

I reduce the xml file child elements there is many more , I'm stuck on the detail parent element part as when it writes the 2nd row it writes need1 for the next Option ancestor element. The date in the cell will be the text (atomic value) field which I inputed exactly as there are in the xml file.

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的计数来获得更多选择

This code works perfect but need5 is variable most cases there is 2 items but sometimes there might be more how will I go by making a count off need5 within option

推荐答案

VBA支持

VBA supports xpath. This is imo the easiest way to process data from xml files.

以下是一些示例代码，可以帮助您入门:

Here is some sample code that will get you started:

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
    Next

这篇关于Excel vba解析复杂XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！