How to read XML attributes using VBA to Excel?

Question

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

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

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

David Zemens · Accepted Answer · 2013-10-03 13:54:34Z

8

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

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

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

enter image description here

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

edited Oct 3, 2013 at 13:54

answered Oct 1, 2013 at 14:03

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

sr1991 Over a year ago

Just one more doubt. Actually my requirement is, I should n't read all the values(not 3,First User,My System). I just need to read "CreatorName" as FirstUser. Any help here??

sr1991 Over a year ago

I edited yourfirst code as: Sub TestXML() Dim Init As Integer Dim xmlDoc As MSXML2.DOMDocument Dim elements As Object Dim el As Variant Init = 5 Set xmlDoc = CreateObject("MSXML2.DOMDocument") xmlDoc.LoadXML ("C:\Users\Su\Documents\Saashu\Testing.xml") Set elements = xmlDoc.getElementsByTagName("DTS:Property") MsgBox "Hi" For Each el In elements MsgBox "Hello" ActiveSheet.Cells(Init, 3)=el.nodeTypeValue Init = Init + 1 Next End Sub But its not working.What might be the prb?

sr1991 Over a year ago

Sorry.. I was unable to post it as a code. Above code is running successfully. But its not showing any o/p. Why??

David Zemens Over a year ago

the LoadXML method takes a string (the full XML string). If you want to load from a document/path, you need to use the Load method, instead: xmlDoc.Load "C:\Users\Su\Documents\Saashu\Testing.xml".

sr1991 · Accepted Answer · 2014-07-02 10:46:53Z

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.

Thanks to David,for helping me in this issue.

Collectives™ on Stack Overflow

How to read XML attributes using VBA to Excel?

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related