excel - 如果使用电话号码，谷歌通过 VBA 搜索没有结果

Question

使用我在网上找到的以下代码，它在搜索电话号码时不返回结果，文本很好，带回网络链接和标题

我注意到，当搜索号码时，link.className 中没有 className“r”，我将如何修复与电话号码一起使用

Sub XMLHTTP()

Dim url As String, lastRow As Long, i As Long
Dim XMLHTTP As Object, html As Object, objResultDiv As Object, objH3 As Object, link As Object

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

For i = 2 To lastRow

  url = "https://www.google.co.uk/search?q=03701116565" & "&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.getelementbyid("rso")

    Set objH3 = objResultDiv.getelementsbytagname("h3")

    For Each link In objH3
        If link.className = "r" Then
            Cells(i, 2) = link.innerText
            Cells(i, 3) = link.getelementsbytagname("a")(0).href
            DoEvents
        End If
    Next
Next

End Sub

标签： excelvbaapiweb-scraping

Answer 1

有一个类名r。请注意以下事项：

Option Explicit
Public Sub GetLinks()
    Dim html As HTMLDocument, links As Object, i As Long, counter As Long
    Set html = New HTMLDocument
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://www.google.co.uk/search?q=03701116565", False
        .send
        html.body.innerHTML = StrConv(.responseBody, vbUnicode)
    End With

    With html
        Set links = .querySelectorAll(".r > [href] , .r h3")
    End With
    For i = 0 To links.Length - 1 Step 2
        counter = counter + 1
        ActiveSheet.Cells(counter, 1) = links.item(i)
        ActiveSheet.Cells(counter, 2) = links.item(i + 1).innerText
    Next
End Sub

实际与您按类定位的标题标签元素之前的href子标签相关联。是标签父级的类。ah3ra

---

如果您想使用后期绑定以及与您类似的方法，则可以使用效率较低的以下方法。请注意，父 div 元素已被选中，因此可以访问a标签并且h3可以用于限定类。

Option Explicit
Public Sub GetLinks()
    Dim html As Object, i As Long
    Dim objResultDiv As Object, objH3 As Object, link As Object

    Set html = CreateObject("htmlfile")
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://www.google.co.uk/search?q=03701116565", False
        .send
        html.body.innerHTML = .responseText
    End With

    Set objResultDiv = html.getElementById("rso")
    Set objH3 = objResultDiv.getElementsByTagName("div")
    For Each link In objH3
        If link.className = "r" Then
            i = i + 1
            On Error Resume Next
            ActiveSheet.Cells(i, 2) = link.getElementsByTagName("a")(0).href
            ActiveSheet.Cells(i, 3) = link.getElementsByTagName("h3")(0).innerText
            On Error GoTo 0
        End If
    Next
End Sub