Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How I can read all Attributes from a XML with VBA?

Tags:

xml

vba

I got from a Webservice an XML. I declared it as a "DOMDocument". This is my XML. Now I want to read all Attributes named "ZIP".

<?xml version="1.0" encoding="utf-8" ?> 
<Location>
  <Cities>
   <City ZIP="8355">Aadorf</City> 
   <City ZIP="5000">Aarau</City> 
   <City ZIP="5004">Aarau</City> 
   <City ZIP="5032">Aarau Rohr</City> 
   <City ZIP="3270">Aarberg</City> 
   <City ZIP="4663">Aarburg</City> 
   <City ZIP="4912">Aarwangen</City> 
   <City ZIP="8607">Aathal-Seegräben</City> 
   <City ZIP="8522">Aawangen</City> 
   <City ZIP="1657">Abländschen</City> 
   <City ZIP="5646">Abtwil AG</City> 
   <City ZIP="9030">Abtwil SG</City> 
  </Cities>
<Location>

With...

Private Sub Workbook_Open()

    Dim i As Integer
    Dim NumberOfElements As Integer
    Dim City As String
    Dim xmlUrl As String
    Dim xmlDoc As New DOMDocument

    xmlUrl = "http://localhost:62231/dataHandling.asmx/GetAllCities"
    xmlDoc.async = False

    If xmlDoc.Load(xmlUrl) = False Then
        MsgBox ("XML LOAD ERROR")
    Else

        NumberOfElements = xmlDoc.getElementsByTagName("City").Length

        For i = 0 To NumberOfElements - 1

            City = xmlDoc.SelectSingleNode("//Cities/City").Attributes.getNamedItem("ZIP").Text

            City = City & " " & xmlDoc.getElementsByTagName("City").Item(i).Text

            Tabelle2.Cells(i + 3, 1).Value = City

        Next i

    End If

End Sub

I get all Innertextes from the Elements "City". But everytime the same Attribute "8355".

City = xmlDoc.SelectSingleNode("//Cities/City").Attributes.getNamedItem("ZIP").Text

This line should be different, but I don't know how I can loop threw the whole XML to read every single Attrbute.

like image 385
Anja Avatar asked May 14 '13 08:05

Anja


People also ask

What is attr in XML?

The Attr object represents an attribute of an Element object. The allowable values for attributes are usually defined in a DTD. Because the Attr object is also a Node, it inherits the Node object's properties and methods.

Can attributes of XML element have multiple values?

attributes cannot contain multiple values (elements can) attributes cannot contain tree structures (elements can) attributes are not easily expandable (for future changes)

How many attributes are there in XML?

Attribute Types: There are three types of attributes described below: String types Attribute: This type of attribute takes any string literal as a value.

What is XML in VBA?

Excel VBA XML. The full form of XML is eXtensible Markup Language which is much like an HTML file, is designed to store and transport the data from different programs. XML file stores the data which includes the splits and separators. We can create a VBA code by which we can import the data from the XML file into Excel ...


1 Answers

xmlDoc.SelectSingleNode("//Cities/City") always selects the first node. It cannot magically select the next node every time, it would have to read your mind for that.

Private Sub Workbook_Open()
  Dim City As String
  Dim xmlUrl As String
  Dim xmlDoc As New DOMDocument
  Dim n As IXMLDOMNode
  Dim i As Long

  xmlUrl = "http://localhost:62231/dataHandling.asmx/GetAllCities"
  xmlDoc.async = False

  If Not xmlDoc.Load(xmlUrl) Then
    MsgBox "XML LOAD ERROR"
  Else

    For Each n In xmlDoc.selectNodes("//Cities/City")
      City = n.Attributes.getNamedItem("ZIP").Text

      City = City & " " & n.Text

      Tabelle2.Cells(i + 3, 1).Value = City
      i = i + 1
    Next

  End If

End Sub
like image 177
GSerg Avatar answered Nov 03 '22 02:11

GSerg