Access API through VBA in Excel

Question

I need log in to this API: http://demo-api.primary.com.ar:8081/pbcp/rest/users/login

There is a quickstart guide with all information, but I never used those instructions and I don't know how perform a macro in Excel to connect and then get market data

I tried different methods in Excel but I can't make it work. My last attempt was to send through WinHttp.WinHttpRequest.5.1 headers like express in cUrl but the errors are systematical.

Here is my code, which is a compilation of different bits of code extracted from the web.

curl -X POST --header 'Content-Type: application/json' --header 'Accept: application/json' -d '{
  "username": "Demo-User",
  "password": "ApiEx@mpl3"
}' 'http://demo-api.primary.com.ar:8081/pbcp/rest/users/login'

My code:

Sub prueba()

Dim oRequest As Object
Set oRequest = CreateObject("WinHttp.WinHttpRequest.5.1")

Dim h1 As String
Dim h2 As String

oRequest.Open "POST", "http://demo-api.primary.com.ar:8081/pbcp/rest/users/login"
oRequest.SetRequestHeader "Content-Type", "application/json", "Accept", "application/json" ''Content-Type: application/json'

Dim s As String
s = Chr(34) & "username" & Chr(34) & ":" & Chr(34) & "Demo-User" & Chr(34) & "," & Chr(34) & "password" & Chr(34) & ":" & Chr(34) & "ApiEx@mpl3" & Chr(34)

oRequest.Send s
MsgBox oRequest.ResponseText
End Sub


'Dim oRequest As Object
'Set oRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
'oRequest.Open "POST", "http://demo-api.primary.com.ar:8081/pbcp/rest/users/login"
'oRequest.SetRequestHeader "Content-Typ", "application/x-www-form-urlencoded"
'oRequest.Send "var1=123&anothervar=test"
'MsgBox oRequest.ResponseText

Alex K. · Accepted Answer · 2017-06-21 13:54:30Z

1

You need to call SetRequestHeader for each header and value, you cant combine them
The request is JSON but you omit the {}

oRequest.Open "POST", "http://demo-api.primary.com.ar:8081/pbcp/rest/users/login"
oRequest.SetRequestHeader "Content-Type", "application/json"
oRequest.SetRequestHeader "Accept", "application/json"

Dim s As String
s = "{" & Chr(34) & "username" & Chr(34) & ":" & Chr(34) & "Demo-User" & Chr(34) & "," & Chr(34) & "password" & Chr(34) & ":" & Chr(34) & "ApiEx@mpl3" & Chr(34) & "}"

answered Jun 21, 2017 at 13:54

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nico Ditu Over a year ago

+1 In first place you are a genius! Thank you very much. I tried hard but i couldn't achieve it whitout your assistence. Now I gonna try to get data :) I hope have success

Collectives™ on Stack Overflow

Access API through VBA in Excel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related