Microsoft Excel VBA JSON POST Method

API & Customization
1

I'm looking for some VBA code to upload data filled out in an Excel spreadsheet to create a record in a Knack object. I've had a crack below and come up with a time out error. Any thoughts?

Sub PostJSON()

  Dim URL As String
  Dim JSONString As String
  Dim objHTTP As New WinHttpRequest
 
  URL = "https://api.knack.com/v1/objects/object_1/records"
  objHTTP.Open "POST", URL, False
  objHTTP.SetRequestHeader "X-Knack-Application-Id", "######"
  objHTTP.SetRequestHeader "X-Knack-REST-API-KEY", "######"
  objHTTP.SetRequestHeader "Content-Type", "application/json"

  JSONString = "{""field_1"":""1 Smith St, Smithville""}"
  objHTTP.Send JSONString

End Sub

2

Thanks for the reply Brad. Unfortunately my code is just fine how it is. I just tested the code from a laptop outside my company's network and it successfully imported the record.
Seems like a network security issue to me. :(

Any way I can get around this and still operate within the network?

3

Hi Stephen - It's been a while but I remember it being very finicky using VBA WinHttpRequests - try single quotes and make sure the address field is formatted correctly (see https://www.knack.com/developer-documentation/#object-based-post for an example with the address field)

JSONString = "{'field_1':'1 Smith St, Smithville'}"