Parse Multilevel XML input using Excel VBA

Question

In general, need to parse input matching certain filter criteria from XML using Excel VBA. The complication lies in the multilevel XML attributes and its pairing result which has to be populated in sequence inside Excel.

XML content as below:

<Flow index="1" item="1" itemName="BB_150" ID="XXX">
      <Attribute name="FUNCTIONAL_X" dataType="Double" unit="" multiplier="" tag="YY">1</Attribute>
      <Attribute name="X_LOC" dataType="String" unit="" multiplier="" tag="YY">2</Attribute>
</Flow>
<Flow index="2" item="2" itemName="CC_200" ID="WWW">
      <Attribute name="FUNCTIONAL_X" dataType="Double" unit="" multiplier="" tag="YY">15</Attribute>
      <Attribute name="X_LOC" dataType="String" unit="" multiplier="" tag="YY">3</Attribute>
</Flow>
<Flow index="3" item="3" itemName="DD_200" ID="UUU">
      <Attribute name="FUNCTIONAL_X" dataType="Double" unit="" multiplier="" tag="YY">20</Attribute>
      <Attribute name="X_LOC" dataType="String" unit="" multiplier="" tag="YY">1</Attribute>
</Flow>
<Flow index="4" item="4" itemName="EE_115" ID="SSS">
      <Attribute name="FUNCTIONAL_X" dataType="Double" unit="" multiplier="" tag="YY">33</Attribute>
      <Attribute name="X_LOC" dataType="String" unit="" multiplier="" tag="YY">2</Attribute>
</Flow>

How do i read only those nodes starting with Attribute in the XML?
Once all nodes with Attribute selected, further parsing in Excel VBA starts. First, look for name=X_LOC and get the value.
Then, look for name = FUNCTIONAL_X and retrieve the value.
Now, need to put all these values into Excel column in worksheet 'Result'. The format of output into Excel as below:

Column (starts with Column A..then continue to next column..etc) Header Name = X_LOC (sort and display in ascending order) Row Result (starts with row1..then row2..etc) = FUNCTIONAL_X (per each pairing X_LOC)

Result populated into Excel should look like below:

A  B  C  D (Excel column)

1  2  3  4 (X_LOC value)

A1 B1 C1 D1 (Excel Row)

1  15 20 33 (FUNCTIONAL_X value)

Note: if there's repeat of same value for X_LOC, do nothing. Remain the existing X_LOC & its FUNCTIONAL_X value. No overwrite needed.

Jean-François Corbett · Accepted Answer · 2011-03-08 11:56:26Z

1

Manipulating XML data in Excel is unbelievably cumbersome. I would just stick to VBA. Create an XML document as shown below, edit it (using XPath to access the required nodes/elements) and then save it.

Set xmlInventory = CreateObject("Msxml2.DOMDocument.6.0")

Documentation: http://msdn.microsoft.com/en-us/library/ms756987%28v=VS.85%29.aspx

XPath tutorial: http://www.w3schools.com/xpath/default.asp

answered Mar 8, 2011 at 11:56

Jean-François Corbett

38.7k30 gold badges144 silver badges192 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

LeenNew Over a year ago

Thanks, Jean..will read it. It's a requirement of the assignment to create excel macro and that is part of the function. So far, i've managed to load the XML (with help from the forum good samaritan) and filter out value from those attributes i need.However, still getting error storing them into multidimensional array where i'm trying to anchor X_LOC.

LeenNew Over a year ago

Declaration: Dim xmldoc As DOMDocument Dim node As IXMLDOMNodeList Dim n As IXMLDOMNode Set xmldoc = New DOMDocument I've created Set node = xmldoc.SelectNodes("//Attribute") - to find all nodes containing 'Attribute'. Can someone pls help show me how the filter criteria's format if i'd like to retrieve one of the nodes let's say name = X_LOC from that nodelist call node.

Collectives™ on Stack Overflow

Parse Multilevel XML input using Excel VBA

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related