在Excel中使用VBA时，Firebase REST API无法解析JSON [英] Firebase REST API not parsing JSON when using VBA in Excel

问题描述

我在使用其余API将JSON数据发送到Firebase数据库时遇到问题，数据已发送，但无法解析.例如，如果我在Windows的命令提示符中使用以下curl命令:

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:

但是，在使用以下VBA代码时:

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

这将发送完全相同的字符串化JSON，并将其添加到Firebase数据库，但是，不会解析JSON字符串:

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

我尝试了不同的内容类型和JSON字符串的变体，但似乎没有任何效果.谁能解释我如何获取VBA脚本来发送Firebase将解析的数据?

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?

谢谢

推荐答案

我找到了一种将excel数据从excel发送到firebase的可能解决方案，但是它没有回答我有关上述VBA代码为什么发送Stringified JSON不能解决的问题在Firebase中无法解析.我仍然想要一个解决方案，因为我已经有了一个函数，可以根据我的数据创建字符串化的JSON.

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.

使用来自 VBA网络库 /stackoverflow.com/questions/21021540/post-json-to-web-in-excel-vba>此Stack Overflow帖子似乎可以解决问题.该示例将字典用于您的数据，但是请我的评论和关于要发送的JSON字符串格式的后续答复. 不需要转义码！

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!

没有JSON的PUT和其他请求类型，但是您可以轻松地在自己中添加它们.

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

与上述等效的代码，但使用VBA-web库(带有自定义PutJson函数)是:

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....

快乐的日子！

但是，我仍然想知道为什么看似相同的curl和VBA HTTP请求导致FireBase中的数据解析不同?

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?

这篇关于在Excel中使用VBA时，Firebase REST API无法解析JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！