Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

excel - Query and parse xml attribute value into XLS using VBA

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...