1

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.

K.Dᴀᴠɪs
  • 9,945
  • 11
  • 33
  • 43
anjulis
  • 219
  • 1
  • 4
  • 14

2 Answers2

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.

Greg
  • 11,302
  • 2
  • 48
  • 79
  • Gregg, thank you for quick reply. Your solution did not work. First, I got an error that Open should be before setRequestHeader. After I moved it, I got the same error: Connection with the server was terminated abnormally – anjulis Feb 01 '18 at 18:46
  • @anjulis If you download Fiddler, can you see what the request says? Or post the data in Postman. Does it return successfully or does the code error also? – Greg Feb 01 '18 at 18:51
  • I can run it in Postman or in C#. Only VBA has problem with the server. – anjulis Feb 01 '18 at 21:10
0

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

Moe
  • 991
  • 2
  • 10
  • 24