在 Excel VBA 代码中处理 XMLHttp 响应中的 JSON 对象 [英] Handle JSON Object in XMLHttp response in Excel VBA Code
问题描述
我需要处理一个 JSON 对象,它是 Excel VBA 中 XMLHTTPRequest 的响应.我写了下面的代码,但它不起作用:
Dim sc As Object设置 sc = CreateObject("ScriptControl")sc.Language = "JScript"将 strURL 作为字符串调暗:strURL = "blah blah"暗淡的strRequest昏暗 XMLhttp:设置 XMLhttp = CreateObject("msxml2.xmlhttp")作为字符串的暗淡响应XMLhttp.Open "POST", strURL, FalseXMLhttp.setrequestheader "Content-Type", "application/x-www-form-urlencoded"XMLhttp.send strRequest响应 = XMLhttp.responseTextsc.Eval ("JSON.parse('" + response + "')")
我在 Set sc = CreateObject("ScriptControl")
行中收到错误 Run-time error '429' ActiveX component can't create objectp>
一旦我们解析了 JSON 对象,你如何访问 JSON 对象的值?
附:我的 JSON 对象示例:{"Success":true,"Message":"Blah blah"}
代码从 nseindia 站点获取数据,该数据在 responseDiv
元素中以 JSON 字符串的形式出现.
必填参考文献
我用过的3个类模块
- cJSONScript
- cStringBuilder
- JSON
(我从 中选择了这些类模块这里)
您可以从这个链接下载文件
标准模块
Const URl As String = "http://www.nseindia.com/live_market/dynaContent/live_watch/get_quote/GetQuote.jsp?symbol=ICICIBANK"子 xmlHttp()将 xmlHttp 调暗为对象设置 xmlHttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")xmlHttp.Open "GET", URl &"&rnd=" &WorksheetFunction.RandBetween(1, 99), FalsexmlHttp.setRequestHeader "内容类型", "文本/xml"xmlHttp.send将 html 调暗为 MSHTML.HTMLDocument设置 html = 新建 MSHTML.HTMLDocumenthtml.body.innerHTML = xmlHttp.ResponseText将 divData 调暗为对象设置 divData = html.getElementById("responseDiv")'?divData.innerHTML' 这里你会得到一个字符串,它是一个 JSON 数据将 strDiv 作为字符串,startVal As Long,endVal As LongstrDiv = divData.innerHTMLstartVal = InStr(1, strDiv, "数据", vbTextCompare)endVal = InStr(startVal, strDiv, "]", vbTextCompare)strDiv = "{" &Mid(strDiv, startVal - 1, (endVal - startVal) + 2) &}"将 JSON 暗淡为新 JSON暗淡为对象设置 p = JSON.parse(strDiv)我 = 1对于 p("data")(1) 中的每个项目单元格(i, 1) = 项目单元格(i,2)= p(数据")(1)(项目)我 = 我 + 1下一个结束子
I need to handle a JSON Object which is the response of XMLHTTPRequest in Excel VBA. I wrote the code below, but it doesn't work:
Dim sc As Object
Set sc = CreateObject("ScriptControl")
sc.Language = "JScript"
Dim strURL As String: strURL = "blah blah"
Dim strRequest
Dim XMLhttp: Set XMLhttp = CreateObject("msxml2.xmlhttp")
Dim response As String
XMLhttp.Open "POST", strURL, False
XMLhttp.setrequestheader "Content-Type", "application/x-www-form-urlencoded"
XMLhttp.send strRequest
response = XMLhttp.responseText
sc.Eval ("JSON.parse('" + response + "')")
I am getting the error Run-time error '429' ActiveX component can't create object in the line Set sc = CreateObject("ScriptControl")
Once we parsed the JSON Object, how do you access the values of the JSON Object?
P.S. My JSON Object sample: {"Success":true,"Message":"Blah blah"}
The code gets the data from nseindia site which comes as a JSON string in responseDiv
element.
Required References
3 Class Module i have used
- cJSONScript
- cStringBuilder
- JSON
(I have picked these class modules from here)
You may download the file from this link
Standard Module
Const URl As String = "http://www.nseindia.com/live_market/dynaContent/live_watch/get_quote/GetQuote.jsp?symbol=ICICIBANK"
Sub xmlHttp()
Dim xmlHttp As Object
Set xmlHttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
xmlHttp.Open "GET", URl & "&rnd=" & WorksheetFunction.RandBetween(1, 99), False
xmlHttp.setRequestHeader "Content-Type", "text/xml"
xmlHttp.send
Dim html As MSHTML.HTMLDocument
Set html = New MSHTML.HTMLDocument
html.body.innerHTML = xmlHttp.ResponseText
Dim divData As Object
Set divData = html.getElementById("responseDiv")
'?divData.innerHTML
' Here you will get a string which is a JSON data
Dim strDiv As String, startVal As Long, endVal As Long
strDiv = divData.innerHTML
startVal = InStr(1, strDiv, "data", vbTextCompare)
endVal = InStr(startVal, strDiv, "]", vbTextCompare)
strDiv = "{" & Mid(strDiv, startVal - 1, (endVal - startVal) + 2) & "}"
Dim JSON As New JSON
Dim p As Object
Set p = JSON.parse(strDiv)
i = 1
For Each item In p("data")(1)
Cells(i, 1) = item
Cells(i, 2) = p("data")(1)(item)
i = i + 1
Next
End Sub
这篇关于在 Excel VBA 代码中处理 XMLHttp 响应中的 JSON 对象的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!