CODEWITHSUNDEEP
excelvba
James
James

Reputation: 33

What object should be referenced to use the Sheets property?

I'm trying to look at cells in column A, and grab the number of employees for that company from a Google search (column A would be "company name number of employees"). Here's the code (need reference to Microsoft Internet Controls and Microsoft HTML Object Library):

Sub GetHits()
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
Dim var As String
Dim var1 As Object
Dim oHtml As HTMLDocument
Dim oElement As Object
Set oHtml = New HTMLDocument

    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 = "https://www.google.com/search?q=" & Cells(i, 1)

        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 oElement = oHtml.getElementsByClassName("Z0LcW")

        Sheets("Sheet1").Range("A" & i) = oElement(i).innerText
        i = i + 1

        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

Run time error 91 - Object variable or With block variable not set

comes in at this line:

Sheets("Sheet1").Range("A" & i) = oElement(i).innertext

I've tried changing it to Worksheets Set With. Perhaps because it's VBA rather than Vb.net? I read something about Set being different between the two versions.

I don't think it's because I'm missing quotation marks, as in another run-time error 91 problem on here, or because of misspelling, etc.

oElement has been set as an object on line 9 - Dim oElement As Object.

Here's a sample of Column A:

enter image description here

Upvotes: 0

Views: 91

Answers (3)

BruceWayne
BruceWayne

Reputation: 23283

Change html.body.innerHTML = XMLHTTP to oHtml.body.innerHTML = ...

OR - change the Set oElement = oHtml... to Set oElement = html.getElementsByClassName()

Then, change oElement(i).innerText to oElement(0).innerText since you want the first (0 indexed) response from that class.

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166146

 Sheets("Sheet1").Range("A" & i) = oElement(i).innerText

It's not clear why, when searching for a single company at a time, the returned page would have at least the same number of matching div's as the Excel row you're on at the time?

There's no connection between your sheet and the page content, other than the company name.

Sheets("Sheet1").Range("A" & i) = oElement(0).innerText

might be more appropriate.

This works for me:

Sub GetHits()
    Dim url As String
    Dim XMLHTTP As Object
    Dim oHtml As HTMLDocument, i As Long
    Dim oElement As Object, sht As Worksheet

    Set sht = ActiveSheet

    For i = 2 To sht.Range("A" & sht.Rows.Count).End(xlUp).Row

        url = "https://www.google.com/search?q=" & Cells(i, 1) & _
              " number of employees"
        With CreateObject("MSXML2.serverXMLHTTP")
            .Open "GET", url, False
            .setRequestHeader "Content-Type", "text/xml"
            .setRequestHeader "User-Agent", _
                "Mozilla/5.0 (Windows NT 6.1; rv:25.0) Gecko/20100101 Firefox/25.0"
            .send
            Set oHtml = New HTMLDocument
            oHtml.body.innerHTML = .ResponseText
        End With

        Set oElement = oHtml.getElementsByClassName("Z0LcW")
        If oElement.Length > 0 Then sht.Range("B" & i) = oElement(0).innerText
    Next

End Sub

Upvotes: 3

aSIMetrical
aSIMetrical

Reputation: 121

Forgive me, I am on my phone...

I see that you have set oHtml as a New HTMLDocument but I do not see it being provided it with any additional information.

Because of that I am assuming this code: Set oElement = oHtml.getElementsByClassName("Z0LcW") is pretty much saying:

Set oElement = Nothing Which would cause a Run-Time Error 91.

As you are applying the innerText to the html object and not the oHtml object, I would try:

Set oElement = html.getElementsByClassName("Z0LcW")

Upvotes: 3

Related Questions