巧用split处理网抓数据

时　间:2020-08-07 14:59:56
作　者:yihui   ID：45044  城市：上海
摘　要:MSXML2.XMLHTTP，记录集，split使用。
正　文:

有网友在群里讨论，对于网页数据，excel有PQ可以直接导入网页数据，那对于access 如何处理网页数据。

论坛里有一个示例：

【Access小品】提取网页中股票数据

http://www.accessoft.com/article-show.asp?id=10505  案例里代码和操作复杂，不是太适合新手。 

尝试修改简化如下：

1."MSXML2.XMLHTTP" 模拟访问网页

2.split  relpace 等处理数据

3 rst记录集写入数据

具体代码如下：

Option Compare Database
Sub 数据抽取()
    tt = Now
    Dim strText As String
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim sql As String
    sql = "select * from 行情"
    CurrentDb.Execute ("delete * from 行情")
    Set rst = CurrentDb.OpenRecordset(sql)
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://www.lvdingjia.com/app/search.html?category=3&long=180", False
        .Send
        strText = .responsetext
    End With
    strText = Split(Split(strText, "")(1), "")(0)
    arr = Split(strText, "")
    ReDim brr(1 To UBound(arr), 1 To 5)
    For i = 1 To UBound(arr)
        Z = Split(Replace(Replace(Replace(Split(arr(i), "htm"">")(1), "", ""), " class=""diff2""", ""), "", ""), "")
        rst.AddNew
        With rst
            !日期 = Z(0)
            !最低价 = Z(1)
            !最高价 = Z(2)
            !均价 = Z(3)
            !涨跌 = Z(4)
            .Update
        End With
    Next
    MsgBox "处理完成" & vbLf & "用时" & Format(Now - tt, "hh:nn:ss")
    DoCmd.OpenTable "行情"
End Sub

案   例:

点击下载此附件

图   示: