Amazon SP-API to MS Accesse VBA (1 Viewer)

[Georg_AMZ]

Hi,

I´m using MS Access (2010 VBA) to pull order-data from Amazon MWS API.

Now I have to change to the new SP-API. Is there someone who found a way to pull data from Amazon with Access or Excel via VBA?

Thx Georg

 

[theDBguy]

Hi. Welcome to AWF!

I have used Amazon API before but not sure which version it was. Can you please post a link to the "new" API documentation?

 

[Georg_AMZ]

Old: Amazon Marketplace Web Service (MWS) I think XLM based
New: Selling-Partner API (SP-API) I think JSON based

 

[theDBguy]

Old: Amazon Marketplace Web Service (MWS) I think XLM based
New: Selling-Partner API (SP-API) I think JSON based

So, if it's just a matter of switching from xml to json, then that shouldn't be a problem. I have a couple of tools to handle json files.

 

[Georg_AMZ]

I think it's not just the switch from xml to json that worries me less.
It's about the way of retrieval and authentication is a whole new system.
Maybe someone already has experience connecting Access or Excel with the SP API and can provide input or even post code here.

 

[theDBguy]

I think it's not just the switch from xml to json that worries me less.
It's about the way of retrieval and authentication is a whole new system.
Maybe someone already has experience connecting Access or Excel with the SP API and can provide input or even post code here.

Do you have a link to the API documentation?

 

[theDBguy]

Here's a link to the code I've used before.

VBA API call standards

Hello MS Access Expert, I would like to know if it is possible to make API calls via VBA that meet the below standards: Ability to adhere to TLS 1.2 encryption for HTTPS connection Ability to support OAuth 2.0 JSON Web Token (JWT) Bearer Flow I have a MS Access system that has been in...

www.access-programmers.co.uk

 

[Georg_AMZ]

I think everything could be found here, but I can´t find my information to start with....

developer-docs.amazon.com/sp-api

can´t insert the link (SPAM Warning)

 

[theDBguy]

I think everything could be found here, but I can´t find my information to start with....

developer-docs.amazon.com/sp-api

can´t insert the link (SPAM Warning)

Okay thanks, I'll take a look. Have you had a chance to see the code I posted? Is it the same as what you're using now?

 

[Georg_AMZ]

I saw the code, yes. We had a Freelancer to solve the problem final, and as i can see it´s prety the same code. But we had to make the code more complex to calculate the Signature for the Request.
I think in the new API there is a new way for the authentification and security

 

[peskywinnets]

I have the same problem...I coded up my own VBA/Access based solution for the old MWS API, but the signing aspect is excessively complicated for the new SP-API (with little information on the web how to accomplish this in VBA)...so I'm facing being cut-off from retrieving Amazon data via API soon.

I just need the signing part solved (I'm quite ok, parsing the new JSON data format vs the old XML)

Out of interest, how much did it cost to have a Freelancer solve this for you?

 

[Georg_AMZ]

I have the same problem...I coded up my own VBA/Access based solution for the old MWS API, but the signing aspect is excessively complicated for the new SP-API (with little information on the web how to accomplish this in VBA)...so I'm facing being cut-off from retrieving Amazon data via API soon.

I just need the signing part solved (I'm quite ok, parsing the new JSON data format vs the old XML)

Out of interest, how much did it cost to have a Freelancer solve this for you?

I found a way with a Freelancer from "Fiverr" his Name ist "mukeshpiplai123". Made a good job and very fast! For 315$

I will ask him for a second step to upload invoices by FBA, he told me it will be about additional 200$

I think it´s a good price for his help, i hope it will work for the next years.... ;-)

 

[theDBguy]

But we had to make the code more complex to calculate the Signature for the Request.

Hmm, somehow I missed your last post. In the code I posted earlier, there was this line.

strSignature = URLEncode(CreateSignature(strQueryToSign, SECRET_KEY))

I wonder if the "complex" signature you're talking about is similar to my CreateSignature() function.

Just curious...

 

[peskywinnets]

With their old MWS API (XML based, which is now going end of life) I managed to get the signing/encoding working (albeit, it took me a while as there is scant information for using VBA for making API calls), with this new SP-API (I think the SP stands for selling partner...its REST/JSON type), the encoding process is on another level!

Connecting to the Selling Partner API

How to connect to the SP-API.

developer-docs.amazon.com

...it's because a URI has to be constructed in a very rigid (obtuse!) Way.... then signed, If you don't construct the URI correctly then then sign it, Amazon servers will reject. If you manage to construct the URI correctly but don't do the signing correctly then Amazon servers will reject(!), but it's not clear when you fail why you are failing (was it the URI at fault...or the signing?) Frustrating Brainache!


(@theDBguy ..alas, that line is not the solution). Amazon provide plenty of examples for their new SP-API in python and even VB.net but as is the usual case, you're on your own with VBA! (I've never coded in VB.net and thought - with it being similar - I could pick the bones out of their examples and apply it to VBA but I failed!

(Some VB.net examples for the new Amazon SP-API....
https://www.example-code.com/vbnet/sp_api.asp )

I must have spent at least six weeks of evenings trying to get the signing/encoding to work....but nope, no workee.

So, I must relent and accept....it's beyond my abilities...so would gladly now pay someone to save me a lot more brainache now that I'm forced to go the way of their new SP-API.

 

[MsAccessNL]

Do you know it there is an example/link with JavaScript?

 

[peskywinnets]

Do you know it there is an example/link with JavaScript?

At the foot of this page are some examples in various languages to do with the signing ...

Create a signed AWS API request - AWS Identity and Access Management

The following is an overview of the process to create a signed request. To calculate a signature, you first need a string to sign. You then calculate a HMAC-SHA256 hash of the string to sign by using a signing key. The following diagram illustrates the process, including the various components...

docs.aws.amazon.com

 

[ComputerVersteher]

Amazon provide plenty of examples for their new SP-API in python and even VB.net but as is the usual case, you're on your own with VBA!

So why not use them via VBA? E.g. you can run js, php and python code by using the command line via VBA. That should simplify signing/encoding as you can use their sdk.

As you need webrequest, you should be aware of VBA-Web, what simplifies using request in VBA. If you open an issue there, maybe someone creates an authenticator for amazon.

 

[peskywinnets]

So why not use them via VBA? E.g. you can run js, php and python code by using the command line via VBA. That should simplify signing/encoding as you can use their sdk.

Because I'm out my depth!

I've just found a post where someone seems to have made a successful Amazon API call using Version 4 signing (which is where I came unstruck)...

Create a signed AWS API request to a Lambda Function URL from Excel (VBA)

I have an AWS Lambda function with a function URL. Its auth type is AWS_IAM and I have credentials. Currently, this Lambda takes two numbers as input and returns their sum (placeholder for future

stackoverflow.com

...so will dust myself down & try and adapt that to my needs.

once more into the breach...