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

问题描述

下面是我的code ..

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>

在此我可以读取使用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.

推荐答案

请参阅要么元素的。文本属性或 .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，但该值是我怎么会知道？

您可以查看在本地窗口中的对象，并检查其属性：

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在Excel读取XML属性？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！