question

giftsmade avatar image
0 Likes"
giftsmade asked ·

Accessing eBay API using VBA in Excel

Hi All, I've searched both the forum and google but haven't had any luck with this. I'm trying to find information and examples of accessing the eBay API using VBA. Essentially I want an easy way to automatically import data in to an excel spreadsheet (recent order information). Would anyone be able to assist please. Thank you in advance.
apiebay-apiebayexcelvba
10 |600 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

giftsmade avatar image
1 Like"
giftsmade answered ·
Thank you very much for your answer @dufflepod. it has been extremely helpful. My apologies for not thanking you sooner.
· Share
10 |600 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

esquire*attire avatar image
1 Like"
esquire*attire answered ·
Thank you , easy and excellent ! works just fine
· Share
10 |600 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

helios825 avatar image
1 Like"
helios825 answered ·
Thanks for posting this @dufflepod ! (I'll link to this thread from helios825.org .)
· Share
10 |600 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dufflepod avatar image
1 Like"
dufflepod answered ·
@helios825 Here's a real quick 'n' dirty example I did yesterday with all error handling removed for brevity, but it should give you an idea. I'm not sure I've used the most apt (or up-to-date?) MSXML2 or WinHttp objects but you should be able to google/check that. Regards. Const body As String = " " & _ " " & _ " " & _ " auth-token-here " & _ " " & _ " Coarse " & _ " 0 " & _ " some-user-name " & _ " 2015-05-30T00:00:00.000Z " & _ " 2015-06-10T00:00:00.000Z " & _ " en_GB " & _ " 100 1 " & _ " ItemArray.Item.Title " & _ " ItemArray.Item.SellingStatus.CurrentPrice " & _ " " Sub ItemsForSale() Set objHTTP = New WinHttp.WinHttpRequest URL = " https://api.ebay.com/ws/api.dll"; objHTTP.Open "POST", URL, False objHTTP.setRequestHeader "X-EBAY-API-DEV-NAME", "dev-name-gose-here" objHTTP.setRequestHeader "X-EBAY-API-CERT-NAME", "cert-name-goes-here" objHTTP.setRequestHeader "X-EBAY-API-CALL-NAME", "GetSellerList" objHTTP.setRequestHeader "X-EBAY-API-SITEID", "3" objHTTP.setRequestHeader "X-EBAY-API-REQUEST-Encoding", "XML" objHTTP.setRequestHeader "X-EBAY-API-COMPATIBILITY-LEVEL", "923" objHTTP.send (body) Set objXML = New MSXML2.DOMDocument objXML.LoadXML(objHTTP.ResponseText) Dim xItemList As IXMLDOMNodeList Set xItemList = objXML.SelectNodes("//Item") Row = 3 Dim xItem As IXMLDOMNode For Each xItem In xItemList Worksheets("Sheet1").Cells(Row, 2) = xItem.SelectNodes("Title").Item(0).Text Worksheets("Sheet1").Cells(Row, 4) = xItem.SelectNodes("SellingStatus/CurrentPrice").Item(0).Text Row = Row + 1 Next Set objHTTP = Nothing Set objXML = Nothing End Sub
· Share
10 |600 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dufflepod avatar image
0 Likes"
dufflepod answered ·
A couple of things spring to mind. It looks like you receive a valid response from the API. Are your search paramaters returning an empty list? (most likely answer) Double check your dev-id, app-id etc. It sounds like you may have these in a bit of a muddle. The API call is working with no error so that's all I can think of. Make sure the user you're searching for actually has items for sale within the time preiod you specify. Check ther number of items returns after the XPATH search with something like.... Worksheets("Sheet1").Cells(1, 1) = xItemList.Length Good luck.
· Share
10 |600 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ve2_ehf avatar image
0 Likes"
ve2_ehf answered ·
Hello, This isn't an answer but a question for @dufflepod. I tried your code I registered with ebay as a programmer, created a production application keyset and a user token I put the user token at auth-token-here Put my App ID in place of "dev-name-gose-here" My cert id in place of "cert-name-goes-here" Tried to run it (Added MS XML 6 reference and MS winhttp reference) and I got no output I added a line Debug.Print objHTTP.ResponseText To see what is returned and got this 2016-11-14T00:51:46.405ZSuccess967E967_INTL_APISELLING_17965958_R1 So, that Success is telling me it worked but there's no other content in that response ! I then tried to use another seller name, tried two but got the same results I tried putting my dev id instead of app id in place of "dev-name-gose-here" but still get the same thing. I am kind of stuck, any suggestion ? thanks !
· Share
10 |600 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dufflepod avatar image
0 Likes"
dufflepod answered ·
@giftsmade See my earlier post.
· Share
10 |600 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

helios825 avatar image
0 Likes"
helios825 answered ·
I don't know the full answer to your question, but I'm pretty sure it's somehow possible. You may want to figure it out 1 piece at a time. I know in the past I've built URLs into Excel. And many eBay's APIs are accessible via a REST/GET based URL structure. So, I'm pretty sure you can get as far as retrieving data from the API into Excel. Then you'd need to parse it. Perhaps the JSON format would be easier than XML on that. If you're a whiz with Excel functions, that part should be easy. And of course VBA can do much more. One avenue you could aim for is to hunt around online for sample Excel spreadsheets that retrieve data remotely for other services altogether, and then adjust for the eBay API. I bet a $5 gig at Fiverr.com might get you set for much of the above, if not all. Always of course weigh your time spent trying to solve problems.
· Share
10 |600 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.