1:建立http请求
set objHttp = CreateObject("WinHttp.WinhttpRequest.5.1")
::创建htmlfile对象 用于创建js语言对象,就是为了后续解析服务端返回的json数据
set htmlfile = CreateObject("htmlfile")
::创建父窗口对象
set parentWindow = htmlfile.parentWindow
::发送请求
objHttp.Open "POST",url,False
objHttp.setRequestHeader "User-Agent","Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0"
::request请求编码格式
objHttp.setRequestHeader "Content-Type" ,"application/json;charset=utf-8"
::reponse返回编码格式
objHttp.setRequestHeader "Content-Accept" ,"application/json;charset=utf-8"
::请求参数拼接,类似java参数拼接,但是在vba里面对于获取字符串value的值需要三个”来引用
str = "{""id"":"""+idValue+""",""name"":"""+nameValue+"""}"
::发送请求
objHttp.send(str)
::接收返回值,json数据
jsonString = objHttp.ReponseText
::将返回的数据赋为js变量接收
parentWindow.execScript = "var jsStr = " & jsonString
::将获取的数据解析到excel某个sheet,此获取json数据方式类似java获取,直接类.属性即可(要用到js中的eval方法)
Sheets(sheetName).Cells(row,col) = parentWindow.eval("jsStr.data.属性")