如何使用VBA到Excel读取XML属性? [英] 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读取Elements,其值使用abc。文本。
它给我的结果为
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
property:
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屋!