Solved

send xml to amazon using vba

Posted on 2013-11-06
14
1,061 Views
Last Modified: 2013-11-12
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>
0
Comment
Question by:eg252
  • 6
  • 6
  • 2
14 Comments
 
LVL 57
ID: 39627855
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
 
LVL 1

Author Comment

by:eg252
ID: 39627954
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
 
LVL 21
ID: 39628250
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 57
ID: 39628449
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
 
LVL 1

Author Comment

by:eg252
ID: 39628550
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
 
LVL 57
ID: 39628652
<<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
 
LVL 1

Author Comment

by:eg252
ID: 39628676
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
 
LVL 1

Author Comment

by:eg252
ID: 39629372
0
 
LVL 57
ID: 39630258
For the XML that you posted, does that match the entire string that Scratch pad returned as the "String to sign" value?

Jim.
0
 
LVL 57
ID: 39630345
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
 
LVL 1

Accepted Solution

by:
eg252 earned 0 total points
ID: 39630387
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
 
LVL 57
ID: 39630512
Very nice...glad to here you've got it working.

Thanks for coming back and posting that.

Jim.
0
 
LVL 21
ID: 39631474
Thanks for sharing your solution!
0
 
LVL 1

Author Closing Comment

by:eg252
ID: 39641070
this works
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access 2016 7 33
Programmer 14 48
Delete Records on a Form in Microsoft Access 5 39
XML Document XPath with Namespaces 3 21
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question