解决 “Microsoft.XMLHTTP” 拒绝访问的问题

最新推荐文章于 2024-06-11 18:01:32 发布

原创最新推荐文章于 2024-06-11 18:01:32 发布 · 2.2k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#xml #XMLHttp #VBA #MSXML2.ServerXMLHTTP

vba 同时被 2 个专栏收录

11 篇文章

订阅专栏

5 篇文章

订阅专栏

本文介绍了一个使用VBA脚本从XML文件中提取数据的方法，通过编写Sub过程下载XML数据，解析并填充Excel表格。脚本利用了Microsoft.XMLHTTP对象和MSXML2.ServerXMLHTTP对象来实现HTTP请求，遍历XML节点获取所需信息。

Sub 下载XML数据()
    Dim http As Object, nod, nod2
	'Set http = CreateObject("Microsoft.XMLHTTP")'拒绝访问
    Set http = CreateObject("MSXML2.ServerXMLHTTP")
    http.Open "get", "http://zx.500wan.com/static/info/kaijiang/xml/ssq/12019.xml", False
    http.Send
    i = 0
    For Each nod In http.responseXML.getElementsByTagName("xml")
        For Each nod2 In nod.ChildNodes
            i = i + 1
            Cells(i, 1) = nod2.nodeName
            Cells(i, 2) = nod2.Text
        Next
    Next
    a = Array("RedFX", "BlueFX", "TiDian")
    For p = 0 To 2
        i = Range("a65536").End(xlUp).Row
        For Each nod In http.responseXML.getElementsByTagName(a(p))
            For Each nod2 In nod.Attributes
                i = i + 1
                Cells(i, 1) = nod2.Name
                Cells(i, 2) = nod2.Text
            Next
        Next
    Next p
End Sub