Amazon SP-API to MS Accesse VBA
- Thread starter Georg_AMZ
- Start date
- Local time
- Yesterday, 22:49
- Joined
- Oct 29, 2018
- Messages
- 22,062
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.
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.
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.
- Local time
- Yesterday, 22:49
- Joined
- Oct 29, 2018
- Messages
- 22,062
Do you have a link to the API documentation?
- Local time
- Yesterday, 22:49
- Joined
- Oct 29, 2018
- Messages
- 22,062
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
- Local time
- Yesterday, 22:49
- Joined
- Oct 29, 2018
- Messages
- 22,062
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?
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
I think in the new API there is a new way for the authentification and security
peskywinnets
Registered User.
- Local time
- Today, 06:49
- Joined
- Feb 4, 2014
- Messages
- 578
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 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.... ;-)
- Local time
- Yesterday, 22:49
- Joined
- Oct 29, 2018
- Messages
- 22,062
Hmm, somehow I missed your last post. In the code I posted earlier, there was this line.
Code:
strSignature = URLEncode(CreateSignature(strQueryToSign, SECRET_KEY))Just curious...
peskywinnets
Registered User.
- Local time
- Today, 06:49
- Joined
- Feb 4, 2014
- Messages
- 578
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!
...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.
...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.
Last edited:
MsAccessNL
Member
- Local time
- Today, 07:49
- Joined
- Aug 27, 2022
- Messages
- 195
Do you know it there is an example/link with JavaScript?
peskywinnets
Registered User.
- Local time
- Today, 06:49
- Joined
- Feb 4, 2014
- Messages
- 578
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
Learn how to use the AWS SigV4 signing protocol to create a signed request for AWS API requests. You’ll learn how to create and hash a canonical request, create a string to sign, derive a signing key, and calculate a signature to add to the request.
docs.aws.amazon.com
ComputerVersteher
Member
- Local time
- Today, 07:49
- Joined
- Feb 27, 2023
- Messages
- 59
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.
Last edited:
peskywinnets
Registered User.
- Local time
- Today, 06:49
- Joined
- Feb 4, 2014
- Messages
- 578
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...
