vba - 如何使用 XMLHTTP 从网页中获取信息

Question

我正在尝试让 Excel 从搜索引擎中检索数据 (torrentz.eu/search?q=abc)

它应该获取第一个链接的信息并将其显示在 Excel 上:

单元格 A1:我的查询

单元格 A2:链接标题

单元格 A3:网址

单元格 A4:链接日期。

看来我不能使用 getelementbyid 因为页面几乎不使用 id 标签。

到目前为止我所拥有的:

Sub XMLHTTP()

Dim url As String, lastRow As Long
Dim XMLHTTP As Object, html As Object, objResultDiv As Object, objH3 As Object, link As Object
Dim start_time As Date
Dim end_time As Date

lastRow = Range("A" & Rows.Count).End(xlUp).Row

Dim cookie As String
Dim result_cookie As String

start_time = Time
Debug.Print "start_time:" & start_time

For i = 2 To lastRow

    url = "http://www.torrentz.eu/search?q=" & Cells(i, 1) & "&rnd=" & WorksheetFunction.RandBetween(1, 10000)

    Set XMLHTTP = CreateObject("MSXML2.serverXMLHTTP")
    XMLHTTP.Open "GET", url, False
    XMLHTTP.setRequestHeader "Content-Type", "text/xml"
    XMLHTTP.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; rv:25.0) Gecko/20100101 Firefox/25.0"
    XMLHTTP.send

        Set html = CreateObject("htmlfile")
    html.body.innerHTML = XMLHTTP.ResponseText
    Set objResultDiv = html.getElementBy????
    Set objH3 = objResultDiv.getElementBytagname("d1")(0)
    Set link = objH3.getelementsbytagname("/d1")(0)


    str_text = Replace(link.innerHTML, "<dt>", "")
    str_text = Replace(str_text, "</dt>", "")

    Cells(i, 2) = str_text
    Cells(i, 3) = link.href
    Cells(i, 4) = link.date????

    DoEvents
Next

end_time = Time
Debug.Print "end_time:" & end_time

Debug.Print "done" & "Time taken : " & DateDiff("n", start_time, end_time)
MsgBox "done" & "Time taken : " & DateDiff("n", start_time, end_time)
End Sub

Answer 1

如果我明白你的后，这应该让你接近

    your code
    *
    *
    *
    Set html = CreateObject("htmlfile")
    html.body.innerHTML = XMLHTTP.ResponseText

    link_title = html.body.getElementsByTagName("dl")(3).getElementsByTagName("a")(0).innerhtml
    link_title = Replace(link_title, "<B>", "", 1, -1, vbTextCompare)
    link_title = Replace(link_title, "</B>", "", 1, -1, vbTextCompare)
    Debug.Print link_title

    link_url = html.body.getElementsByTagName("dl")(3).getElementsByTagName("a")(0).getAttribute("href")
    link_url = Replace(link_url, "about:", "http://torrentz.eu", 1, -1, vbTextCompare)
    Debug.Print link_url

    link_date = html.body.getElementsByTagName("dl")(3).getElementsByTagName("span")(1).innerhtml
    link_date = Replace(link_date, "<SPAN title=""", "", 1, -1, vbTextCompare)
    targ = InStr(1, link_date, """", vbTextCompare)
    link_date = Left(link_date, -1 + targ)
    Debug.Print link_date

    continue your code