如何使用 VBA 读取 XML 属性到 Excel? [英] How to read XML attributes using VBA to Excel?

问题描述

这是我的代码..

   <?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>

在此我可以使用abc.baseName"读取元素，并使用abc.Text"读取它的值.它给了我结果

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

属性 3物业
属性 FirstUser

Property 3 Property
Property FirstUser

在此我如何将PackageFormatVersion"读为 3?即，我知道某个值是 3，但我怎么知道这个值是什么??

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??

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

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

推荐答案

参考元素的 .Text 属性或 .nodeTypeValue 属性:

Refer either to the element's .Text property or the .nodeTypeValue property :

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

我知道某个值是 3，但我怎么知道这个值是多少?

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

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

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

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

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

这篇关于如何使用 VBA 读取 XML 属性到 Excel?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！