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

 

[John_w]

What exactly is the problem?

I tried my code today and it still works successfully. It also works after deleting the eBayAuthToken and HardExpirationTime values saved in the sheet cells, which causes it to request a new eBay authorisation.

 

[S4NK44]

Hello John_W,

I am also having issues running the code. I am trying to simply grab the results of this (which runs fine in the API explorer and gives me the data I need, I just need it in Excel):

<?xml version="1.0" encoding="utf-8"?><GetOrdersRequest xmlns="urn:ebay:apis:eBLBaseComponents">
  <!-- Call-specific Input Fields -->
  <IncludeFinalValueFee>true</IncludeFinalValueFee>
  <NumberOfDays>30</NumberOfDays>
  <OrderRole>Seller</OrderRole>
  <OrderStatus>Completed</OrderStatus>
  <SortingOrder>Ascending</SortingOrder>
  <DetailLevel>ReturnSummary</DetailLevel>
  <ErrorLanguage>en_US</ErrorLanguage>
  <MessageID>string</MessageID>
  <WarningLevel>Low</WarningLevel>
</GetOrdersRequest>

When using your code, I get an error with the XMLrequest = "..." lines, as it throws an syntax error there in my VBA screen (I used the newest one you provided). Am I missing a reference or something? I've tried 3/4 methods to get this data into Excel, and have had no luck thus far. I hoped it would have been easier.

 

[John_w]

When using your code, I get an error with the XMLrequest = "..." lines, as it throws an syntax error there in my VBA screen (I used the newest one you provided). Am I missing a reference or something?

What is the exact and complete error? Which line does it happen on? If it is a run-time error, click Debug on the error message and the errant line is highlighted in yellow.

Have you added the following references (via Tools -> References in the VBA editor) noted at the top of Module1?

'Microsoft XML v6.0               
'Microsoft HTML Object Library 
'Microsoft Internet Controls

 

[S4NK44]

Thanks for getting back to me, John_w. The error occurs at all of the XMLrequest lines. The exact error is a "Compile Error: Syntax Error." I can also confirm that I have all of the appropriate modules. I have added screen-shots here: Imgur: The most awesome images on the Internet

Thanks for your help! This is going to help a lot, if I can get this working!

What is the exact and complete error? Which line does it happen on? If it is a run-time error, click Debug on the error message and the errant line is highlighted in yellow.

Have you added the following references (via Tools -> References in the VBA editor) noted at the top of Module1?

'Microsoft XML v6.0               
'Microsoft HTML Object Library 
'Microsoft Internet Controls

 

[John_w]

Posting the Module1 code in the forum has changed the embedded apostrophes within the "string" lines to double quotes. To fix the error, change the embedded double quotes to apostrophes, like this:

    XMLrequest = "<?xml version='1.0' encoding='utf-8'?>" & _
                 "<GetSessionIDRequest xmlns='urn:ebay:apis:eBLBaseComponents'>" & _
                 "<RuName>" & RuName & "</RuName>" & _
                 "</GetSessionIDRequest>"

 

[S4NK44]

Thanks John_w! So now that the syntax error is resolved, I am getting an error similar to previous users.

The code gets to the "SessionID = XMLnode.nodeTypedValue" line and errors out. In debug mode, it states: "Run-Time error '91': Object variable or With block variable not set."

I have double-checked my DevID, AppID, CertID, and RuName. Any thoughts? Thanks!

Posting the Module1 code in the forum has changed the embedded apostrophes within the "string" lines to double quotes. To fix the error, change the embedded double quotes to apostrophes, like this:

    XMLrequest = "" & _
                 "" & _
                 "" & RuName & "" & _
                 ""

 

[John_w]

The code gets to the "SessionID = XMLnode.nodeTypedValue" line and errors out. In debug mode, it states: "Run-Time error '91': Object variable or With block variable not set."

That error means the XMLnode variable is Nothing, which is caused by the previous line:

Set XMLnode = XMLresponseElement.SelectSingleNode("/a:GetSessionIDResponse/a:SessionID")

attempting to get the SessionID string from the XML response.

I can reproduce the error by specifying an incorrect DevID, AppID, CertID or RuName. The code contains Debug.Print statements which output to the VBA Immediate Window - press Ctrl+G in the VBA editor to display the Immediate Window. If you look in the Immediate Window after the error has occurred there is a long XML string which includes the reason for the failure. For example, for a incorrect RuName string it says "The ru_name in your request is invalid". This XML error response may help you to pinpoint the cause of your error.

Remember this code is meant to be used with the eBay sandbox and an eBay developer account. If you are trying to use it with the real eBay site then changes may be needed. Note that in the ebayUserName string used in the code, "TESTUSER_xxxxxxx", the "TESTUSER_" part was fixed and provided by eBay.

Maybe you could contact this thread's OP as his/her code works successfully.

 

[S4NK44]

Thanks! That could be the issue... I am trying to utilize it with production data. Will continue working to find a solution. Thanks for your help!

That error means the XMLnode variable is Nothing, which is caused by the previous line:

Set XMLnode = XMLresponseElement.SelectSingleNode("/a:GetSessionIDResponse/a:SessionID")

attempting to get the SessionID string from the XML response.

I can reproduce the error by specifying an incorrect DevID, AppID, CertID or RuName. The code contains Debug.Print statements which output to the VBA Immediate Window - press Ctrl+G in the VBA editor to display the Immediate Window. If you look in the Immediate Window after the error has occurred there is a long XML string which includes the reason for the failure. For example, for a incorrect RuName string it says "The ru_name in your request is invalid". This XML error response may help you to pinpoint the cause of your error.

Remember this code is meant to be used with the eBay sandbox and an eBay developer account. If you are trying to use it with the real eBay site then changes may be needed. Note that in the ebayUserName string used in the code, "TESTUSER_xxxxxxx", the "TESTUSER_" part was fixed and provided by eBay.

Maybe you could contact this thread's OP as his/her code works successfully.