send xml to amazon using vba

Does anyone know how to use this xml? I want to use vba to update amazon with a tracking number.. I found this - and I registered with amazon - but i'm pretty clueless as to how to proceed...

<?xml version="1.0" encoding="UTF-8"?>
<AmazonEnvelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="amznenvelope.
xsd">
<Header>
<DocumentVersion>1.01</DocumentVersion>
<MerchantIdentifier>My Store</MerchantIdentifier>
</Header>
<MessageType>OrderFulfillment</MessageType>
<Message>
<MessageID>1</MessageID>
<OrderFulfillment>
<MerchantOrderID>1234567</MerchantOrderID>
<MerchantFulfillmentID>1234567</MerchantFulfillmentID>
<FulfillmentDate>2002-05-01T15:36:33-08:00</FulfillmentDate>
<FulfillmentData>
<CarrierCode>UPS</CarrierCode>
<ShippingMethod>Second Day</ShippingMethod>
<ShipperTrackingNumber>1234567890</ShipperTrackingNumber>
</FulfillmentData>
<Item>
<MerchantOrderItemID>1234567</MerchantOrderItemID>
<MerchantFulfillmentItemID>1234567</MerchantFulfillmentItemID>
<Quantity>2</Quantity>
</Item>
</OrderFulfillment>
LVL 1
eg252Asked:
Who is Participating?
 
eg252Connect With a Mentor Author Commented:
I'm posting this for the benefit of the community. I spent over 10 long hard hours on this - and I'm sure there are those that can GREATLY benefit from this.


Option Compare Database

Private Declare Sub GetSystemTime Lib "kernel32.dll" (lpSystemTime As SYSTEMTIME)

Private Type SYSTEMTIME
    wYear As Integer
    wMonth As Integer
    wDayOfWeek As Integer
    wDay As Integer
    wHour As Integer
    wMinute As Integer
    wSecond As Integer
    wMilliseconds As Integer
End Type


Public Function GetIsoTimestamp() As String
Dim st As SYSTEMTIME
 
'Get the local date and time
GetSystemTime st

'Format the result
GetIsoTimestamp = _
    Format$(st.wYear, "0000") & "-" & _
    Format$(st.wMonth, "00") & "-" & _
    Format$(st.wDay, "00") & "T" & _
    Format$(st.wHour, "00") & "%3A" & _
    Format$(st.wMinute, "00") & "%3A" & _
    Format$(st.wSecond, "00") & "Z"
End Function

Public Sub setAmazon(a)

Dim mydb As Database, ars As Recordset, co As Integer
Set ars = Me.RecordsetClone
For co = 1 To ars.RecordCount

Dim strtosign As String
strtosign ="AWSAccessKeyId=**your key**&Action=SubmitFeed&FeedType=_POST_ORDER_FULFILLMENT_DATA_&MarketplaceIdList.Id.1=ATVPDKIKX0DER&Merchant=**your merchant**&PurgeAndReplace=false&SignatureMethod=HmacSHA256&SignatureVersion=2&Timestamp=" & GetIsoTimestamp & "&Version=2009-01-01"

Dim fxml As String
fxml = "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & "UTF-8" & Chr(34) & "?>"
fxml = fxml & "<AmazonEnvelope xmlns:xsi=" & Chr(34) & "http://www.w3.org/2001/XMLSchema-instance" & Chr(34) & "xsi:noNamespaceSchemaLocation=" & Chr(34) & "amzn-envelope.xsd" & Chr(34) & " >"
fxml = fxml & "<Header><DocumentVersion>1.01</DocumentVersion><MerchantIdentifier>My Store</MerchantIdentifier></Header>"
fxml = fxml & "<MessageType>OrderFulfillment</MessageType><Message><MessageID>1</MessageID><OrderFulfillment><MerchantOrderID>" & ars!ID & "</MerchantOrderID><AmazonOrderID>" & ars![order-id] & "</AmazonOrderID>"
fxml = fxml & "<FulfillmentDate>" & GetIsoTimestamp & "</FulfillmentDate><FulfillmentData><CarrierCode>USPS</CarrierCode><ShippingMethod>First Class</ShippingMethod><ShipperTrackingNumber>" & ars![TrackingNumber] & "</ShipperTrackingNumber></FulfillmentData>"
fxml = fxml & "<Item><MerchantFulfillmentItemID>" & ars![order-item-id] & "</MerchantFulfillmentItemID><Quantity>" & ars![quantity-purchased] & "</Quantity></Item></OrderFulfillment></Message></AmazonEnvelope>"
   
Dim crypt As New ChilkatCrypt2
success = crypt.UnlockComponent("your key")
If (success <> 1) Then
    MsgBox crypt.LastErrorText
    Exit Sub
End If
crypt.HashAlgorithm = "md5"
crypt.EncodingMode = "base64"
crypt.Charset = "utf-8"
Dim md5Hash As String
md5Hash = crypt.HashStringENC(fxml)
crypt.HashAlgorithm = "sha256"
Dim AWSSecretAccessKey As String
AWSSecretAccessKey = "Your key"
crypt.SetHmacKeyString AWSSecretAccessKey
crypt.Charset = "utf-8"
crypt.EncodingMode = "base64"
Dim signature As String
signature = crypt.HmacStringENC("POST" & Chr(10) & "mws.amazonservices.com"

& Chr(10) & "/" & Chr(10) & strtosign)
Stop
signature = Replace(signature, "=", "%3D") 'URL encode
signature = Replace(signature, "+", "%2B") 'URL encode

