-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
How to import eBay orders into Excel in VBA
- Thread starter mintz
- Start date
I managed to pull the data but Im having trouble putting it in all in order
it will return outputs like this:
the data is completely mixed up
for example "johnk" doesnt have an Address 2, but the code gave it "marilyn43"'s value
also, "macchi" doesn't have an email, and the code gave it "marilyn's" value
I'm guessing I need some pointers in the For loop?
Code:
Sub GetSellerTransactions()
Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
URL = "https://api.ebay.com/ws/api.dll"
objHTTP.Open "POST", URL, False
objHTTP.setRequestHeader "X-EBAY-API-DEV-NAME", "________"
objHTTP.setRequestHeader "X-EBAY-API-CERT-NAME", "________"
objHTTP.setRequestHeader "X-EBAY-API-APP-NAME", "________"
objHTTP.setRequestHeader "X-EBAY-API-CALL-NAME", "GetSellerTransactions"
objHTTP.setRequestHeader "X-EBAY-API-SITEID", "0"
objHTTP.setRequestHeader "X-EBAY-API-REQUEST-Encoding", "XML"
objHTTP.setRequestHeader "X-EBAY-API-COMPATIBILITY-LEVEL", "967"
objHTTP.send (body)
Set objXML = New MSXML2.DOMDocument
objXML.LoadXML (objHTTP.ResponseText)
Dim xItemList As IXMLDOMNodeList
Set xItemList = objXML.SelectNodes("//Item")
Row = 1
Dim xItem As IXMLDOMNode
Dim copy As Worksheet
For Each xItem In xItemList
Cells(Row, 1) = xItem.SelectNodes("//Buyer/UserID").Item(0).Text
Cells(Row, 2) = xItem.SelectNodes("//Buyer/Name").Item(0).Text
Cells(Row, 3) = xItem.SelectNodes("///Buyer/Phone").Item(0).Text
Cells(Row, 4) = xItem.SelectNodes("//Buyer/Email").Item(0).Text
Row = Row + 1
Next
Set objHTTP = Nothing
Set objXML = Nothing
End Subit will return outputs like this:
the data is completely mixed up
for example "johnk" doesnt have an Address 2, but the code gave it "marilyn43"'s value
also, "macchi" doesn't have an email, and the code gave it "marilyn's" value
I'm guessing I need some pointers in the For loop?
Upvote
0
Excel Facts
Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
For the benefit of others, how did you solve it?
I've played around with the eBay XML HTTP API, and found I had to set the "SelectionNamespaces" property before loading the XMLHTTP response text into the XML document, and then you can access the currencyID attribute like this:
I've played around with the eBay XML HTTP API, and found I had to set the "SelectionNamespaces" property before loading the XMLHTTP response text into the XML document, and then you can access the currencyID attribute like this:
Code:
Dim XMLhttp
Dim XMLdoc As MSXML2.DOMDocument60
Dim XMLresponseElement As MSXML2.IXMLDOMElement
Dim XMLnode As MSXML2.IXMLDOMNode
Set XMLdoc = New MSXML2.DOMDocument60
XMLdoc.async = False
XMLdoc.validateOnParse = False
XMLdoc.setProperty "SelectionNamespaces", "xmlns:a='urn:ebay:apis:eBLBaseComponents'"
XMLdoc.LoadXML XMLhttp.responseText 'GetSellerTransactions response
Set XMLresponseElement = XMLdoc.DocumentElement
Set XMLresponseElement = XMLresponseElement.SelectSingleNode("/a:GetSellerTransactionsResponse/a:TransactionArray/a:Transaction")
Set XMLnode = XMLresponseElement.SelectSingleNode("a:AmountPaid")
Debug.Print XMLnode.nodeName & " = " & XMLnode.nodeTypedValue
Debug.Print XMLnode.Attributes.getNamedItem("currencyID").text
Upvote
0
You can import eBay orders into Excel using the eBay API, which the code in this thread uses. Based on the notes I made in August 2016 (some details may have changed since then), the steps are:
1. Create an eBay Developer Account at https://developer.ebay.com/join/. I'm not sure, but I think you need to link this to your eBay seller account. Instead, I also created an eBay Sandbox test seller account and Paypal Developer account and linked them.
2. On the Application Keys page note the Dev Id, App Id and Cert Id strings assigned to your eBay developer account, as these are used in the VBA code.
3. Click 'Get a Token' on the Your Application page to generate a RuName string (eBay Redirect URL name), which is used in the VBA code.
4. Make a note of one of your specific ItemId strings - used in the code below to retrieve details about that item. The code below uses the eBay Trading API to send a GetItemRequest to retrieve details about a specific item id. The code also sends a GetSellerTransactionsRequest to retrieve one or multiple recent order line items.
5. Change the .setRequestHeader "X-EBAY-API-SITEID" lines to number of the ebay site on which you are registered - see SiteCodeType - API Reference - Trading API.
Here is the complete code, which works (or did work) for the specific requirements of the OP in this thread. When I run it now, I get the error "System error -2146697208" at the first XMLhttp Send line. Note that code I'm posting below is for learning/development purposes and doesn't do anything nice like import orders neatly into Excel cells.
Class module named cEbayAuth (when adding a new class module you must change its module name from the default of Class1 to cEbayAuth in the VBA Properties pane.)
Standard module, Module1
As noted at the top of Module1, you must set the following references (via Tools -> References in the VBA editor), otherwise the code won't compile or run.
'Microsoft XML v6.0
'Microsoft HTML Object Library
'Microsoft Internet Controls
1. Create an eBay Developer Account at https://developer.ebay.com/join/. I'm not sure, but I think you need to link this to your eBay seller account. Instead, I also created an eBay Sandbox test seller account and Paypal Developer account and linked them.
2. On the Application Keys page note the Dev Id, App Id and Cert Id strings assigned to your eBay developer account, as these are used in the VBA code.
3. Click 'Get a Token' on the Your Application page to generate a RuName string (eBay Redirect URL name), which is used in the VBA code.
4. Make a note of one of your specific ItemId strings - used in the code below to retrieve details about that item. The code below uses the eBay Trading API to send a GetItemRequest to retrieve details about a specific item id. The code also sends a GetSellerTransactionsRequest to retrieve one or multiple recent order line items.
5. Change the .setRequestHeader "X-EBAY-API-SITEID" lines to number of the ebay site on which you are registered - see SiteCodeType - API Reference - Trading API.
Here is the complete code, which works (or did work) for the specific requirements of the OP in this thread. When I run it now, I get the error "System error -2146697208" at the first XMLhttp Send line. Note that code I'm posting below is for learning/development purposes and doesn't do anything nice like import orders neatly into Excel cells.
Class module named cEbayAuth (when adding a new class module you must change its module name from the default of Class1 to cEbayAuth in the VBA Properties pane.)
Code:
'Based on http://ramblings.mcpher.com/Home/excelquirks/guests/oauth2
'Required reference:
'Microsoft Internet Controls
'
'================================================================================== '
Option Explicit
#If VBA7 Then
Private Declare PtrSafe Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As LongPtr)
Private Declare PtrSafe Function SetForegroundWindow Lib "user32.dll" (ByVal hwnd As LongPtr) As LongPtr
#Else
Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
Private Declare Function SetForegroundWindow Lib "user32.dll" (ByVal hwnd As Long) As Long
#End If
'Internet Explorer variables for ebay webpage which displays 'Grant Access to ' and 'I Agree' button
Private WithEvents IE As InternetExplorer
Private pIEcomplete As Boolean
Private peBayUsername As String
Private peBayAuth As Boolean
Public Function GetEbayAuth(eBaySignInURL As String, eBayUsername As String) As Boolean
peBayUsername = eBayUsername
peBayAuth = False
pIEcomplete = False
Set IE = New InternetExplorer
With IE
.navigate eBaySignInURL
SetForegroundWindow .hwnd
.AddressBar = False
.MenuBar = False
.Resizable = False
.Visible = True
End With
Do: DoEvents: Sleep 200: Loop Until pIEcomplete
Debug.Print peBayAuth
GetEbayAuth = peBayAuth
End Function
Private Sub IE_NewWindow2(ppDisp As Object, Cancel As Boolean)
Debug.Print "NewWindow2"
Dim IE As InternetExplorer
Set IE = ppDisp.Application
Debug.Print IE.LocationName
End Sub
'Break wait loop on quit of IE
Private Sub IE_OnQuit()
pIEcomplete = True
End Sub
'Check the IE window title or location URL to determine if user granted access.
Private Sub IE_TitleChange(ByVal text As String)
Debug.Print Now; text
If InStr(1, text, "isAuthSuccessful=true", vbTextCompare) > 0 And InStr(1, text, "&username=" & peBayUsername, vbTextCompare) > 0 Then
peBayAuth = True
IE.Quit
ElseIf InStr(1, IE.LocationURL, "ThirdPartyAuthSucessFailure") > 0 Then 'Success misspelt as Sucess
'Actual URL https://signin.ebay.com/ws/eBayISAPI.dll?ThirdPartyAuthSucessFailure&
IE.Quit
ElseIf InStr(1, IE.LocationURL, "ThirdPartyAuthSuccessFailure") > 0 Then 'Possible future spelling correction
IE.Quit
End If
End SubStandard module, Module1
Code:
'----------------------------------------------------------------------------------
'References for early binding. CreateObject string for late binding.
'Microsoft XML v6.0 "MSXML2.XMLHTTP"
'Microsoft HTML Object Library "HTMLfile"
'Microsoft Internet Controls "InternetExplorer.Application"
Option Explicit
Public Sub XMLhttp_Ebay()
#If VBA7 Then
Dim httpReq As XMLHTTP60
Set httpReq = New XMLHTTP60
#Else
Dim httpReq As XMLhttp
Set httpReq = New XMLhttp
#End If
Dim DevID As String, AppID As String, CertID As String
Dim RuName As String
Dim eBayUsername As String
Dim ItemID As String
Dim SessionID As String
Dim eBayAuthToken As String, HardExpirationTime As String, tokenExpirationTime As Date
Dim XMLrequest As String
Dim ebayAuth As cEbayAuth, eBayAuthSuccessful As Boolean
Dim URL As String
Dim endpoint As String
Dim XMLdoc As MSXML2.DOMDocument60
Dim XMLnode As MSXML2.IXMLDOMNode
Dim XMLnodeList As MSXML2.IXMLDOMNodeList
Dim XMLresponseElement As MSXML2.IXMLDOMElement
Dim XMLresponseElementBuyer As IXMLDOMElement
'These 3 values are assigned in eBay Developer's Account (https://developer.ebay.com/join/), Application Keys page
DevID = "xxxxxxx-xxxx-xxxx-xxx-xxxxxxxx"
AppID = "xxxxx-xxxxx-xxx-xxxxxx-xxxxxx"
CertID = "xxxx-xxxxx-xxxx-xxxx-xxx-xxxxx"
'RuName (eBay Redirect URL name) is assigned in eBay Developer's Account via Get a Token from eBay via Your Application
'RuName string Required
'The eBay Registered URL must be specified in this field. An eBay Registered URL is created and associated with a developer's Sandbox
'or Production key set by logging into the user's developer account on developer.ebay.com and going to the User Tokens page. The RuName
'value passed in this field must match the one specified for the specific Sandbox or Production key set being used to make the GetSessionID
'call. For more information on adding and registering your RuName, see the the Setting Up an Application to Receive Tokens tutorial.
RuName = "xxxxxx_xxxxx-xxxxx-xxxx-xxxx"
'eBay username for sandbox user 1 - the seller
eBayUsername = "TESTUSER_xxxxxxx"
'ItemID - the eBay item to be retrieved
ItemID = "123456789012"
'eBay XML API Sandbox Gateway URI
endpoint = "https://api.sandbox.ebay.com/ws/api.dll"
'Get the current eBayAuthToken and HardExpirationTime (if previously saved) from sheet cells
eBayAuthToken = Worksheets(1).Range("B1").Value
HardExpirationTime = Worksheets(1).Range("B2").Value
'http://developer.ebay.com/devzone/xml/docs/reference/ebay/index.html#Limitations
'If you are using the Trading API, pass the RequesterCredentials node in the request XML as in the snippet below for most XML
'requests (exceptions are FetchToken and GetSessionID).
'
' string
'
'https://developer.ebay.com/devzone/xml/docs/Concepts/MakingACall.html#HTTPHeaders
'Table: HTTP Headers for XML API Calls
'Name Required? Description
'X-EBAY-API-COMPATIBILITY-LEVEL Always
'The eBay release version that your application supports. See the eBay Schema Versioning Strategy for information about how the version affects
'the way eBay processes your request.
'X-EBAY-API-DEV-NAME Conditionally
'Your Developer ID (DevID), as registered with the eBay Developers Program. The developer ID is unique to each licensed developer (or company).
'This value is required only for calls that set up and retrieve a user's authentication token (these calls are: FetchToken, GetSessionID,
'GetTokenStatus, and RevokeToken). In all other calls, this value is ignored. If you lose your keys you can retrieve them using the View Keys
'link on your My Account page. Here is the direct link to the Keys page (requires signin): http://developer.ebay.com/DevZone/account/keys.asp
'X-EBAY-API-APP-NAME Conditionally
'Your application ID (AppID), as registered with the eBay Developers Program. This value is required only for calls that set up and retrieve a
'user's authentication token (FetchToken, GetSessionID, GetTokenStatus, and RevokeToken). In all other calls, this value is ignored. Do not
'specify this value in AddItem and other calls that list items. The application ID is unique to each application created by the developer.
'The application ID and certificate ID are issued in pairs. Multiple application/certificate ID pairs can be issued for a single developer ID.
'X-EBAY-API-CERT-NAME Conditionally
'Your certificate ID (CertID), as registered with the eBay Developers Program. This value is required only for calls that set up and retrieve a
'user's authentication token (FetchToken, GetSessionID, GetTokenStatus, and RevokeToken). In all other calls, this value is ignored. Do not
'specify this value in AddItem and other calls that list items. The certificate ID is unique to each application created by the developer.
'X-EBAY-API-CALL-NAME Always
'Canonical name of the call you are using (e.g., AddItem). To be routed correctly, the value must match the request name. For example, if you are
'using AddItemRequest, specify "AddItem" (without "Request") in this call name header.
'X-EBAY-API-SITEID Always
'eBay site to which you want to send the request. See SiteCodeType for a list of valid site ID values. This is usually the eBay site an item is
'listed on or that a user is registered on, depending on the purpose of the call. See Specifying the Target Site to understand how the site ID
'may affect validation of the call and how it may affect the data that is returned. For calls like AddItem, the site that you pass in the body of
'the request must be consistent with this header. Note: In AddItem, you specify the 2-letter site code. In this header, you specify the numeric site ID.
'Content-Type Recommended
'Specify "text/xml". Sending any other value might cause the call to fail. Note: If you use a library that sets the ContentType header by default,
'make sure it uses "text/xml".
'Content-Length Recommended
'The length of the XML request string you are sending. Used by eBay to determine how much data to read from the stream.
'http://developer.ebay.com/devzone/xml/docs/Reference/eBay/GetSessionID.html
'Use this call to retrieve a SessionID, which is a unique identifier that you use for authentication during the token-creation process.
'You must have a valid SessionID value in order to make a FetchToken request
XMLrequest = "" & _
"" & _
"" & _
"" & eBayAuthToken & "" & _
"" & _
"" & ItemID & "" & _
""
'Send GetSessionIDRequest
With httpReq
.Open "POST", endpoint, False
.setRequestHeader "X-EBAY-API-SITEID", "3"
.setRequestHeader "X-EBAY-API-COMPATIBILITY-LEVEL", "967"
.setRequestHeader "X-EBAY-API-CALL-NAME", "GetSessionID"
.setRequestHeader "X-EBAY-API-APP-NAME", AppID
.setRequestHeader "X-EBAY-API-DEV-NAME", DevID
.setRequestHeader "X-EBAY-API-CERT-NAME", CertID
.setRequestHeader "Content-Type", "text/xml"
.setRequestHeader "Content-Length", Len(XMLrequest)
.send (XMLrequest) 'brackets are compulsory only for late binding of XMLhttp
Debug.Print .Status, .statusText
Debug.Print .getAllResponseHeaders
Debug.Print .responseText
'Using XMLdoc.setProperty "SelectionNamespaces" - see http://stackoverflow.com/questions/20032610/vba-excel-selectsinglenode-syntax
Set XMLdoc = New MSXML2.DOMDocument60
XMLdoc.async = False
XMLdoc.validateOnParse = False
XMLdoc.setProperty "SelectionNamespaces", "xmlns:a='urn:ebay:apis:eBLBaseComponents'"
XMLdoc.LoadXML .responseText
End With
Debug.Print XMLdoc.XML
'GetSessionIDResponse
'
'2016-08-20T15:40:32.174ZSuccess979E979_INTL_API_18061441_R1vuoDAA**a89c521e1560a471d23324b3fffffff5
'SessionID string
'A 40-character identifier supplied by eBay to an application. Used to confirm the identities of the user and the application in a URL redirect
'during the process in which the user agrees to let the application wield a user token that grants the application the right to access eBay data
'on behalf of the user. Subsequently also used as input for the FetchToken API call. The SessionID is valid for five minutes after it is retrieved.
'Extract the SessionID from the GetSessionIDResponse
Set XMLresponseElement = XMLdoc.DocumentElement
Set XMLnode = XMLresponseElement.SelectSingleNode("/a:GetSessionIDResponse/a:SessionID")
SessionID = XMLnode.nodeTypedValue
Debug.Print SessionID
tokenExpirationTime = CvtDateTime(HardExpirationTime)
If eBayAuthToken = "" Or Now > tokenExpirationTime Then
'The eBay user must sign in to the eBay sandbox using the Sandbox user 1 account (TESTUSER_xxxxxx)
URL = "https://signin.sandbox.ebay.com/ws/eBayISAPI.dll?SignIn&RUName=" & RuName & "&SessID=" & Escape(SessionID)
Debug.Print URL
Set ebayAuth = New cEbayAuth
eBayAuthSuccessful = ebayAuth.GetEbayAuth(URL, eBayUsername)
If Not eBayAuthSuccessful Then
MsgBox "eBay username " & eBayUsername & " did not grant access to this application."
Exit Sub
End If
'http://developer.ebay.com/devzone/xml/docs/Reference/eBay/FetchToken.html
'FetchToken
'Use this call to retrieve an authentication token for a user. The call can be used to get a token only after the specified user has given
'consent for the token to be generated. Consent is given through the eBay sign-in page. After token retrieval, the token can be used to
'authenticate other calls made on behalf of the associated user.
'
'Unlike other Trading API calls, FetchToken requests do not require a user token for authorization. Instead, the call makes use of your
'application keys (App ID, Dev ID, and Cert ID) for authorization. In addition, you must supply a SessionID value, which you obtain by making
'a call to GetSessionID.
XMLrequest = "" & _
"" & _
"" & SessionID & "" & _
""
'Send FetchTokenRequest
With httpReq
.Open "POST", endpoint, False
.setRequestHeader "X-EBAY-API-SITEID", "3"
.setRequestHeader "X-EBAY-API-COMPATIBILITY-LEVEL", "967"
.setRequestHeader "X-EBAY-API-CALL-NAME", "FetchToken"
.setRequestHeader "X-EBAY-API-APP-NAME", AppID
.setRequestHeader "X-EBAY-API-DEV-NAME", DevID
.setRequestHeader "X-EBAY-API-CERT-NAME", CertID
.setRequestHeader "Content-Type", "text/xml"
.setRequestHeader "Content-Length", Len(XMLrequest)
.send (XMLrequest) 'brackets are compulsory only for late binding of XMLhttp
Debug.Print .Status, .statusText
Debug.Print .getAllResponseHeaders
Debug.Print .responseText
Set XMLdoc = New MSXML2.DOMDocument60
XMLdoc.async = False
XMLdoc.validateOnParse = False
XMLdoc.setProperty "SelectionNamespaces", "xmlns:a='urn:ebay:apis:eBLBaseComponents'"
XMLdoc.LoadXML .responseText
End With
Debug.Print XMLdoc.XML
Set XMLresponseElement = XMLdoc.DocumentElement
'FetchTokenResponse
'
'
' string
' dateTime
'eBayAuthToken string
'The authentication token for the user.
'
'HardExpirationTime dateTime
'Date and time at which the token returned in eBayAuthToken expires and can no longer be used to authenticate the user for that application.
'Extract eBayAuthToken and HardExpirationTime from response and put in cells B1 and B2
Set XMLnode = XMLresponseElement.SelectSingleNode("/a:FetchTokenResponse/a:eBayAuthToken")
eBayAuthToken = XMLnode.nodeTypedValue
Debug.Print eBayAuthToken
Set XMLnode = XMLresponseElement.SelectSingleNode("/a:FetchTokenResponse/a:HardExpirationTime")
HardExpirationTime = XMLnode.nodeTypedValue
Debug.Print HardExpirationTime
Worksheets(1).Range("B1").Value = eBayAuthToken
Worksheets(1).Range("B2").Value = HardExpirationTime
End If
'http://developer.ebay.com/devzone/xml/docs/Reference/eBay/GetItem.html
'GetItem
'
'Use this call to retrieve the data for a single item listed on an eBay site. GetItem returns the data in an Item object.
'
'This call is conceptually similar to eBay's View item page. Data returned includes the item's title, description, price information,
'seller information, shipping details, high bidder information (if there currently is a high bidder), and so on.
XMLrequest = "" & _
"" & _
"" & _
"" & eBayAuthToken & "" & _
"" & _
"" & ItemID & "" & _
""
'Send GetItemRequest
With httpReq
.Open "POST", endpoint, False
.setRequestHeader "X-EBAY-API-SITEID", "3"
.setRequestHeader "X-EBAY-API-COMPATIBILITY-LEVEL", "967"
.setRequestHeader "X-EBAY-API-CALL-NAME", "GetItem"
.setRequestHeader "X-EBAY-API-APP-NAME", AppID
.setRequestHeader "X-EBAY-API-DEV-NAME", DevID
.setRequestHeader "X-EBAY-API-CERT-NAME", CertID
.setRequestHeader "Content-Type", "text/xml"
.setRequestHeader "Content-Length", Len(XMLrequest)
.send (XMLrequest) 'brackets are compulsory only for late binding of XMLhttp
Debug.Print .Status, .statusText
Debug.Print .getAllResponseHeaders
Debug.Print .responseText
Set XMLdoc = New MSXML2.DOMDocument60
XMLdoc.async = False
XMLdoc.validateOnParse = False
XMLdoc.setProperty "SelectionNamespaces", "xmlns:a='urn:ebay:apis:eBLBaseComponents'"
XMLdoc.LoadXML .responseText
End With
Debug.Print XMLdoc.XML
Set XMLresponseElement = XMLdoc.DocumentElement
Set XMLnodeList = XMLresponseElement.SelectNodes("/a:GetItemResponse/a:Item/a:ListingDetails/a:*")
For Each XMLnode In XMLnodeList
Debug.Print XMLnode.nodeName, XMLnode.nodeTypedValue
Next
parseXML XMLresponseElement, 0
Set XMLnode = XMLresponseElement.SelectSingleNode("/a:GetItemResponse/a:Item/a:ListingDuration")
Debug.Print XMLnode.nodeTypedValue
Set XMLnode = XMLresponseElement.SelectSingleNode("/a:GetItemResponse/a:Item/a:ListingDetails/a:ConvertedStartPrice")
Debug.Print XMLnode.nodeTypedValue
Debug.Print XMLnode.Attributes.getNamedItem("currencyID").text
'http://developer.ebay.com/devzone/xml/docs/Reference/eBay/GetSellerTransactions.html
'GetSellerTransactions
'
'A seller will use this call to retrieve one or multiple recent order line items. This call can potentially return many order line items,
'depending on the recent sale activity of the authenticated user. Sales older than 90 days cannot be retrieved with this call.
XMLrequest = "" & _
"" & _
"" & _
"" & eBayAuthToken & "" & _
"" & _
""
'Send GetSellerTransactionsRequest
With httpReq
.Open "POST", endpoint, False
.setRequestHeader "X-EBAY-API-SITEID", "3"
.setRequestHeader "X-EBAY-API-COMPATIBILITY-LEVEL", "967"
.setRequestHeader "X-EBAY-API-CALL-NAME", "GetSellerTransactions"
.setRequestHeader "X-EBAY-API-APP-NAME", AppID
.setRequestHeader "X-EBAY-API-DEV-NAME", DevID
.setRequestHeader "X-EBAY-API-CERT-NAME", CertID
.setRequestHeader "Content-Type", "text/xml"
.setRequestHeader "Content-Length", Len(XMLrequest)
.send (XMLrequest) 'brackets are compulsory only for late binding of XMLhttp
Debug.Print .Status, .statusText
Debug.Print .getAllResponseHeaders
Debug.Print .responseText
Set XMLdoc = New MSXML2.DOMDocument60
XMLdoc.async = False
XMLdoc.validateOnParse = False
XMLdoc.setProperty "SelectionNamespaces", "xmlns:a='urn:ebay:apis:eBLBaseComponents'"
XMLdoc.LoadXML .responseText
End With
Debug.Print XMLdoc.XML
Set XMLresponseElement = XMLdoc.DocumentElement
parseXML XMLresponseElement, 0
'Select all nodes within the node
Set XMLnodeList = XMLresponseElement.SelectNodes("/a:GetSellerTransactionsResponse/a:TransactionArray/a:Transaction/a:Buyer/a:*")
Debug.Print XMLnodeList.Length
For Each XMLnode In XMLnodeList
Debug.Print XMLnode.nodeName, XMLnode.nodeTypedValue
Next
'Select the Transaction node and output value of AmountPaid node and its currencyID attribute
Dim XMLresponseElementTransaction As IXMLDOMElement
Set XMLresponseElementTransaction = XMLresponseElement.SelectSingleNode("/a:GetSellerTransactionsResponse/a:TransactionArray/a:Transaction")
Set XMLnode = XMLresponseElementTransaction.SelectSingleNode("a:AmountPaid")
Debug.Print XMLnode.nodeName & " = " & XMLnode.nodeTypedValue; XMLnode.Attributes.getNamedItem("currencyID").text
'Select the Buyer node within the Transaction node
Dim XMLBuyerNode As IXMLDOMNode
Set XMLBuyerNode = XMLresponseElementTransaction.SelectSingleNode("a:Buyer")
Debug.Print XMLBuyerNode.ChildNodes.Length
For Each XMLnode In XMLBuyerNode.ChildNodes
Debug.Print XMLnode.nodeName, XMLnode.nodeTypedValue
Next
'Another way of accessing the Buyer node by specifying the full path
Set XMLresponseElementBuyer = XMLresponseElement.SelectSingleNode("/a:GetSellerTransactionsResponse/a:TransactionArray/a:Transaction/a:Buyer")
Debug.Print XMLresponseElementBuyer.ChildNodes.Length
Set XMLresponseElementBuyer = XMLresponseElementTransaction.SelectSingleNode("a:Buyer")
Debug.Print XMLresponseElementBuyer.ChildNodes.Length
'Dim XMLBuyerNodes As IXMLDOMNodeList
'Set XMLBuyerNodes = XMLresponseElementTransaction.SelectNodes("/a:*")
'Debug.Print XMLBuyerNodes.Length
For Each XMLnode In XMLresponseElementBuyer.ChildNodes
Debug.Print XMLnode.nodeName, XMLnode.nodeTypedValue
Next
'Access specific nodes within the Buyer node
Set XMLnode = XMLresponseElementBuyer.SelectSingleNode("a:Email")
Debug.Print XMLnode.nodeName & " = " & XMLnode.nodeTypedValue
Set XMLnode = XMLresponseElementBuyer.SelectSingleNode("a:UserID")
Debug.Print XMLnode.nodeName & " = " & XMLnode.nodeTypedValue
Set XMLnode = XMLresponseElementBuyer.SelectSingleNode("a:UserFirstName")
Debug.Print XMLnode.nodeName & " = " & XMLnode.nodeTypedValue
Set XMLnode = XMLresponseElementBuyer.SelectSingleNode("a:UserLastName")
Debug.Print XMLnode.nodeName & " = " & XMLnode.nodeTypedValue
' TransactionArray
'Transaction
' AmountPaid 27.2 currencyID=GBP
'BuyerUserID GetDispute.Dispute
'GetOrders.OrderArray.Order
'GetOrderTransactions.OrderArray.Order
'PayPal Express Checkout Payment Sent (Unique Transaction ID3L3xxxxxxxx733433G)
Dim OrderLineItemID As String
OrderLineItemID = ItemID & "-" & "3L3xxxxxxx733433G"
XMLrequest = "" & _
"" & _
"" & _
"" & OrderLineItemID & "" & _
"" & _
""
With httpReq
.Open "POST", endpoint, False
.setRequestHeader "X-EBAY-API-SITEID", "3"
.setRequestHeader "X-EBAY-API-COMPATIBILITY-LEVEL", "967"
.setRequestHeader "X-EBAY-API-CALL-NAME", "GetOrders"
.setRequestHeader "X-EBAY-API-APP-NAME", AppID
.setRequestHeader "X-EBAY-API-DEV-NAME", DevID
.setRequestHeader "X-EBAY-API-CERT-NAME", CertID
.setRequestHeader "Content-Type", "text/xml"
.setRequestHeader "Content-Length", Len(XMLrequest)
.send (XMLrequest) 'brackets are compulsory only for late binding of XMLhttp
Debug.Print .Status, .statusText
Debug.Print .getAllResponseHeaders
Debug.Print .responseText
Set XMLdoc = New MSXML2.DOMDocument60
XMLdoc.async = False
XMLdoc.validateOnParse = False
XMLdoc.setProperty "SelectionNamespaces", "xmlns:a='urn:ebay:apis:eBLBaseComponents'"
XMLdoc.LoadXML .responseText
End With
Debug.Print XMLdoc.XML
End Sub
'http://www.tushar-mehta.com/publish_train/xl_vba_cases/vba_web_pages_services/
'With bug fix. The "%" should be first in the BadChars string because it is used as the escape character.
Private Function Escape(ByVal param As String) As String
Dim i As Integer, BadChars As String
BadChars = "%<>=&!@#$^()+{[}]|\;:'"",/?"
'BadChars = "<>%=&!@#$^()+{[}]|\;:'"",/?" 'original code
For i = 1 To Len(BadChars)
param = Replace(param, Mid(BadChars, i, 1), "%" & Hex(Asc(Mid(BadChars, i, 1))))
Next
param = Replace(param, " ", "+")
Escape = param
End Function
'Scan XML DOM tree recursively and output node names and values to Immediate window
Private Sub parseXML(node As IXMLDOMNode, Optional level As Integer)
Dim i As Long
If node.NodeType = NODE_ELEMENT Then
Debug.Print
Debug.Print String(level * 2, " ") & node.nodeName;
ElseIf node.NodeType = NODE_TEXT Then
Debug.Print " " & node.NodeValue;
For i = 0 To node.ParentNode.Attributes.Length - 1
Debug.Print " "; node.ParentNode.Attributes(i).BaseName & "=" & node.ParentNode.Attributes(i).text;
Next
End If
For i = 0 To node.ChildNodes.Length - 1
parseXML node.ChildNodes(i), level + 1
Next
End Sub
'http://developer.ebay.com/devzone/xml/docs/Reference/eBay/types/simpleTypes.html#dateTime
'dateTime
'
'A specific instant of time.
'
'Date-time values are in the ISO 8601 date and time format. Hours are in 24-hour format (e.g., 2:00:00pm is 14:00:00). Unless otherwise
'specified, all date-time values are recorded in Universal Coordinated Time (UTC), also known as Greenwich Mean Time (GMT) or Zulu (because
'the time portion of the time stamp ends with a Z). That is, time values do not reflect the time zone shown on the eBay Web site. Here is an
'example of the date-time format:
'
'YYYY-MM-DDTHH:MM:SS.SSSZ (e.g., 2004-08-04T19:09:02.768Z)
'
'See Time Values for information about how to convert between local time zones and GMT.
'http://developer.ebay.com/devzone/guides/ebayfeatures/Basics/DataTypes.html#TimeValues
'
'We use the dateTime data type to convey start and end times, the official eBay time, and other time values.
'
'xs:dateTime is part of the XML schema namespace defined as:
'
'xmlns: xs = "http://www.w3.org/2001/XMLSchema"
Private Function CvtDateTime(eBayDateTime As String) As Date
'Convert eBay dateTime string to an Excel Date variable
'2018-02-14T22:56:01.000Z
CvtDateTime = DateSerial(Mid(eBayDateTime, 1, 4), Mid(eBayDateTime, 6, 2), Mid(eBayDateTime, 9, 2)) + TimeValue(Mid(eBayDateTime, 12, 8))
End Function
Private Sub Log_HTML(HTML As String)
Static num As Integer
num = num + 1
Open "F:\Temp\Excel\Log\HTML" & num & Format(Now, " hh mm ss") & ".txt" For Output As #1
Print #1, HTML
Close #1
End Sub'Microsoft XML v6.0
'Microsoft HTML Object Library
'Microsoft Internet Controls
Last edited by a moderator:
Upvote
0
Thanks for you detailed post. I'm really thankful for your time and help.
Would you mind if i ask something else? Actually I'm new to VBA and to programming as well.
I'm getting error for debug. It stuck on this line so far
" SessionID = XMLnode.nodeTypedValue"
Can you help bit more.
Regards
Would you mind if i ask something else? Actually I'm new to VBA and to programming as well.
I'm getting error for debug. It stuck on this line so far
" SessionID = XMLnode.nodeTypedValue"
Can you help bit more.
Regards
Upvote
0
I see what has happened. The forum has removed the XML markup in Module1 (the XMLrequest = "..." lines) so the code is sending completely incorrect requests.
Try this as Module1 instead:
Try this as Module1 instead:
Code:
'References for early binding. CreateObject string for late binding.
'Microsoft XML v6.0 "MSXML2.XMLHTTP"
'Microsoft HTML Object Library "HTMLfile"
'Microsoft Internet Controls "InternetExplorer.Application"
Option Explicit
Public Sub XMLhttp_Ebay2()
#If VBA7 Then
Dim httpReq As XMLHTTP60
Set httpReq = New XMLHTTP60
#Else
Dim httpReq As XMLhttp
Set httpReq = New XMLhttp
#End If
Dim DevID As String, AppID As String, CertID As String
Dim RuName As String
Dim eBayUsername As String
Dim ItemID As String
Dim SessionID As String
Dim eBayAuthToken As String, HardExpirationTime As String, tokenExpirationTime As Date
Dim XMLrequest As String
Dim ebayAuth As cEbayAuth, eBayAuthSuccessful As Boolean
Dim URL As String
Dim endpoint As String
Dim XMLdoc As MSXML2.DOMDocument60
Dim XMLnode As MSXML2.IXMLDOMNode
Dim XMLnodeList As MSXML2.IXMLDOMNodeList
Dim XMLresponseElement As MSXML2.IXMLDOMElement
Dim XMLresponseElementBuyer As IXMLDOMElement
'These 3 values are assigned in eBay Developer's Account (https://developer.ebay.com/join/), Application Keys page
DevID = "xxxxxx-xxxx-xxxx-xxxx-xxxxxxxxx"
AppID = "xxxxx-xxxxx-SBX-xxxxxx-xxxxx"
CertID = "SBX-xxxxxxxxx-xxxx-xxxx-xxxx-xxxx"
'RuName (eBay Redirect URL name) is assigned in eBay Developer's Account via Get a Token from eBay via Your Application
'RuName string Required
'The eBay Registered URL must be specified in this field. An eBay Registered URL is created and associated with a developer's Sandbox
'or Production key set by logging into the user's developer account on developer.ebay.com and going to the User Tokens page. The RuName
'value passed in this field must match the one specified for the specific Sandbox or Production key set being used to make the GetSessionID
'call. For more information on adding and registering your RuName, see the the Setting Up an Application to Receive Tokens tutorial.
RuName = "xxxxxx-xxxxxx-xxxxx-cchzm"
'eBay username for sandbox user 1 - the seller
eBayUsername = "TESTUSER_xxxx"
'ItemID - the eBay item to be retrieved
ItemID = "123456789012"
'eBay XML API Sandbox Gateway URI
endpoint = "https://api.sandbox.ebay.com/ws/api.dll"
'Get the current eBayAuthToken and HardExpirationTime (if previously saved) from sheet cells
eBayAuthToken = Worksheets(1).Range("B1").Value
HardExpirationTime = Worksheets(1).Range("B2").Value
'http://developer.ebay.com/devzone/xml/docs/reference/ebay/index.html#Limitations
'If you are using the Trading API, pass the RequesterCredentials node in the request XML as in the snippet below for most XML
'requests (exceptions are FetchToken and GetSessionID).
'
' string
'
'https://developer.ebay.com/devzone/xml/docs/Concepts/MakingACall.html#HTTPHeaders
'Table: HTTP Headers for XML API Calls
'Name Required? Description
'X-EBAY-API-COMPATIBILITY-LEVEL Always
'The eBay release version that your application supports. See the eBay Schema Versioning Strategy for information about how the version affects
'the way eBay processes your request.
'X-EBAY-API-DEV-NAME Conditionally
'Your Developer ID (DevID), as registered with the eBay Developers Program. The developer ID is unique to each licensed developer (or company).
'This value is required only for calls that set up and retrieve a user's authentication token (these calls are: FetchToken, GetSessionID,
'GetTokenStatus, and RevokeToken). In all other calls, this value is ignored. If you lose your keys you can retrieve them using the View Keys
'link on your My Account page. Here is the direct link to the Keys page (requires signin): http://developer.ebay.com/DevZone/account/keys.asp
'X-EBAY-API-APP-NAME Conditionally
'Your application ID (AppID), as registered with the eBay Developers Program. This value is required only for calls that set up and retrieve a
'user's authentication token (FetchToken, GetSessionID, GetTokenStatus, and RevokeToken). In all other calls, this value is ignored. Do not
'specify this value in AddItem and other calls that list items. The application ID is unique to each application created by the developer.
'The application ID and certificate ID are issued in pairs. Multiple application/certificate ID pairs can be issued for a single developer ID.
'X-EBAY-API-CERT-NAME Conditionally
'Your certificate ID (CertID), as registered with the eBay Developers Program. This value is required only for calls that set up and retrieve a
'user's authentication token (FetchToken, GetSessionID, GetTokenStatus, and RevokeToken). In all other calls, this value is ignored. Do not
'specify this value in AddItem and other calls that list items. The certificate ID is unique to each application created by the developer.
'X-EBAY-API-CALL-NAME Always
'Canonical name of the call you are using (e.g., AddItem). To be routed correctly, the value must match the request name. For example, if you are
'using AddItemRequest, specify "AddItem" (without "Request") in this call name header.
'X-EBAY-API-SITEID Always
'eBay site to which you want to send the request. See SiteCodeType for a list of valid site ID values. This is usually the eBay site an item is
'listed on or that a user is registered on, depending on the purpose of the call. See Specifying the Target Site to understand how the site ID
'may affect validation of the call and how it may affect the data that is returned. For calls like AddItem, the site that you pass in the body of
'the request must be consistent with this header. Note: In AddItem, you specify the 2-letter site code. In this header, you specify the numeric site ID.
'Content-Type Recommended
'Specify "text/xml". Sending any other value might cause the call to fail. Note: If you use a library that sets the ContentType header by default,
'make sure it uses "text/xml".
'Content-Length Recommended
'The length of the XML request string you are sending. Used by eBay to determine how much data to read from the stream.
'http://developer.ebay.com/devzone/xml/docs/Reference/eBay/GetSessionID.html
'Use this call to retrieve a SessionID, which is a unique identifier that you use for authentication during the token-creation process.
'You must have a valid SessionID value in order to make a FetchToken request
XMLrequest = "<!--?xml version='1.0' encoding='utf-8'?-->" & _
"<getsessionidrequest xmlns="urn:ebay:apis:eBLBaseComponents">" & _
"<runame>" & RuName & "</runame>" & _
"</getsessionidrequest>"
'Send GetSessionIDRequest
With httpReq
.Open "POST", endpoint, False
.setRequestHeader "X-EBAY-API-SITEID", "3"
.setRequestHeader "X-EBAY-API-COMPATIBILITY-LEVEL", "967"
.setRequestHeader "X-EBAY-API-CALL-NAME", "GetSessionID"
.setRequestHeader "X-EBAY-API-APP-NAME", AppID
.setRequestHeader "X-EBAY-API-DEV-NAME", DevID
.setRequestHeader "X-EBAY-API-CERT-NAME", CertID
.setRequestHeader "Content-Type", "text/xml"
.setRequestHeader "Content-Length", Len(XMLrequest)
.send (XMLrequest) 'brackets are compulsory only for late binding of XMLhttp
Debug.Print .Status, .statusText
Debug.Print .getAllResponseHeaders
Debug.Print .responseText
'Using XMLdoc.setProperty "SelectionNamespaces" - see http://stackoverflow.com/questions/20032610/vba-excel-selectsinglenode-syntax
Set XMLdoc = New MSXML2.DOMDocument60
XMLdoc.async = False
XMLdoc.validateOnParse = False
XMLdoc.setProperty "SelectionNamespaces", "xmlns:a='urn:ebay:apis:eBLBaseComponents'"
XMLdoc.LoadXML .responseText
End With
Debug.Print XMLdoc.XML
'GetSessionIDResponse
'
'2016-08-20T15:40:32.174ZSuccess979E979_INTL_API_18061441_R1vuoDAA**a89c521e1560a471d23324b3fffffff5
'SessionID string
'A 40-character identifier supplied by eBay to an application. Used to confirm the identities of the user and the application in a URL redirect
'during the process in which the user agrees to let the application wield a user token that grants the application the right to access eBay data
'on behalf of the user. Subsequently also used as input for the FetchToken API call. The SessionID is valid for five minutes after it is retrieved.
'Extract the SessionID from the GetSessionIDResponse
Set XMLresponseElement = XMLdoc.DocumentElement
Set XMLnode = XMLresponseElement.SelectSingleNode("/a:GetSessionIDResponse/a:SessionID")
SessionID = XMLnode.nodeTypedValue
Debug.Print SessionID
tokenExpirationTime = CvtDateTime(HardExpirationTime)
If eBayAuthToken = "" Or Now > tokenExpirationTime Then
'The eBay user must sign in to the eBay sandbox using the Sandbox user 1 account (TESTUSER_xxxxxx)
URL = "https://signin.sandbox.ebay.com/ws/eBayISAPI.dll?SignIn&RUName=" & RuName & "&SessID=" & Escape(SessionID)
Debug.Print URL
Set ebayAuth = New cEbayAuth
eBayAuthSuccessful = ebayAuth.GetEbayAuth(URL, eBayUsername)
If Not eBayAuthSuccessful Then
MsgBox "eBay username " & eBayUsername & " did not grant access to this application."
Exit Sub
End If
'http://developer.ebay.com/devzone/xml/docs/Reference/eBay/FetchToken.html
'FetchToken
'Use this call to retrieve an authentication token for a user. The call can be used to get a token only after the specified user has given
'consent for the token to be generated. Consent is given through the eBay sign-in page. After token retrieval, the token can be used to
'authenticate other calls made on behalf of the associated user.
'
'Unlike other Trading API calls, FetchToken requests do not require a user token for authorization. Instead, the call makes use of your
'application keys (App ID, Dev ID, and Cert ID) for authorization. In addition, you must supply a SessionID value, which you obtain by making
'a call to GetSessionID.
XMLrequest = "<!--?xml version='1.0' encoding='utf-8'?-->" & _
"<fetchtokenrequest xmlns="urn:ebay:apis:eBLBaseComponents">" & _
"<sessionid>" & SessionID & "</sessionid>" & _
"</fetchtokenrequest>"
'Send FetchTokenRequest
With httpReq
.Open "POST", endpoint, False
.setRequestHeader "X-EBAY-API-SITEID", "3"
.setRequestHeader "X-EBAY-API-COMPATIBILITY-LEVEL", "967"
.setRequestHeader "X-EBAY-API-CALL-NAME", "FetchToken"
.setRequestHeader "X-EBAY-API-APP-NAME", AppID
.setRequestHeader "X-EBAY-API-DEV-NAME", DevID
.setRequestHeader "X-EBAY-API-CERT-NAME", CertID
.setRequestHeader "Content-Type", "text/xml"
.setRequestHeader "Content-Length", Len(XMLrequest)
.send (XMLrequest) 'brackets are compulsory only for late binding of XMLhttp
Debug.Print .Status, .statusText
Debug.Print .getAllResponseHeaders
Debug.Print .responseText
Set XMLdoc = New MSXML2.DOMDocument60
XMLdoc.async = False
XMLdoc.validateOnParse = False
XMLdoc.setProperty "SelectionNamespaces", "xmlns:a='urn:ebay:apis:eBLBaseComponents'"
XMLdoc.LoadXML .responseText
End With
Debug.Print XMLdoc.XML
Set XMLresponseElement = XMLdoc.DocumentElement
'FetchTokenResponse
'
'
' string
' dateTime
'eBayAuthToken string
'The authentication token for the user.
'
'HardExpirationTime dateTime
'Date and time at which the token returned in eBayAuthToken expires and can no longer be used to authenticate the user for that application.
'Extract eBayAuthToken and HardExpirationTime from response and put in cells B1 and B2
Set XMLnode = XMLresponseElement.SelectSingleNode("/a:FetchTokenResponse/a:eBayAuthToken")
eBayAuthToken = XMLnode.nodeTypedValue
Debug.Print eBayAuthToken
Set XMLnode = XMLresponseElement.SelectSingleNode("/a:FetchTokenResponse/a:HardExpirationTime")
HardExpirationTime = XMLnode.nodeTypedValue
Debug.Print HardExpirationTime
Worksheets(1).Range("B1").Value = eBayAuthToken
Worksheets(1).Range("B2").Value = HardExpirationTime
End If
'http://developer.ebay.com/devzone/xml/docs/Reference/eBay/GetItem.html
'GetItem
'
'Use this call to retrieve the data for a single item listed on an eBay site. GetItem returns the data in an Item object.
'
'This call is conceptually similar to eBay's View item page. Data returned includes the item's title, description, price information,
'seller information, shipping details, high bidder information (if there currently is a high bidder), and so on.
XMLrequest = "<!--?xml version='1.0' encoding='utf-8'?-->" & _
"<getitemrequest xmlns="urn:ebay:apis:eBLBaseComponents">" & _
"<requestercredentials>" & _
"<ebayauthtoken>" & eBayAuthToken & "</ebayauthtoken>" & _
"</requestercredentials>" & _
"<itemid>" & ItemID & "</itemid>" & _
"</getitemrequest>"
'Send GetItemRequest
With httpReq
.Open "POST", endpoint, False
.setRequestHeader "X-EBAY-API-SITEID", "3"
.setRequestHeader "X-EBAY-API-COMPATIBILITY-LEVEL", "967"
.setRequestHeader "X-EBAY-API-CALL-NAME", "GetItem"
.setRequestHeader "X-EBAY-API-APP-NAME", AppID
.setRequestHeader "X-EBAY-API-DEV-NAME", DevID
.setRequestHeader "X-EBAY-API-CERT-NAME", CertID
.setRequestHeader "Content-Type", "text/xml"
.setRequestHeader "Content-Length", Len(XMLrequest)
.send (XMLrequest) 'brackets are compulsory only for late binding of XMLhttp
Debug.Print .Status, .statusText
Debug.Print .getAllResponseHeaders
Debug.Print .responseText
Set XMLdoc = New MSXML2.DOMDocument60
XMLdoc.async = False
XMLdoc.validateOnParse = False
XMLdoc.setProperty "SelectionNamespaces", "xmlns:a='urn:ebay:apis:eBLBaseComponents'"
XMLdoc.LoadXML .responseText
End With
Debug.Print XMLdoc.XML
Set XMLresponseElement = XMLdoc.DocumentElement
Set XMLnodeList = XMLresponseElement.SelectNodes("/a:GetItemResponse/a:Item/a:ListingDetails/a:*")
For Each XMLnode In XMLnodeList
Debug.Print XMLnode.nodeName, XMLnode.nodeTypedValue
Next
parseXML XMLresponseElement, 0
Set XMLnode = XMLresponseElement.SelectSingleNode("/a:GetItemResponse/a:Item/a:ListingDuration")
Debug.Print XMLnode.nodeTypedValue
Set XMLnode = XMLresponseElement.SelectSingleNode("/a:GetItemResponse/a:Item/a:ListingDetails/a:ConvertedStartPrice")
Debug.Print XMLnode.nodeTypedValue
Debug.Print XMLnode.Attributes.getNamedItem("currencyID").text
'http://developer.ebay.com/devzone/xml/docs/Reference/eBay/GetSellerTransactions.html
'GetSellerTransactions
'
'A seller will use this call to retrieve one or multiple recent order line items. This call can potentially return many order line items,
'depending on the recent sale activity of the authenticated user. Sales older than 90 days cannot be retrieved with this call.
XMLrequest = "<!--?xml version='1.0' encoding='utf-8'?-->" & _
"<getsellertransactionsrequest xmlns="urn:ebay:apis:eBLBaseComponents">" & _
"<requestercredentials>" & _
"<ebayauthtoken>" & eBayAuthToken & "</ebayauthtoken>" & _
"</requestercredentials>" & _
"</getsellertransactionsrequest>"
'Send GetSellerTransactionsRequest
With httpReq
.Open "POST", endpoint, False
.setRequestHeader "X-EBAY-API-SITEID", "3"
.setRequestHeader "X-EBAY-API-COMPATIBILITY-LEVEL", "967"
.setRequestHeader "X-EBAY-API-CALL-NAME", "GetSellerTransactions"
.setRequestHeader "X-EBAY-API-APP-NAME", AppID
.setRequestHeader "X-EBAY-API-DEV-NAME", DevID
.setRequestHeader "X-EBAY-API-CERT-NAME", CertID
.setRequestHeader "Content-Type", "text/xml"
.setRequestHeader "Content-Length", Len(XMLrequest)
.send (XMLrequest) 'brackets are compulsory only for late binding of XMLhttp
Debug.Print .Status, .statusText
Debug.Print .getAllResponseHeaders
Debug.Print .responseText
Set XMLdoc = New MSXML2.DOMDocument60
XMLdoc.async = False
XMLdoc.validateOnParse = False
XMLdoc.setProperty "SelectionNamespaces", "xmlns:a='urn:ebay:apis:eBLBaseComponents'"
XMLdoc.LoadXML .responseText
End With
Debug.Print XMLdoc.XML
Set XMLresponseElement = XMLdoc.DocumentElement
parseXML XMLresponseElement, 0
'Select all nodes within the node
Set XMLnodeList = XMLresponseElement.SelectNodes("/a:GetSellerTransactionsResponse/a:TransactionArray/a:Transaction/a:Buyer/a:*")
Debug.Print XMLnodeList.Length
For Each XMLnode In XMLnodeList
Debug.Print XMLnode.nodeName, XMLnode.nodeTypedValue
Next
'Select the Transaction node and output value of AmountPaid node and its currencyID attribute
Dim XMLresponseElementTransaction As IXMLDOMElement
Set XMLresponseElementTransaction = XMLresponseElement.SelectSingleNode("/a:GetSellerTransactionsResponse/a:TransactionArray/a:Transaction")
Set XMLnode = XMLresponseElementTransaction.SelectSingleNode("a:AmountPaid")
Debug.Print XMLnode.nodeName & " = " & XMLnode.nodeTypedValue; XMLnode.Attributes.getNamedItem("currencyID").text
'Select the Buyer node within the Transaction node
Dim XMLBuyerNode As IXMLDOMNode
Set XMLBuyerNode = XMLresponseElementTransaction.SelectSingleNode("a:Buyer")
Debug.Print XMLBuyerNode.ChildNodes.Length
For Each XMLnode In XMLBuyerNode.ChildNodes
Debug.Print XMLnode.nodeName, XMLnode.nodeTypedValue
Next
'Another way of accessing the Buyer node by specifying the full path
Set XMLresponseElementBuyer = XMLresponseElement.SelectSingleNode("/a:GetSellerTransactionsResponse/a:TransactionArray/a:Transaction/a:Buyer")
Debug.Print XMLresponseElementBuyer.ChildNodes.Length
Set XMLresponseElementBuyer = XMLresponseElementTransaction.SelectSingleNode("a:Buyer")
Debug.Print XMLresponseElementBuyer.ChildNodes.Length
'Dim XMLBuyerNodes As IXMLDOMNodeList
'Set XMLBuyerNodes = XMLresponseElementTransaction.SelectNodes("/a:*")
'Debug.Print XMLBuyerNodes.Length
For Each XMLnode In XMLresponseElementBuyer.ChildNodes
Debug.Print XMLnode.nodeName, XMLnode.nodeTypedValue
Next
'Access specific nodes within the Buyer node
Set XMLnode = XMLresponseElementBuyer.SelectSingleNode("a:Email")
Debug.Print XMLnode.nodeName & " = " & XMLnode.nodeTypedValue
Set XMLnode = XMLresponseElementBuyer.SelectSingleNode("a:UserID")
Debug.Print XMLnode.nodeName & " = " & XMLnode.nodeTypedValue
Set XMLnode = XMLresponseElementBuyer.SelectSingleNode("a:UserFirstName")
Debug.Print XMLnode.nodeName & " = " & XMLnode.nodeTypedValue
Set XMLnode = XMLresponseElementBuyer.SelectSingleNode("a:UserLastName")
Debug.Print XMLnode.nodeName & " = " & XMLnode.nodeTypedValue
' TransactionArray
'Transaction
' AmountPaid 27.2 currencyID=GBP
'BuyerUserID GetDispute.Dispute
'GetOrders.OrderArray.Order
'GetOrderTransactions.OrderArray.Order
'PayPal Express Checkout Payment Sent (Unique Transaction ID3L3xxxxxxxx733433G)
Dim OrderLineItemID As String
OrderLineItemID = ItemID & "-" & "3L3xxxxxxx733433G"
XMLrequest = "<!--?xml version='1.0' encoding='utf-8'?-->" & _
"<getordersrequest xmlns="urn:ebay:apis:eBLBaseComponents">" & _
"<orderidarray>" & _
"<orderid>" & OrderLineItemID & "</orderid>" & _
"</orderidarray>" & _
"</getordersrequest>"
With httpReq
.Open "POST", endpoint, False
.setRequestHeader "X-EBAY-API-SITEID", "3"
.setRequestHeader "X-EBAY-API-COMPATIBILITY-LEVEL", "967"
.setRequestHeader "X-EBAY-API-CALL-NAME", "GetOrders"
.setRequestHeader "X-EBAY-API-APP-NAME", AppID
.setRequestHeader "X-EBAY-API-DEV-NAME", DevID
.setRequestHeader "X-EBAY-API-CERT-NAME", CertID
.setRequestHeader "Content-Type", "text/xml"
.setRequestHeader "Content-Length", Len(XMLrequest)
.send (XMLrequest) 'brackets are compulsory only for late binding of XMLhttp
Debug.Print .Status, .statusText
Debug.Print .getAllResponseHeaders
Debug.Print .responseText
Set XMLdoc = New MSXML2.DOMDocument60
XMLdoc.async = False
XMLdoc.validateOnParse = False
XMLdoc.setProperty "SelectionNamespaces", "xmlns:a='urn:ebay:apis:eBLBaseComponents'"
XMLdoc.LoadXML .responseText
End With
Debug.Print XMLdoc.XML
End Sub
'http://www.tushar-mehta.com/publish_train/xl_vba_cases/vba_web_pages_services/
'With bug fix. The "%" should be first in the BadChars string because it is used as the escape character.
Private Function Escape(ByVal param As String) As String
Dim i As Integer, BadChars As String
BadChars = "%<>=&!@#$^()+{[}]|\;:'"",/?"
'BadChars = "<>%=&!@#$^()+{[}]|\;:'"",/?" 'original code
For i = 1 To Len(BadChars)
param = Replace(param, Mid(BadChars, i, 1), "%" & Hex(Asc(Mid(BadChars, i, 1))))
Next
param = Replace(param, " ", "+")
Escape = param
End Function
'Scan XML DOM tree recursively and output node names and values to Immediate window
Private Sub parseXML(node As IXMLDOMNode, Optional level As Integer)
Dim i As Long
If node.NodeType = NODE_ELEMENT Then
Debug.Print
Debug.Print String(level * 2, " ") & node.nodeName;
ElseIf node.NodeType = NODE_TEXT Then
Debug.Print " " & node.NodeValue;
For i = 0 To node.ParentNode.Attributes.Length - 1
Debug.Print " "; node.ParentNode.Attributes(i).BaseName & "=" & node.ParentNode.Attributes(i).text;
Next
End If
For i = 0 To node.ChildNodes.Length - 1
parseXML node.ChildNodes(i), level + 1
Next
End Sub
'http://developer.ebay.com/devzone/xml/docs/Reference/eBay/types/simpleTypes.html#dateTime
'dateTime
'
'A specific instant of time.
'
'Date-time values are in the ISO 8601 date and time format. Hours are in 24-hour format (e.g., 2:00:00pm is 14:00:00). Unless otherwise
'specified, all date-time values are recorded in Universal Coordinated Time (UTC), also known as Greenwich Mean Time (GMT) or Zulu (because
'the time portion of the time stamp ends with a Z). That is, time values do not reflect the time zone shown on the eBay Web site. Here is an
'example of the date-time format:
'
'YYYY-MM-DDTHH:MM:SS.SSSZ (e.g., 2004-08-04T19:09:02.768Z)
'
'See Time Values for information about how to convert between local time zones and GMT.
'http://developer.ebay.com/devzone/guides/ebayfeatures/Basics/DataTypes.html#TimeValues
'
'We use the dateTime data type to convey start and end times, the official eBay time, and other time values.
'
'xs:dateTime is part of the XML schema namespace defined as:
'
'xmlns: xs = "http://www.w3.org/2001/XMLSchema"
Private Function CvtDateTime(eBayDateTime As String) As Date
'Convert eBay dateTime string to an Excel Date variable
'2018-02-14T22:56:01.000Z
If eBayDateTime <> "" Then
CvtDateTime = DateSerial(Mid(eBayDateTime, 1, 4), Mid(eBayDateTime, 6, 2), Mid(eBayDateTime, 9, 2)) + TimeValue(Mid(eBayDateTime, 12, 8))
Else
CvtDateTime = 0
End If
End Function
Upvote
0
