CODEWITHSUNDEEP
xmlvbaexcel
rryanp
rryanp

Reputation: 1027

Excel VBA XML - Document not Loading

I am trying to create a table of data in Excel from an API XML data pull. Prior to the code below I make the API call, and the first line below presents a msgbox with the XML data (so that part appears to be working).

MsgBox httpReq.responseText
Dim xDoc As MSXML2.DOMDocument
Set xDoc = New MSXML2.DOMDocument
xDoc.async = False
xDoc.validateOnParse = False
If xDoc.Load(httpReq.responseText) Then
   'Do something
Else
   MsgBox "Document failed to load."
End If

Unfortunately I then keep hitting the Else side of that If/Then (i.e. the document is not loading). Do I have that syntax wrong? In my research efforts, it seems the parameter of the .Load() is usually a file or URL--can I not use the httpReq.responseText in place of a file name or URL?

Upvotes: 1

Views: 2435

Answers (1)

SWa
SWa

Reputation: 4363

You want the LoadXML method:

If xDoc.LoadXML(httpReq.responseText) Then
   MsgBox "Do something"
Else
   MsgBox "Document failed to load."
End If

Working example:

Sub test()

    Dim httpReq As Object
    Set httpReq = CreateObject("winhttp.winhttprequest.5.1")

    httpReq.Open "GET", "http://xmlgw.companieshouse.gov.uk/examples/namesearch_req.xml", False
    httpReq.send

    Dim xDoc As MSXML2.DOMDocument
    Set xDoc = New MSXML2.DOMDocument

    xDoc.async = False
    xDoc.validateOnParse = False

    If xDoc.LoadXML(httpReq.responseText) Then
       MsgBox "Do something"
    Else
       MsgBox "Document failed to load."
    End If

End Sub

Upvotes: 2

Related Questions