Accessing eBay API using VBA in Excel
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.
8 Replies· Add your reply
Answer by dufflepod · May 31, 2015 at 11:03 AM
@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.
Const body As String = "<?xml version=""1.0"" encoding=""utf-8""?>" & _ "<GetSellerListRequest xmlns=""urn:ebay:apis:eBLBaseComponents"">" & _ "<RequesterCredentials>" & _ "<eBayAuthToken>auth-token-here</eBayAuthToken>" & _ "</RequesterCredentials>" & _ "<GranularityLevel>Coarse</GranularityLevel>" & _ "<Sort>0</Sort>" & _ "<UserID>some-user-name</UserID>" & _ "<EndTimeFrom>2015-05-30T00:00:00.000Z</EndTimeFrom>" & _ "<EndTimeTo>2015-06-10T00:00:00.000Z</EndTimeTo>" & _ "<ErrorLanguage>en_GB</ErrorLanguage>" & _ "<Pagination><EntriesPerPage>100</EntriesPerPage><PageNumber>1</PageNumber></Pagination>" & _ "<OutputSelector>ItemArray.Item.Title</OutputSelector>" & _ "<OutputSelector>ItemArray.Item.SellingStatus.CurrentPrice</OutputSelector>" & _ "</GetSellerListRequest>" 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
People who like this
Answer by helios825 · Apr 22, 2015 at 06:29 AM
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.
Answer by ve2_ehf · Nov 13, 2016 at 05:55 PM
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
To see what is returned and got this
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 ?
Answer by dufflepod · Nov 14, 2016 at 08:40 AM
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