Dim http As New MSXML2.XMLHTTP
    With http
        .Open "POST", "https://mws.amazonservices.com/?" & strtosign & "&Signature=" & signature, False
        .setRequestHeader "Content-Type", "text/xml"
        .setRequestHeader "Content-MD5", md5Hash
        .send fxml
s = .responseText
End With
End Sub
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Amazon should have some docs/examples on the way in which they want you to talk to the system.   Normally, you use SOAP or WDSL, and then talk to the web site with RESTful or simply the http POST/GET (example below).

What is it exactly your trying to do?  I should be able to get you going in the right direction at least.

Jim.

                  ' Set the correct URL
                  'strPostURL = "https://test.authorize.net/gateway/transact.dll"
110               strPostURL = "https://secure.authorize.net/gateway/transact.dll"
                  'strPostURL = "https://developer.authorize.net/tools/paramdump/index.php"

120               strPostSting = ""
125        strPostSting = strPostSting & "x_login=" & URLEncode(strAPILogin) & "&"
126       strPostSting = strPostSting & "x_tran_key=" & URLEncode(strTransactionKey) & "&"
                  'For debugging.
                  'strPostSting = strPostSting & "x_test_request=" & URLEncode("TRUE") & "&"
130               strPostSting = strPostSting & "x_version=" & URLEncode("3.1") & "&"
140               strPostSting = strPostSting & "x_delim_data=" & URLEncode("TRUE") & "&"
150               strPostSting = strPostSting & "x_delim_char=" & URLEncode("|") & "&"
160               strPostSting = strPostSting & "x_relay_response=" & URLEncode("FALSE") & "&"
170               strPostSting = strPostSting & "x_email_customer=" & URLEncode("FALSE") & "&"

180               strPostSting = strPostSting & "x_type=" & URLEncode("PRIOR_AUTH_CAPTURE") & "&"
190               strPostSting = strPostSting & "x_trans_id=" & URLEncode(rs!CCTransactionID) & "&"

                  ' Additional fields can be added here as outlined in the AIM integration
                  ' guide at: http://developer.authorize.net
200               strPostSting = left(strPostSting, Len(strPostSting) - 1)

                  ' We use xmlHTTP to submit the input values and record the response
                  Dim objRequest As New MSXML2.XMLHTTP
210               objRequest.Open "POST", strPostURL, False
220               objRequest.Send strPostSting
230               strPostResponse = objRequest.responseText
                  'Debug.Print strPostResponse
240               Set objRequest = Nothing
0
 
eg252Author Commented:
Thank you for responding.
i need very specifically AMAZON xml help..
I need to know if anyone did Amazon XML in vba..  I need the url of where to post and a small sample so I can get started..
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Have you looked at Amazon's documentation here:

Amazon Marketplace Web Service (Amazon MWS) Documentation
 https://developer.amazonservices.com/gp/mws/docs.html/189-7490411-2657469

and also:

Amazon MWS Scratchpad
https://mws.amazonservices.com/scratchpad/index.html
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Are you using the Marketplace Web Service or doing something else with Amazon?

XML is just the data that get's sent received, not how.   You could for example be working with EDI and XML.

That's why I asked exactly what it was you were doing.

So far, we don't have enough info to help you.

Jim.
0
 
eg252Author Commented:
All I'm tryring to do is to update Amazon with a tracking number for an order.. I'm working on this for hours and hours.. I finally got to the point where I figured out signatures and I got the xml and url.. Now I'm stuck because the md5 doesnt match.. and I'm trying to figure out the md5. I will post the code when I'm done - but right now I want to STILL know if anyone has successfully updated amazon with a tracking number from access...
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<but right now I want to STILL know if anyone has successfully updated amazon with a tracking number from access... >>

 There's no reason you can't and it seems what HiTechCoach pointed you to is what your working with.   I saw a section in there on MD5 and what to do.

 I also have MD5 code here somewhere...

 Jim.
0
 
eg252Author Commented:
thank you. i saw the scratchpad AND the documentation page. I tried 5 or 6 md5 modules and still cannot match..
I'm attaching my xml - this is the md5 that Amazon wants..

XadsJ9m+KfWBFE5TygQGdg==

All the modules I tried did NOT get that answer....

Thank you all for trying!
testing.txt
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
For the XML that you posted, does that match the entire string that Scratch pad returned as the "String to sign" value?

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
The digest I get on your XML is:

8c12b32fe838087cec694733327546c8

 so I'm thinking what Amazon is calculating on is not what you posted.   So far, I haven't known this MD5 code to have a problem (haven't used it a ton though).

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Very nice...glad to here you've got it working.

Thanks for coming back and posting that.

Jim.
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Thanks for sharing your solution!
0
 
eg252Author Commented:
this works
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.