Making a eBay Finding API Call with Excel VBA

[Dahani]

Hello again!!!


I keep getting this error:

<errormessage xmlns="http://www.ebay.com/marketplace/search/v1/services"><error>
<errorid>(11002)</errorid>

<domain>(Security)</domain>

<severity>(Error)</severity>

<category>(System)</category>

<message>(Authentication failed : Missing required credential for authn method)</message>

<subdomain>(Authentication)</subdomain>

(Missing required credential for authn method)


</error>


</errormessage>





After I run this code:


Sub VBAeBayPostAPICall()




Dim xmlHtp As New MSXML2.XMLHTTP
Dim sURL As String
Dim sEnv As String
Dim XMLDOC As New DOMDocument




<soap:envelope><soap:header>sURL = "http://svcs.ebay.com/services/search/FindingService/v1?OPERATION-NAME=findItemsByKeywords"


sEnv = "(?xml version =""1.0"" encoding=""utf-8""?)"
sEnv = sEnv & "(soap:Envelope xmlns:soap=""http://www.w3.org/2003/05/soap-envelope"" xmlns=""http://www.ebay.com/marketplace/search/v1/services"" )"
sEnv = sEnv & "(soap:Header)"




</soap:header></soap:envelope>sEnv = sEnv & "(/soap:Header)"
sEnv = sEnv & "(soap:Body)"
sEnv = sEnv & "(findItemsByKeywordsRequest)"
sEnv = sEnv & "(keywords)harry potter(/keywords)"
sEnv = sEnv & "(/findItemsByKeywordsRequest)"
sEnv = sEnv & "(/soap:Body)"
sEnv = sEnv & "(/soap:Envelope)"






With xmlHtp


.Open "POST", sURL, False
.setRequestHeader "HOST", ""
.setRequestHeader "SERVICE-NAME", "FindingService"
.setRequestHeader "OPERATION-NAME", "findItemsByKeywords"
.setRequestHeader "GLOBAL-ID", "EBAY-US"
.setRequestHeader "SERVICE-VERSION", "1.13.0"
.setRequestHeader "SECURITY-APPNAME", "MYAPPID" ' I put my APPID here in this field
.setRequestHeader "REQUEST-DATA-FORMAT", "XML"
.setRequestHeader "MESSAGE-PROTOCOL", "SOAP12"
.setRequestHeader "MESSAGE-ENCODING", "UTF-8"


.send sEnv

XMLDOC.LoadXML .responseText
XMLDOC.Save ActiveWorkbook.Path & "C:\Users\Desktop\WebQueryResult2.xml"


End With
End Sub


Why am I getting that error?

 

[Kyle123]

There's a lot wrong here.

1. Why are you trying to use SOAP? That's probably the hardest of the lot.
2. Why are you using parentheses instead of < and >?
3. Why are you passing url parameters as request headers?

Have a read here: Making a Finding API Call

 

[Dahani]

I was using the parentheses because Mr.Excel seems to erase the brackets I submit.

And what if I pass the header parameters in the header section of the envelope?

I tried this and I get the same results:

Dim xmlHtp As New MSXML2.XMLHTTP
Dim sURL As String
Dim sEnv As String
Dim XMLDOC As New DOMDocument




sURL = "http://svcs.ebay.com/services/search/FindingService/v1?OPERATION-NAME=findItemsByKeywords"


sEnv = "(?xml version =""1.0"" encoding=""utf-8""?)"
sEnv = sEnv & "(soap:Envelope xmlns:soap=""http://www.w3.org/2003/05/soap-envelope"" xmlns=""http://www.ebay.com/marketplace/search/v1/services"");"
sEnv = sEnv & "(soap:Header)"


sEnv = sEnv & "(service-name)FindingService(/service-name)"
sEnv = sEnv & "(soapperation-name)findItemsByKeywords(/soapperation-name)"
sEnv = sEnv & "(global-id)EBAY-US(/global-id)"
sEnv = sEnv & "(service-version)1.13.0(/service-version)"
sEnv = sEnv & "(soap:security-appname)MyAppName(/soap:security-appname)"
sEnv = sEnv & "(request-data-format)XML(/request-data-format)"
sEnv = sEnv & "(message-protocol)SOAP12(/message-protocol)"
sEnv = sEnv & "(message-encoding)UTF-8(/message-encoding)"


sEnv = sEnv & "(/soap:Header)"
sEnv = sEnv & "(soap:Body)"
sEnv = sEnv & "(findItemsByKeywordsRequest)"
sEnv = sEnv & "(keywords)harry potter(/keywords)"
sEnv = sEnv & "(/findItemsByKeywordsRequest)"
sEnv = sEnv & "(/soap:Body)"
sEnv = sEnv & "(/soap:Envelope)"






With xmlHtp


.Open "POST", sURL, False
.setRequestHeader "HOST", ""
'.setRequestHeader "SERVICE-NAME", "FindingService"
'.setRequestHeader "OPERATION-NAME", "findItemsByKeywords"
'.setRequestHeader "GLOBAL-ID", "EBAY-US"
'.setRequestHeader "SERVICE-VERSION", "1.13.0"
'.setRequestHeader "SECURITY-APPNAME", "MyAppName"
'.setRequestHeader "REQUEST-DATA-FORMAT", "XML"
'.setRequestHeader "MESSAGE-PROTOCOL", "SOAP12"
'.setRequestHeader "MESSAGE-ENCODING", "UTF-8"


