如何使用 VBA 到 Excel 读取 XML 属性？

How to read XML attributes using VBA to Excel?

提问by SR1991

Here is my code..

这是我的代码..

   <?xml version="1.0" ?> 
   <DTS:Executable xmlns:DTS="www.microsoft.com/abc" DTS:ExecutableType="xyz">
       <DTS:Property DTS:Name="PackageFormatVersion">3</DTS:Property> 
       <DTS:Property DTS:Name="VersionComments" /> 
       <DTS:Property DTS:Name="CreatorName">FirstUser</DTS:Property> 
       <DTS:Property DTS:Name="CreatorComputerName">MySystem</DTS:Property>
   </DTS:Executable>

In this I am able to read Elements using "abc.baseName" and its value using "abc.Text". It gives me result as

在这里，我可以使用“abc.baseName”读取元素，并使用“abc.Text”读取它的值。它给了我结果

Property 3 Property
Property FirstUser

属性 3 属性
属性 FirstUser

In this how can I read "PackageFormatVersion" as 3? i.e., I know some value is 3 but what that value is how could I know??

在这种情况下，我如何将“PackageFormatVersion”读为 3？即，我知道某个值是 3 但我怎么知道这个值是多少？？

I mean I have to select which attribute I want to read.

我的意思是我必须选择我想要读取的属性。

回答by David Zemens

Refer either to the element's .Textproperty or the .nodeTypeValueproperty :

请参阅元素的.Text属性或.nodeTypeValue属性：

Sub TestXML()
Dim xmlDoc As Object 'Or enable reference to Microsoft XML 6.0 and use: MSXML2.DOMDocument
Dim elements As Object
Dim el As Variant
Dim xml$
xml = "<?xml version=""1.0"" ?>"
xml = xml & "<DTS:Executable xmlns:DTS=""www.microsoft.com/abc"" DTS:ExecutableType=""xyz"">"
xml = xml & "<DTS:Property DTS:Name=""PackageFormatVersion"">3</DTS:Property>"
xml = xml & "<DTS:Property DTS:Name=""VersionComments"" />"
xml = xml & "<DTS:Property DTS:Name=""CreatorName"">FirstUser</DTS:Property>"
xml = xml & "<DTS:Property DTS:Name=""CreatorComputerName"">MySystem</DTS:Property>"
xml = xml & "</DTS:Executable>"

Set xmlDoc = CreateObject("MSXML2.DOMDocument")
'## Use the LoadXML method to load a known XML string
xmlDoc.LoadXML xml
'## OR use the Load method to load xml string from a file location:
'xmlDoc.Load "C:\my_xml_filename.xml"

'## Get the elements matching the tag:
Set elements = xmlDoc.getElementsByTagName("DTS:Property")
'## Iterate over the elements and print their Text property
For Each el In elements
    Debug.Print el.Text
    '## Alternatively:
    'Debug.Print el.nodeTypeValue
Next

End Sub

I know some value is 3 but what that value is how could I know??

我知道有些值是 3 但我怎么知道这个值是多少？？

You can review the objects in the Locals window, and examine their properties:

您可以在 Locals 窗口中查看对象，并检查它们的属性：

Here is an alternative, which seems clunkier to me than using the GetElementsByTagNamebut if you need to traverse the document, you could use something like this:

这是一种替代方法，对我来说似乎比使用GetElementsByTagName更笨拙，但是如果您需要遍历文档，则可以使用以下内容：

Sub TestXML2()
Dim xmlDoc As MSXML2.DOMDocument
Dim xmlNodes As MSXML2.IXMLDOMNodeList
Dim xNode As MSXML2.IXMLDOMNode
Dim cNode As MSXML2.IXMLDOMNode
Dim el As Variant
Dim xml$
xml = "<?xml version=""1.0"" ?>"
xml = xml & "<DTS:Executable xmlns:DTS=""www.microsoft.com/abc"" DTS:ExecutableType=""xyz"">"
xml = xml & "<DTS:Property DTS:Name=""PackageFormatVersion"">3</DTS:Property>"
xml = xml & "<DTS:Property DTS:Name=""VersionComments"" />"
xml = xml & "<DTS:Property DTS:Name=""CreatorName"">FirstUser</DTS:Property>"
xml = xml & "<DTS:Property DTS:Name=""CreatorComputerName"">MySystem</DTS:Property>"
xml = xml & "</DTS:Executable>"

Set xmlDoc = CreateObject("MSXML2.DOMDocument")
'## Use the LoadXML method to load a known XML string
xmlDoc.LoadXML xml
'## OR use the Load method to load xml string from a file location:
'xmlDoc.Load "C:\my_xml_filename.xml"

'## Get the elements matching the tag:
Set xmlNodes = xmlDoc.ChildNodes
'## Iterate over the elements and print their Text property
For Each xNode In xmlDoc.ChildNodes
    If xNode.NodeType = 1 Then  ' only look at type=NODE_ELEMENT
        For Each cNode In xNode.ChildNodes
            Debug.Print cNode.nodeTypedValue
            Debug.Print cNode.Text
        Next
    End If
Next

End Sub

回答by SR1991

Sub TestXML()
Set Reference to Microsoft XML 6.0
Dim Init As Integer
Dim xmlDoc As MSXML2.DOMDocument
Dim elements As Object
Dim el As Variant
Dim Prop As String
Dim NumberOfElements As Integer
Dim n As IXMLDOMNode
Init = 5

Set xmlDoc = CreateObject("MSXML2.DOMDocument")

xmlDoc.Load ("C:\Users\Saashu\Testing.xml")

Set elements = xmlDoc.getElementsByTagName("DTS:Property")

Prop = xmlDoc.SelectSingleNode("//DTS:Property").Attributes.getNamedItem("DTS:Name").Text

NumberOfElements = xmlDoc.getElementsByTagName("DTS:Property").Length

For Each n In xmlDoc.SelectNodes("//DTS:Property")
   Prop = n.Attributes.getNamedItem("DTS:Name").Text
   Prop = Prop & " :: " & n.Text
   ActiveSheet.Cells(Init, 9).Value = Prop
   Init = Init + 1
Next
End Sub

This code still needs refinement as my requirement is to display only some of those attributes like CreatorName and CreatorComputerName,not all.

这段代码仍然需要改进，因为我的要求是只显示一些属性，如 CreatorName 和 CreatorComputerName，而不是全部。

Thanks to David,for helping me in this issue.

感谢大卫在这个问题上帮助我。