0

I am trying to parse an api URL using VBA. However, cannot figure out why I get no output with the below code. No error message either. If I use another XML file it seems to work, however..

Any tips from someone of code improvement? THanks!

Sub testing()
 
   Dim http As New XMLHTTP60
    Dim xmlDoc As Object, post As Object

    With http
        .Open "GET", "https://www.researchonline.se/api/MacroAdmin/GetForecast?name=policy&start=Mon%20Sep%2014%202020&end=Tue%20Sep%2014%202021", False
        .send
        Set xmlDoc = CreateObject("MSXML2.DOMDocument")
        xmlDoc.loadXML .responseXML.XML
    End With

     For Each post In xmlDoc.getElementsByTagName("dateStarts")
        r = r + 1:
        
        Cells(r, 1) = post.getElementsByTagName("d3p1:dateTime").Text
       
        MsgBox post.getElementsByTagName("d3p1:dateTime").Text
        
    Next post
    
End Sub
Gustav
  • 53,498
  • 7
  • 29
  • 55
Jenny Nygren
  • 11
  • 1
  • It looks like you need (to look up) a root element in the retrieved XML text. Perhaps: ArrayOfSHBForecast. – Gustav Sep 21 '20 at 12:45
  • The http.responseXML.XML is empty. Maybe you can try to parse http.responseText. – Zer0Kelvin Sep 21 '20 at 13:08
  • I'd guess you need to add the namespace before trying to access the elements which use that namespace. https://stackoverflow.com/questions/1199739/vbscript-msxml-and-namespaces – Tim Williams Sep 21 '20 at 15:33
  • On review - when called from VBA, that URL returns JSON, not XML. – Tim Williams Sep 21 '20 at 16:05
  • Thanks for quick feedback everyone! Just two follow-up questions: 1) How can I tell that it is a JSON and not XML? 2) How would I amend the coding to adjust for JSON (or is there a good previous thread on this?) Thanks! – Jenny Nygren Sep 21 '20 at 17:03
  • 1)I just looked at the `responseText` property 2)This is the typical library used in VBA for parsing JSON: https://github.com/VBA-tools/VBA-JSON. Previous posts on this topic: https://www.google.com/search?q=vba+parse+json+site:stackoverflow.com – Tim Williams Sep 21 '20 at 18:10

0 Answers0