.send sEnv

XMLDOC.LoadXML .responseText
XMLDOC.Save ActiveWorkbook.Path & "C:\Users\Gateway1\Desktop\WebQueryResult2.xml"


End With

 

[Kyle123]

Why are you attached to SOAP, standard XML is much easier?

 

[Dahani]

Did you check my edit?

OK. I will consider changing it all to xml!!!

 

[Kyle123]

PLease use code tags when posting code. Your post is really hard to read

 

[Dahani]

Sorry. This is an updated modification I have done. It uses XML.

<finditemsbykeywordsrequest xmlns="" http:="" www.ebay.com="" marketplace="" search="" v1="" services""="">Sub VBAeBayPostAPICall()



Dim xmlHtp  As New MSXML2.XMLHTTP
Dim sURL    As String
Dim sEnv    As String
Dim XMLDOC  As New DOMDocument




sURL = "http://svcs.ebay.com/services/search/FindingService/v1?OPERATION-NAME=findItemsByKeywords"


sEnv = "[HTML]<?xml version =""1.0"" encoding=""utf-8""?>[/HTML]"
sEnv = sEnv & "[HTML]<findItemsByKeywordsRequest xmlns=""http://www.ebay.com/marketplace/search/v1/services"">[/HTML];"
sEnv = sEnv & "[HTML]<Header>[/HTML]"


sEnv = sEnv & "[HTML]<service-name>[/HTML]FindingService[HTML]</service-name>[/HTML]"
sEnv = sEnv & "[HTML]<operation-name>[/HTML]findItemsByKeywords[HTML]</operation-name>[/HTML]"
sEnv = sEnv & "[HTML]<global-id>[/HTML]EBAY-US[HTML]</global-id>[/HTML]"
sEnv = sEnv & "[HTML]<service-version>[/HTML]1.13.0[HTML]</service-version>[/HTML]"
sEnv = sEnv & "[HTML]<security-appname>[/HTML]MyAppName[HTML]</security-appname>[/HTML]"
sEnv = sEnv & "[HTML]<request-data-format>[/HTML]XML[HTML]</request-data-format>[/HTML]"
'sEnv = sEnv & "[HTML]<message-protocol>[/HTML]SOAP12[HTML]</message-protocol>[/HTML]"
sEnv = sEnv & "[HTML]<message-encoding>[/HTML]UTF-8[HTML]</message-encoding>[/HTML]"


sEnv = sEnv & "[HTML]</Header>[/HTML]"
sEnv = sEnv & "[HTML]<Body>[/HTML]"
sEnv = sEnv & "[HTML]<findItemsByKeywordsRequest>[/HTML]"
sEnv = sEnv & "[HTML]<keywords>[/HTML]harry potter phoenix[HTML]</keywords>[/HTML]"
sEnv = sEnv & "[HTML]</Body>[/HTML]"
sEnv = sEnv & "[HTML]</findItemsByKeywordsRequest>[/HTML]"






With xmlHtp


    .Open "POST", sURL, False
  
    '.setRequestHeader "SERVICE-NAME", "FindingService"
    '.setRequestHeader "OPERATION-NAME", "findItemsByKeywords"
    '.setRequestHeader "GLOBAL-ID", "EBAY-US"
    '.setRequestHeader "SERVICE-VERSION", "1.13.0"
    '.setRequestHeader "SECURITY-APPNAME", "MyAppName"
    '.setRequestHeader "REQUEST-DATA-FORMAT", "XML"
    '.setRequestHeader "MESSAGE-PROTOCOL", "SOAP12"
    '.setRequestHeader "MESSAGE-ENCODING", "UTF-8"






    .send sEnv
    
    XMLDOC.LoadXML .responseText
    XMLDOC.Save ActiveWorkbook.Path & "C:\Users\Gateway1\Desktop\WebQueryResult2.xml"
    
    
End With
End Sub

</finditemsbykeywordsrequest>

 

[Kyle123]

Your code doesn't make sense, there's an example in the link I gave you, quite simply you want:

    Dim req As New XMLHTTP
    Dim doc As New DOMDocument
    
    Dim url As String
    Dim nd As String
    
    nd = "&"
    
    url = "http://svcs.ebay.com/services/search/FindingService/v1?"
    url = url & "OPERATION-NAME=findItemsByKeywords" & nd
    url = url & "SERVICE-VERSION=1.0.0" & nd
    url = url & "SECURITY-APPNAME=YOURAPPID" & nd
    url = url & "RESPONSE-DATA-FORMAT=XML&REST-PAYLOAD" & nd
    url = url & "REST-PAYLOAD&keywords=harry potter phoenix"
    
    
    req.Open "GET", url, False
    req.Send
    
    doc.LoadXML req.ResponseText
    doc.Save "C:\Users\Gateway1\Desktop\WebQueryResult2.xml"