POST to JSON using VBA

Question

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

Have you tried adding your username & password to the sURL as parameters? (Without doing this manually, I can't say how this should be specified but probably as ? parameters on the end of the URL string e.g., ?LoginName=****&Password=****). — David Zemens
– David Zemens, Commented Mar 29, 2018 at 17:51

drec4s · Accepted Answer · 2018-03-29 18:16:29Z

1

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.

edited Mar 29, 2018 at 18:16

answered Mar 29, 2018 at 17:54

drec4s

8,1178 gold badges35 silver badges56 bronze badges

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

2 Comments

Tyler Dawson Over a year ago

Yes, that's exactly what my string looks like. I removed the quotes around LoginName and Password at one point to troubleshoot the error, but I get the same response with or without them.

drec4s Over a year ago

Well, if that is the case, what do you mean when you say that pasting that url to a browser worked? Are you making a POST via VBA when you should be making a GET request?

Collectives™ on Stack Overflow

POST to JSON using VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related