title: Getting data from XML doc using Excel 2010 VBA, node length returns 0 when node is present
body: <p>I'm trying to develop a VBA script that takes the ISSN number of journal titles from an excel 2010 spreadsheet, and query the Sherpa/Romeo web service (a copyright policy database). It then populates the spreadsheet with self-archiving policy for each title.</p>
  
  <p>While it works for most of the rows in my test spreadsheet, I can't seem to get the information for a bunch of journal titles. Coincidentally they're all of one publisher, I'm not sure it'd make any difference because it's XML. I think there must be something wrong with my code but I cannot determine why it thinks "resp.getElementsByTagName("journal").Length" as 0 when the journal tag is present. <a href="http://sherpa.ac.uk/romeo/api29.php?ak=PQ2mALMLxMs&amp;issn=0387-7604" rel="nofollow">Here's the link to the XML result for an ISSN that didn't work as expected</a>.  I tried to compare it with the result of the ones that worked, I can't see any difference. </p>
  
  <p>I'm new to all this, any tips would be greatly appreciated. I hope someone can help :)</p>
  
  <p>Here's a list of ISSNs, I put ** next to the ones that have data within the "journal" tag but didn't get picked up. </p>
  
  <pre><code>0387-7604 **
  1864-7782
  1932-6203
  0927-5568
  0302-9743
  1687-725X
  0302-9743
  0387-7604
  0302-9743
  1871-5192 **
  1448-0220
  1550-2783
  1466-853X **
  1438-4639 **
  1642-431X
  0142-0615
  0096-140X
  1746-1391
  1096-3480
  1065-9471
  0260-2938
  1055-9965
  1084-8045 **
  </code></pre>
  
  <p>My code:
  </p>
  
  <pre><code>Private Sub btnCopyright_Click()
  Dim wsISSN As Worksheet
  Set wsISSN = ThisWorkbook.Sheets("ISSN_2")
  Dim ISSN As String
  Dim URL As String
  Dim baseURL As String
  baseURL = "http://www.sherpa.ac.uk/romeo/api29.php?" 'I removed my API key' 
  
  Dim i As Integer
  Dim Last As Integer
  
  i = 1
  Last = wsISSN.Range("D6000").End(xlUp).Row
  If Last = 1 Then Exit Sub
  
  For i = 2 To Last 'second row to last row'
      Dim req As New xmlhttp
      ISSN = Cells(i, 4).Value
  
      If ISSN = "Invalid" Or ISSN = "" Then
          GoTo skipISSN
      End If
  
      URL = baseURL &amp; "&amp;issn=" &amp; ISSN
      req.Open "GET", URL, False
      req.Send
      Debug.Print (req.ResponseText)
  
      Dim resp As New DOMDocument
      resp.LoadXML req.ResponseText
      Debug.Print (resp.getElementsByTagName("journal").Length)
  
      If resp.getElementsByTagName("journal").Length = 0 Then
          Cells(i, 5).Value = "unknown"
          Cells(i, 6).Value = "unknown"
          Cells(i, 7).Value = "unknown"
          Cells(i, 8).Value = "unknown"
          Cells(i, 9).Value = "unknown"
          Cells(i, 10).Value = "unknown"
          Cells(i, 11).Value = "unknown"
          GoTo skipISSN
      End If
  
          Dim preprint As String
          Dim preRest As String
  
          Debug.Print (resp.getElementsByTagName("prearchiving").Length)
          If resp.getElementsByTagName("prearchiving").Length = 0 Then
              Cells(i, 5).Value = "-"
          Else
              preprint = resp.SelectSingleNode("//preprints/prearchiving").Text
              If preprint = "can" Then
                  Cells(i, 5).Value = "Yes"
              ElseIf preprint = "restricted" Then
                  Cells(i, 5).Value = "restricted"
              Else
                  Cells(i, 5).Value = "unknown"
              End If
          End If
  
          'any restrictions for archiving preprint?'
          Debug.Print (resp.getElementsByTagName("prerestrictions").Length)
          If resp.getElementsByTagName("prerestrictions").Length = 0 Then
              Cells(i, 6).Value = "-"
          Else
              preRest = resp.SelectSingleNode("//preprints/prerestrictions").Text
              Debug.Print (preRest)
              If preRest &lt;&gt; "" Then
                  Cells(i, 6).Value = preRest
              Else
                  Cells(i, 6).Value = "none"
              End If
          End If
  
          'is postprint allowed?'
          Dim postprint As String
          Dim postRest As String
          Debug.Print (resp.getElementsByTagName("postarchiving").Length)
          If resp.getElementsByTagName("postarchiving").Length = 0 Then
              Cells(i, 7).Value = "-"
          Else
              postprint = resp.SelectSingleNode("//postprints/postarchiving").Text
              If postprint = "can" Then
                  Cells(i, 7).Value = "Yes"
              ElseIf postprint = "restricted" Then
                  Cells(i, 7).Value = "restricted"
              Else
                  Cells(i, 7).Value = "unknown"
              End If
          End If
  
          'any restrictions for archiving postprint?'
          Debug.Print (resp.getElementsByTagName("postrestrictions").Length)
          If resp.getElementsByTagName("postrestrictions").Length = 0 Then
              Cells(i, 8).Value = "-"
          Else
              postRest = resp.SelectSingleNode("//postprints/postrestrictions").Text
              Debug.Print (postRest)
              If postRest &lt;&gt; "" Then
                  Cells(i, 8).Value = postRest
              Else
                  Cells(i, 8).Value = "none"
              End If
          End If
  
          'is publishers version allowed?'
  
          Dim allCond As String
  
          Debug.Print (resp.getElementsByTagName("condition").Length)
          If resp.getElementsByTagName("condition").Length = 0 Then
              Cells(i, 9).Value = "-"
              Cells(i, 10).Value = "-"
              Cells(i, 11).Value = "-"
          Else
              allCond = resp.SelectSingleNode("//conditions").Text
              Debug.Print (allCond)
                  If InStr(allCond, "embargo") &gt; 0 Then
                      Cells(i, 9).Value = "maybe"
                      Cells(i, 10).Value = "yes"
                      Cells(i, 11).Value = allCond
                  ElseIf InStr(allCond, "Publisher's version/PDF may be used") = 0 Then
                      Cells(i, 9).Value = "no"
                      Cells(i, 10).Value = "-"
                      Cells(i, 11).Value = allCond
                  ElseIf InStr(allCond, "Publisher's version/PDF may be used") &gt; 0 Then
                      Cells(i, 9).Value = "yes"
                      Cells(i, 10).Value = "-"
                      Cells(i, 11).Value = allCond
                  ElseIf allCond = "" Then
                      Cells(i, 9).Value = "-"
                      Cells(i, 10).Value = "-"
                      Cells(i, 11).Value = "-"
                  Else
                      Cells(i, 9).Value = "-"
                      Cells(i, 10).Value = "-"
                      Cells(i, 11).Value = allCond
                  End If
              End If
  
  skipISSN:
  
      Next i
  End Sub
  </code></pre>