CODEWITHSUNDEEP
jsonvba
Tyler Dawson
Tyler Dawson

Reputation: 3

POST to JSON using VBA

I created a function to post to an application using VBA tied to an Excel workbook to create many records at once. So far I can't get a successful post of an authentication method before moving forward with record creation. Here's what I have so far.

Dim Sheet As Worksheet
Dim Row As Integer
Dim sURL As String
Dim sContent As String
Dim oHttp As Object
Dim rContent

Set Sheet = Sheets("APISend")
Set oHttp = CreateObject("MSXML2.ServerXMLHTTP")
sURL = "https://cityworks.cityofdenton.com:9000/Cityworks/services/AMS/Authentication/Authenticate/"

sContent = Sheet.Range("C1").Value
oHttp.Open "POST", sURL, False
oHttp.send (sContent)
rContent = oHttp.ResponseText
Sheet.Range("D1").Value = rContent
Sheet.Range("E1").Value = sContent
Set Object = Nothing

The value in C1 is: {LoginName:"*****",Password:"*****"}

I receive the response: {"Value":false,"Status":1,"Message":"LoginName not populated."}

The following URL string works when pasted into a browser: https://cityworks.cityofdenton.com:9000/Cityworks/services/AMS/Authentication/Authenticate/?data={"LoginName":"*****","Password":"*****"}

I'm new to JSON, so I'm sure I'm missing something simple. I've been all over message boards trying what I see, but no luck so far. Can someone provide the solution?

Thank you! Tyler

EDIT: Here's the solution. For my API, I had to include everything in the sURL variable without using sContent as the Body input. Code copied below.

Set Sheet = Sheets("APISend")
Set oHttp = CreateObject("MSXML2.ServerXMLHTTP")
sContent = Sheet.Range("C1").Value
sURL = "https://cityworks.cityofdenton.com:9000/Cityworks/services/AMS/Authentication/Authenticate/?data=" & sContent

sContent = Sheet.Range("C1").Value
oHttp.Open "POST", sURL, False
oHttp.send sURL
rContent = oHttp.ResponseText
Sheet.Range("D1").Value = rContent
Sheet.Range("E1").Value = sContent

Upvotes: 0

Views: 124

Answers (1)

drec4s
drec4s

Reputation: 8077

I believe you are not sending a valid JSON string:

Make sure the string inside Range("C1") has LoginName and Password between quotes like this:

{"LoginName":"*****", "Password":"*****"}

EDIT

After inspecting that URL I can say that you need to pass your data inside a querystring /?data={"LoginName":"*****","Password":"*****"} for the backend to authenticate. Either a POST or GET reproduce the same results.

However, if you pass your credentials as the request body, you will get a "LoginName not populated." error.

Upvotes: 1

Related Questions