How to import eBay orders into Excel in VBA

[mintz]

How do I import eBay orders into an Excel sheet in VBA? (with "Awaiting Shipping" status)

For example:

Cell A1 > get Transaction ID
Cell B1 > get Item ID
Cell C1 > get Buyer Name
Cell D1 > get Buyer Email

etc etc etc

 

[mintz]

nobody knows?? should be easy I guess

 

[Scott Huish]

Not sure why you think that it's easy exactly. But here's the page for the eBay API

https://go.developer.ebay.com/api-documentation

I haven't read it, so I'm not sure if it returns XML or JSON data.

 

[TonyUK72]

Easiest solution is to goto reporting in your ebay store and follow instructions to download CSV report for items needing dispatch.

If you want to further manipulate the data (CSV) using Excel come back to us.

 

[mintz]

Easiest solution is to goto reporting in your ebay store and follow instructions to download CSV report for items needing dispatch.

If you want to further manipulate the data (CSV) using Excel come back to us.

Yes that's exactly what I'm currently doing

1. manually download CSV
2. on the master workbook I wrote a macro (with great assistance from you forum guys) that takes the data from the downloaded csv, copies it to a temp sheet, manipulates the data (phone, address, zip formatting etc)
3. then pastes the data in the corresponding columns on the master workbook

the manual csv download takes time and I want to cut it
that's why I want the macro to load data directly from eBay into the temp sheet, instead of it copying from a downloaded csv

 

[mintz]

Not sure why you think that it's easy exactly. But here's the page for the eBay API

https://go.developer.ebay.com/api-documentation

I haven't read it, so I'm not sure if it returns XML or JSON data.

yes I've found that page on google as I searched for "ebay api vba"
but I haven't previous experience with API so I thought I would better find someone who is familiar with both ebay's api and vba to help with that

 

[mintz]

Not sure why you think that it's easy exactly. But here's the page for the eBay API

https://go.developer.ebay.com/api-documentation

I haven't read it, so I'm not sure if it returns XML or JSON data.

YES!! I figured it out!! I just managed to retrieve the data into excel after hours reading the API doc

Just one little technical problem

 

[mintz]

what is the correct syntax to retrieve the TotalShippingCost with currencyID=USD?

 

[John_w]

It should be the same syntax as the other nodes:

SelectNodes("//TotalShippingCost").Item(0).Text

which should return the node value, which is the AmountType string.

currencyID is an attribute of the node element, and to extract the CurrencyCodeType (the text "USD"), the code should be something like:

SelectNodes("//TotalShippingCost").Item(0).Attributes.getNamedItem("currencyID").Text

 

[mintz]

It should be the same syntax as the other nodes:

SelectNodes("//TotalShippingCost").Item(0).Text

which should return the node value, which is the AmountType string.

currencyID is an attribute of the node element, and to extract the CurrencyCodeType (the text "USD"), the code should be something like:

SelectNodes("//TotalShippingCost").Item(0).Attributes.getNamedItem("currencyID").Text

hmm when I tried //TotalShippingCost alone it still gave error, I think in the API doc it says that the input needs include the currencyID="USD" (so the API knows what value to return based on the user's requested currencyID) in this case I'd like the value of TotalShippingCost in USD currency (rather than just a "USD" text)