vba 和 XML 只需要从某些节点中提取属性值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28304042/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
vba and XML only need to extract attribute values from certain nodes
提问by David.Warwick
I am new to XML. I have a requirement now where I must extract data from a bunch of XML files. I cannot show the entire XML file, but hopefully someone can show me how to do this. I have read through a lot of forums and blogs and I am just so confused. Here is the XML.
我是 XML 的新手。我现在有一个要求，我必须从一堆 XML 文件中提取数据。我无法显示整个 XML 文件，但希望有人可以告诉我如何执行此操作。我已经阅读了很多论坛和博客，但我很困惑。这是 XML。
<?xml version="1.0"?>
<docpms>
<distrib>
<mrc>
<toolsetc>
<misc>
<seqlist>
<item spinno="02">
<paratext>Gloves</paratext>
</item>
<item spinno="022">
<paratext>Pail</paratext>
</item>
<item spinno="03">
<paratext>Lanyard</paratext>
</item>
<item spinno="07">
<paratext>Goggles</paratext>
</item>So, I want to loop through the item nodes that have a spinno attribute and get the text value of the attribute. There are many other item nodes that do not have this attribute, so I think I need to use something like the following VBA.
因此，我想遍历具有 spinno 属性的项目节点并获取该属性的文本值。还有很多其他的项目节点没有这个属性，所以我想我需要使用类似下面的VBA。
Option Compare Database
Option Explicit
Dim xDoc As MSXML2.DOMDocument60
Dim strpath As String
Dim spinAttribute As MSXML2.IXMLDOMAttribute
Public Sub LoadDocument()
strpath = Environ("USERPROFILE") & "\Documents\XMLPrivate Sub Workbook_Open()
Dim spin As String
Dim xmlUrl As String
Dim xmlDoc As New DOMDocument
Dim n As IXMLDOMNode
Dim nText As String
xmlUrl = Environ("USERPROFILE") & "\Documents\Viewer\XMLDim iNodes, n
Set xDoc = New MSXML2.DOMDocument60
xDoc.validateOnParse = True
xDoc.LoadXML Sheet1.Range("a1").Value 'my testing
xDoc.setProperty "SelectionLanguage", "XPath" '<<<######
'I simplified your XML a bit, so adjust here....
Set iNodes = xDoc.SelectNodes("/docpms/seqlist/item[@spinno]")
Debug.Print iNodes.Length
For Each n In iNodes
Debug.Print n.Text 'all itens with the spinno attribute
Next n
##代码##1C.xml"
xmlDoc.async = False
If Not xmlDoc.Load(xmlUrl) Then
MsgBox "XML LOAD ERROR"
Else
For Each n In xmlDoc.selectNodes("//misc/seqlist/item")
spin = n.Attributes.getNamedItem("spinno").Text
nText = n.Text
Next
End If
End Sub
\"
Set xDoc = New MSXML2.DOMDocument60
xDoc.validateOnParse = False
If (xDoc.Load(strpath & "1C.xml")) Then
' The document loaded successfully.
' Now do something intersting.
DisplayNode xDoc.childNodes, 0
Else
' The document failed to load.
' See the previous listing for error information.
End If
End Sub
Public Sub DisplayNode(ByRef Nodes As MSXML2.IXMLDOMNodeList, _
ByVal Indent As Integer)
Dim strSpin As String
Dim xNode As MSXML2.IXMLDOMNode
Set xNode = xDoc.selectNodes("/docpms/mrc/toolsetc/misc/seqlist/item")
For Each xNode In Nodes
Set spinAttribute = xNode.Attributes.getNamedItem("spinno")
strSpin = spinAttribute.Text
Next xNode
End SubBut when I am stepping through the code, I get a type mismatch on Set xNode = xDoc.selectNodes("/docpms/mrc/toolsetc/misc/seqlist/item") for starters and I am not sure if my for each loop will do what I want. Any help will be appreciated.
但是，当我逐步执行代码时，对于初学者，我在 Set xNode = xDoc.selectNodes("/docpms/mrc/toolsetc/misc/seqlist/item") 上遇到类型不匹配，我不确定我的 for 每个循环是否会做我想做的。任何帮助将不胜感激。
Thanks,
谢谢，
David
大卫
回答by David.Warwick
I found the answer in another forum post on Stack Overflow. Sorry I didn't see this earlier today. Here is the answer How I can read all Attributes from a XML with VBA?and here is my final code.
我在 Stack Overflow 上的另一篇论坛帖子中找到了答案。抱歉我今天早些时候没有看到这个。这是我如何使用 VBA 从 XML 中读取所有属性的答案？这是我的最终代码。
This searches for and grabs all of the attribute values that I need.
这将搜索并获取我需要的所有属性值。
Thanks,
谢谢，
David
大卫
回答by Tim Williams
You can use XPath to select only nodes which have that attribute:
您可以使用 XPath 仅选择具有该属性的节点：
##代码##