Excel VBA XML - Document not Loading

Question

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?

SWa · Accepted Answer · 2015-03-13 17:08:31Z

2

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

answered Mar 13, 2015 at 17:08

SWa

4,36325 silver badges42 bronze badges

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

Collectives™ on Stack Overflow

Excel VBA XML - Document not Loading

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related