使用VBA将xml属性值查询并解析为XLS [英] Query and parse xml attribute value into XLS using VBA
问题描述
我正在尝试在范围 B2
中打开/加载在Excel工作表中指定的XML文件。然后,搜索 name = FUNCTIONAL_ITEM
的XML属性列表,并在>
之后获取所有属性值。
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 ">
.
在以下示例中,我想提取出 8
, 9
和 10
。
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.
推荐答案
你需要使用的是XPath,假设你有我们将调用 d
的 DomDocument60 对象中的XML文档,并声明了一个
IXMLDOMNodeList
变量调用 i
然后使用:
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:
设置我= d.selectNodes(// Attribute [@ name ='FUNCTIONAL ITEM'])
然后,您可以遍历节点 i
并从每个节点提取文本
属性。
You can then iterate through the nodes in i
and extract the text
property from each node.
这是一个相当微乎其微的程序来演示(如果你还没有这样做,你需要通过工具>参考文献添加对Microsoft XML,v6.0的引用):
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
这篇关于使用VBA将xml属性值查询并解析为XLS的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!