EXCEL VBA 网络获取JSON并解读

JSON解读_20191130223712.png

第一步，创建从网络获取数据的Function：

'-----------------------------------------------
'获取网络Json数据（测试成功）
'-----------------------------------------------
Function getWebData(ByVal URL As String, Optional ByVal Method As String = "GET", Optional ByVal ReturnType As String = "Text", Optional ByVal Async As Boolean = False, Optional ByVal Username As String = "", Optional ByVal Password As String = "")
    
    '处理变量
    Method = IIf(UCase(Method) = "GET", "GET", "POST")
    
    '创建对象
    Dim xmlHttp As Object
    Set xmlHttp = CreateObject("MSXML2.XMLHTTP")
    'Set xmlHttp = CreateObject("Msxml2.ServerXMLHTTP")
    
    '发送请求
    xmlHttp.Open Method, URL, Async, Username, Password
    xmlHttp.Send
    
    '等待响应
    Do While xmlHttp.ReadyState <> 4
        DoEvents
    Loop
    
    '得到请求数据
    If xmlHttp.ReadyState = 4 Then
    
        '根据要求返回不同结果
        Select Case UCase(ReturnType)
            Case "TEXT"
                getWebData = xmlHttp.responsetext
            Case "BODY"
                getWebData = xmlHttp.responsebody
            Case "STREAM"
                getWebData = xmlHttp.responseStream
            Case "XML"
                getWebData = xmlHttp.responseXML
            Case Else
                getWebData = xmlHttp.responsetext
        End Select
        
    Else
        getWebData = "调用失败，错误代码：" & xmlHttp.Status    '返回错误结果
    End If
    
    '销毁对象
    Set xmlHttp = Nothing
    
End Function

第二步，获取数据并解析JSON：

'-------------------------------------------------------------
'解析JSON成功
'参考：http://club.excelhome.net/thread-1303169-2-1.html
'-------------------------------------------------------------
Public Sub getResource()
    
    '创建对象
    Dim oDom As Object
    Dim oWindow As Object
    Dim strHTML As String
    
    Set oDom = CreateObject("HTMLFILE")
    Set oWindow = oDom.parentWindow
    
    '{"weatherinfo":{"city":"北京","cityid":"101010100","temp":"27.9","WD":"南风","WS":"小于3级","SD":"28%","AP":"1002hPa","njd":"暂无实况","WSE":"<3","time":"17:55","sm":"2.1","isRadar":"1","Radar":"JC_RADAR_AZ9010_JB"}}
    strHTML = getWebData("http://www.weather.com.cn/data/sk/101010100.html", "get", "text")   
    
    '创建对象
    oWindow.execScript "a=" & strHTML
    
    Cells(1, 1) = strHTML
    Cells(2, 1) = oWindow.eval("a.weatherinfo.city")
    Cells(3, 1) = oWindow.eval("a.weatherinfo.cityid")
    Cells(4, 1) = oWindow.eval("a.weatherinfo.temp")
    Cells(5, 1) = oWindow.eval("a.weatherinfo.WD")
    Cells(6, 1) = oWindow.eval("a.weatherinfo.SD")
    
End Sub