使用 VBA 遍历 XML
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20020990/
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
Looping through XML using VBA
提问by Jiminy Cricket
I'm trying to loop through the following simple XML using VBA, with the ultimate goal to be able to easily extract the data in sequence.
我正在尝试使用 VBA 遍历以下简单的 XML，最终目标是能够轻松地按顺序提取数据。
<?xml version="1.0"?>
<PMRData>
<Staff StaffName="Person 1">
<Openings>1.1</Openings>
<Closures>1.11</Closures>
</Staff>
<Staff StaffName="Person 2">
<Openings>1.2</Openings>
<Closures>1.22</Closures>
</Staff>
<Staff StaffName="Person 3">
<Openings>1.3</Openings>
<Closures>1.33</Closures>
</Staff>
</PMRData>
My code so far manages to get the data into the Immediate Window but not in the order I need it. It should be: Staff Name Person1 Openings 1.1 Closures 1.11 Staff Name Person 2 Openings 2.2 Closures 2.22 etc.
到目前为止，我的代码设法将数据放入立即窗口，但不是按照我需要的顺序。应该是：员工姓名 Person1 Openings 1.1 Closures 1.11 员工姓名 Person 2 Openings 2.2 Closures 2.22 等等。
Meaning i need to make my recursive function specific, rather than looping for all nodes. Any help would be greatly appreciated! This is what I have so far...
这意味着我需要使我的递归函数特定，而不是循环所有节点。任何帮助将不胜感激！这是我到目前为止...
Dim xDoc As DOMDocument
Set xDoc = New DOMDocument
Dim xNode As IXMLDOMNode
Dim xElem As IXMLDOMElement
Dim xElemCount As Integer
Dim xSub As IXMLDOMElement
Dim Nodes As IXMLDOMNodeList
Set xElem = xDoc.SelectSingleNode("//PMRData")
Range("a1").Select
xElemCount = xElem.ChildNodes.Length
Debug.Print "xElem has " & xElemCount & " Nodes"
For Each xSub In xElem.ChildNodes
If xSub.Attributes.Length > 0 Then
For i = 0 To xSub.Attributes.Length - 1
Debug.Print xSub.Attributes(i).nodeName & " - " & xSub.Attributes(i).NodeValue
ActiveCell.Value = xSub.Attributes(i).nodeName
ActiveCell.Offset(0, 1).Value = xSub.Attributes(i).NodeValue
ActiveCell.Offset(1, 0).Select
Next i
End If
Next xSub
Set Nodes = xElem.SelectNodes("//PMRData")
For Each xNode In Nodes
DisplayNode xNode
Next xNode
End Sub
Public Sub DisplayNode(ByRef xNode As IXMLDOMNode)
Dim xNode2 As IXMLDOMNode
If xNode.NodeType = NODE_TEXT Then
Debug.Print "xNode = " & xNode.ParentNode.nodeName
Debug.Print "xNodeValue = " & xNode.NodeValue
End If
If xNode.HasChildNodes Then
For Each xNode2 In xNode.ChildNodes
DisplayNode xNode2
Next xNode2
End If
End Sub
回答by dee
Option Explicit
Private Const xml As String = "<PMRData>" & _
"<Staff StaffName='Person 1'>" & _
"<Openings>1.1</Openings>" & _
"<Closures>1.11</Closures>" & _
"</Staff>" & _
"<Staff StaffName='Person 2'>" & _
"<Openings>1.2</Openings>" & _
"<Closures>1.22</Closures>" & _
"</Staff>" & _
"<Staff StaffName='Person 3'>" & _
"<Openings>1.3</Openings>" & _
"<Closures>1.33</Closures>" & _
"</Staff>" & _
"</PMRData>"
Sub test()
Dim xDoc As DOMDocument
Set xDoc = New DOMDocument
If Not xDoc.LoadXML(xml) Then
Err.Raise xDoc.parseError.ErrorCode, , xDoc.parseError.reason
End If
Dim list As IXMLDOMNodeList
Set list = xDoc.SelectNodes("//PMRData/Staff")
Dim attr As IXMLDOMAttribute
Dim node As IXMLDOMNode
Dim childNode As IXMLDOMNode
For Each node In list
Set attr = node.Attributes.getNamedItem("StaffName")
If (Not attr Is Nothing) Then
Debug.Print attr.BaseName & " " & attr.Text
End If
If (node.HasChildNodes) Then
For Each childNode In node.ChildNodes
Debug.Print childNode.BaseName & " " & childNode.Text
Next childNode
End If
Next node
End Sub
Output:
输出：
StaffName Person 1
Openings 1.1
Closures 1.11
StaffName Person 2
Openings 1.2
Closures 1.22
StaffName Person 3
Openings 1.3
Closures 1.33
