Firebase REST API not parsing JSON when using VBA in Excel

Question

I am having trouble sending JSON data to a firebase database using the rest API, the data is sent, but it does not parse. For instance if I use this curl command in command prompt in windows:

curl -X PUT -d  "{\"lastName\":\"Jones\",\"firstName\":\"Bubba\"}"   https://<database-name>.firebaseio.com/rest/test/.json

That results in the correct parsing of the data:

Yet, when using the following VBA code:

Sub PUSHhttpRequestTest()  'Doesn't Work!!

    Dim sc As Object
    Set sc = CreateObject("ScriptControl")
    sc.Language = "JScript"

    Dim strURL As String: strURL = "https://<database-name>.firebaseio.com/rest/.json"

    Dim strRequest
    strRequest = """{\""lastName\"":\""Jones\"",\""firstName\"":\""Bubba\""}"""
    Dim XMLhttp: Set XMLhttp = CreateObject("msxml2.xmlhttp")
    Dim response As String
    Debug.Print strRequest
    XMLhttp.Open "PUT", strURL, False
    XMLhttp.setrequestheader "Content-Type", "application/json;charset=UTF-8"
    XMLhttp.sEnd strRequest
    response = XMLhttp.responseText
    Debug.Print response
End Sub

This sends exactly the same stringified JSON, and it gets added to the Firebase database, however, the JSON string doesn't get parsed:

I have tried different Content Types, and variations on the JSON string, but nothing seems to work. Can anyone explain how I can get the VBA script to send data that Firebase will parse?

Thanks

FrodmanG · Accepted Answer · 2018-08-14 14:35:28Z

I found a possible solution to sending JSON data from excel to firebase, but it doesn't answer my question about why the above VBA code sending a Stringified JSON doesn't get parsed in Firebase. I would still like a solution to that, because I already have a function the creates the stringified JSON from my data.

Using the VBA-web Library from this Stack Overflow post seems to do the trick. The example uses dictionaries for your data, however please my comment and the subsequent reply regarding the format of the JSON string to send. No escape code is required!

There is no PUT, and Other request types for json, but you can easily add these in yourself.

The equivalent code to the above, but using VBA-web library (with custom PutJson function) is:

Sub test()

Dim strURL As String: strURL = "https://<database-name>/rest/test/whatwhat/.json"
Dim strRequest As String: strRequest = "{""LastName"":""Jones"",""firstName"":""Bubba""}"

Dim Client As New WebClient
Dim Response As WebResponse
Set Response = Client.PutJson(strURL, strRequest)

ActiveSheet.Range("A1").Value = Response.Content
End Sub

And we end up with this....

Happy Days!

However, I'd still like to know why the seemingly identical curl and VBA HTTP requests result in different parsing of the data in FireBase?

Collectives™ on Stack Overflow

Firebase REST API not parsing JSON when using VBA in Excel

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related