Parsing out XML response to get record Id

[lizardbreath]

Hi There,

I have code that I created that sends a post to a CRM to create a record. After sending the post, I need to consume data from the basic XML Response which is where I am stuck on.

Here is an example of the code to post the data, I had to strip out the actual URL due to security reasons(Bolded section).


Sub CreateRecord()

With CreateObject("WinHttp.WinHttpRequest.5.1")
.Open "POST", "FakeWebsite", False
.SetRequestHeader "content-type", "application/x-www-form-urlencoded; charset=UTF-8"
.SetRequestHeader "user-agent", "user-agent: Mozilla/5.0 (Windows NT 6.1; Win64; x64)"
.Send "First_name=Bob"
MsgBox .ResponseText
End With

End Sub


When I send this request, it will respond with an XML that looks like this below. I need to extract out the LeadId value of "6881061" and the result value of "Success" and put it into two difference cells F2 & G2

<?xml version="1.0" encoding="UTF-8"?>
<ImportResults>
<ImportResult refId="" leadId="6881061" result="Success" message="" />
</ImportResults>

 

[lizardbreath]

Hi There,

Can somebody take a stab at this or at least point me in the right direction? Where I am struggling the most is getting specifically to the Lead Id portion in the XML response and parsing that out.

 

[Peter_SSs]

If you incorporate this in place of your MsgBox line what happens?

Dim s As String
Dim LeadID As String
s = .ResponseText
LeadID = Split(Split(s, "leadid=", -1, 1)(1), """")(1)
MsgBox LeadID

 

[lizardbreath]

If you incorporate this in place of your MsgBox line what happens?

Dim s As String
Dim LeadID As String
s = .ResponseText
LeadID = Split(Split(s, "leadid=", -1, 1)(1), """")(1)
MsgBox LeadID

That did the trick! Very creative. Thank you for the assist!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

So, you could skip s altogether and just go straight to ..

Dim LeadID As String
LeadID = Split(Split(.ResponseText, "leadid=", -1, 1)(1), """")(1)

 

[Dan_W]

On the off-chance it might be helpful to someone with a similar need, I set out below another approach using the FILTERXML function. Assuming that the XML text is in cell B2:
[F2] =FILTERXML(B2,"//ImportResult/@leadId")
[G2] =FILTERXML(B2,"//ImportResult/@result")