Excel VBA调用RouteXL API发POST请求返回409无输入错误如何解决

问题原因 #

• 你声明了请求头Content-type: application/json，但实际提交的请求体是表单键值对格式，API无法解析JSON结构的输入，因此返回409无输入的错误。
• 代码存在冗余对象声明，你同时创建了Req和objHTTP两个HTTP请求对象，实际只用到了Req，无用代码可以删除。
• 请求体格式不符合要求：JSON格式下不需要加locations=前缀，需要把位置数组放在标准JSON对象中。

修正方案 #

1. 调整请求体为标准JSON格式，去掉locations=前缀，改为包裹在JSON对象中，以匹配你设置的JSON请求头
2. 如果你想沿用表单提交格式，需要把Content-type改为application/x-www-form-urlencoded，两种格式二选一即可，优先推荐按官方规范使用JSON格式
3. 清理无用的objHTTP对象创建语句

修正后的代码 #

Function ReqPOSTxl(url As String, key As String)

Dim Req As New XMLHTTP60

Req.Open "POST", url, False

Req.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
Req.setRequestHeader "Content-type", "application/json"
Req.setRequestHeader "Accept-Crs", "epsg:4258"
Req.setRequestHeader "X-Api-Key", key
Req.setRequestHeader "Authorization", "Basic " & EncodeBase64("username:password")

Dim API_body As String
' 修正为标准JSON格式，外层为对象，key为locations，值为位置数组
API_body = "{""locations"": [{""address"":""The Hague, The Netherlands"",""lat"":""52.05429"",""lng"":""4.248618""},{""address"":""The Hague, The Netherlands"",""lat"":""52.076892"",""lng"":""4.26975""},{""address"":""Uden, The Netherlands"",""lat"":""51.669946"",""lng"":""5.61852""},{""address"":""Sint-Oedenrode, The Netherlands"",""lat"":""51.589548"",""lng"":""5.432482""}]}"
Debug.Print API_body

Req.send API_body

Debug.Print Req.responseText
Set ReqPOSTxl = ParseJson(Req.responseText)
 
End Function

内容的提问来源于stack exchange，提问作者Bas Rugenbrink