Solved

send xml to amazon using vba

Posted on 2013-11-06
14
976 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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
Comment Utility
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:eg252
Comment Utility
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
Comment Utility
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Very nice...glad to here you've got it working.

Thanks for coming back and posting that.

Jim.
0
 
LVL 21
Comment Utility
Thanks for sharing your solution!
0
 
LVL 1

Author Closing Comment

by:eg252
Comment Utility
this works
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Browsing the questions asked to the Experts of this forum, you will be amazed to see how many times people are headaching about monster regular expressions (regex) to select that specific part of some HTML or XML file they want to extract. The examp…
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.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now