Query and parse xml attribute value into XLS using VBA

Question

I'm trying to open/load an XML file specified in an Excel worksheet in the range B2. Then, search through a list of XML attributes for name=FUNCTIONAL_ITEM and get all the attribute values after ">.

In the following example, i'd like to extract out the value 8, 9 and 10.

<Attribute name="BIN" dataType="String" unit="" multiplier="" tag="LINE,MRPM">1</Attribute>
<Attribute name="FUNCTIONAL_ITEM" dataType="Double" unit="" multiplier="" tag="LINE,LINE DB">8</Attribute>
<Attribute name="FUNCTIONAL_ITEM" dataType="Double" unit="" multiplier="" tag="LINE,LINE DB">9</Attribute>
<Attribute name="FUNCTIONAL_ITEM" dataType="Double" unit="" multiplier="" tag="LINE,LINE DB">10</Attribute>

Could someone please point me in the right direction for implementing this.

Sorry, this question is way to broad. Break it down it to steps and research them independently. 1. Retrieving file name from range. 2. Opening an Xml file. 3. Parsing Xml from VBA. I'm guessing you can find answers for all of those already here on Stack Overflow. If not, ask a specific question. — Benjol
– Benjol, Commented Mar 4, 2011 at 12:02

barrowc · Accepted Answer · 2011-03-05 08:59:26Z

1

What you need to use is XPath. Assuming that you have your XML document in a DomDocument60 object which we'll call d and you have declared an IXMLDOMNodeList variable called i then use this:

Set i = d.selectNodes("//Attribute[@name='FUNCTIONAL ITEM']")

You can then iterate through the nodes in iand extract the text property from each node.

Here's a fairly minimal program to demonstrate (you need to add a reference to "Microsoft XML, v6.0" via Tools > References if you haven't done so already):

Sub main()

Dim d As DOMDocument60
Dim i As IXMLDOMNodeList
Dim n As IXMLDOMNode

Set d = New DOMDocument60
d.Load 'file path goes here

Debug.Print "*****"
Set i = d.selectNodes("//Attribute[@name='FUNCTIONAL ITEM']")
For Each n In i
    Debug.Print n.Text
Next n
Debug.Print "*****"

End Sub

answered Mar 5, 2011 at 8:59

barrowc

10.7k2 gold badges47 silver badges59 bronze badges

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

Collectives™ on Stack Overflow

Query and parse xml attribute value into XLS using VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related