IT编程技术

[
  {
    "id": "361730",
    "province": "辽宁省",
    "cityname": "大连市",
    "countyname": "沙河口区",
    "townname": "白山路街道",
    "detailsname": "碧海社区居委会"
  },
  {
    "id": "361730",
    "province": "辽宁省",
    "cityname": "大连市",
    "countyname": "沙河口区",
    "townname": "白山路街道",
    "detailsname": "集贤社区居委会" 
  }
]

Sub SendHttpGetRequest()
    Dim url As String
    Dim param As String
    Dim http As Object
    
    '设置请求的URL和参数
    url = "http://www.excelstudy.icu/api/list.php?province=%E8%BE%BD%E5%AE%81%E7%9C%81&cityname=%E5%A4%A7%E8%BF%9E%E5%B8%82&countyname=%E6%B2%99%E6%B2%B3%E5%8F%A3%E5%8C%BA"
    
    '创建HTTP对象
    Set http = CreateObject("MSXML2.XMLHTTP")
    
    '发送GET请求
    http.Open "GET", url, False
    http.send
    
    '获取服务器响应
    strResult = http.responseText
    
    Set obj = ParseJSONData(strResult)
    '清理资源
    Set http = Nothing
End Sub

 'vba转化Json数据格式
 Function ParseJSONData(strJson)
     Dim jsonObj As Object
     Dim ScriptControl As New ScriptControl
     
     '设置ScriptControl属性
     ScriptControl.Language = "JScript"
     
     '使用"eval"方法将JSON字符串解析为JavaScript对象
     Set jsonObj = ScriptControl.Eval("(" + strJson + ")")
    
    '访问JSON对象的属性
     For Each objItem In jsonObj
        Debug.Print "省: " & objItem.province
        Debug.Print "市: " & objItem.cityname
        Debug.Print "区_县: " & objItem.countyname
        Debug.Print "乡_街道: " & objItem.townname
        Debug.Print "村: " & objItem.detailsname
        Debug.Print "--------------------------"
     Next
    
     Set ParseJSONData = jsonObj
 End Function