Calling Rest API from VBA - “Connection with the s

问题:

I am trying to RESTfull API from Excel VBA. I already have a working version in C#:

//Request Auth Token
var client = new RestClient("https://api.xxx.com/exp/oauth2/v1/access_token_cors");
var request = new RestRequest(Method.POST);
request.AddHeader("Content-Type", "application/x-www-form-urlencoded");
request.AddParameter("application/x-www-form-urlencoded", "response_type=token&grant_type=client_credentials&client_id=1234&client_secret=1234&scope=", ParameterType.RequestBody);
IRestResponse response = client.Execute(request);

Need to port this code to VBA. I wrote:

Set MyRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
MyRequest.Open "POST", "https://api.xxx.com/exp/oauth2/v1/access_token_cors", False
MyRequest.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
PostData = """application/x-www-form-urlencoded"", ""response_type=token&grant_type=client_credentials&client_id=1234&client_secret=1234&scope="""
MyRequest.send (PostData)

When I run the VBA version I get an error "Connection with the server was terminated abnormally" on .Send line

Since it works in C# it cannot be a firewall or server problems. What can I do to get it working? I have searched for similar questions but none there are applicable to my situation.

回答1:

You should be able to do the following:

Set request = CreateObject("WinHttp.WinHttpRequest.5.1")
request.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
request.Open "POST", URL, False 'Your missing the actual url
request.Option(4) 'Ignore SSL Errors.
request.Option(12) 'Allow redirect to SSL
request.send("response_type=token&grant_type=client_credentials&client_id=1234&client_secret=1234&scope=")

I assume the issue is, you don't have a defined URL. More than likely the api could be SSL, so you should account for that, also why create a PostData when you can input the string. You also have a lot of quotation marks, I assume you're doing that to correctly send them, I believe that is off. The above should work for you.

回答2:

In case you have this issue on windows 7 or 8, it might be related to VBA sending data packets with SSL protocol which gets dropped by servers only accepting TLS. In that case, You will need to apply a 2-step patch/update to fix this for windows 7,

Step 1. Get Microsoft Update: Download relevant (32-bits or 64-bits of user's Windows version) Microsoft Security Protocol Update and install if not already install.

Step 2. Download Microsoft Easy Fix: Download Microsoft “Easy Fix” from Microsoft Support Article, and execute to set TLS 1.1+ as default.

Source : Update to enable TLS 1.1 and TLS 1.2 as default secure protocols in WinHTTP in Windows