Unable to index output results into excel

Question

I have a list of movies in a sheet that spans from A2:A966 and will run through the list of movies in the A2:A966 and pull the data for the ratings into B2:966 and total reviews into C2:C966.

The output does not display in cells B2 and C2 however there are no errors that arise from running the macro. I've tried all sorts of variations and even tried getting a msgbox to display the results but still nothing.

sub imd_data()


 Dim IE As New InternetExplorer, html As HTMLDocument, ele As Object

    With IE
        .Visible = True
        .navigate "http://www.google.com/"
        Do Until .readyState = READYSTATE_COMPLETE: Loop
        Set html = .document
        Set sht = Sheets("Sheet2")           

    End With

    html.getElementById("lst-ib").Value = sht.Range("A2") & " IMDB"
    html.getElementById("btnK").Click
    Application.Wait Now + TimeValue("00:00:05")
    html.getElementsByClassName("rc")(0).getElementsByTagName("a")(0).Click
    Application.Wait Now + TimeValue("00:00:05")

    For Each ele In html.getElementsByClassName("imdbRating")
        With ele.getElementsByClassName("ratingValue")
            If .Length Then r = r + 1: Cells(r, 1) = .Item(0).innerText
            MsgBox .Item(0).innerText
            sht.Range("B2") = .Item(0).innerText
        End With
        With ele.getElementsByClassName("small")
            If .Length Then Cells(r, 2) = .Item(0).innerText
             sht.Range("C2") = .Item(0).innerText
        End With
    Next ele    
    IE.Quit    

End Sub

Vityata · Accepted Answer · 2017-12-13T17:49:11.157

Try to refer the sht in every part of the code. E.g., rewrite this one:

For Each ele In html.getElementsByClassName("imdbRating")
    With ele.getElementsByClassName("ratingValue")
        If .Length Then r = r + 1: sht.Cells(r, 1) = .Item(0).innerText
        MsgBox .Item(0).innerText
        sht.Range("B2") = .Item(0).innerText
    End With
    With ele.getElementsByClassName("small")
        If .Length Then sht.Cells(r, 2) = .Item(0).innerText
         sht.Range("C2") = .Item(0).innerText
    End With
Next ele

thus, adding sht.Cells twice. In you do not refer to the parent sheet of the cell, the value from Range("C2") or Cells(r,2) is taken from the ActiveSheet. Judging that your parent sheet should be Sheet2, then most probably this is not your ActiveSheet.

Just an idea - do not use : in the If, it is hard to follow and it is not always what you (or at least me) expect it would be - VBA - How the colon `:` works in VBA code with condition

The code that worked for me is this one:

Sub ImdbData()    
    Dim IE As New InternetExplorer, html As HTMLDocument, ele As Variant
    Dim sht     As Worksheet
    Dim r       As Long        
    With IE
        .Visible = True
        .navigate "http://www.google.com/"
        Do Until .readyState = READYSTATE_COMPLETE: Loop
        Set html = .document
    End With

    Set sht = Worksheets(1)

    html.getElementById("lst-ib").value = "The incredibles " & " IMDB"
    html.getElementById("btnK").Click
    Application.Wait Now + TimeValue("00:00:05")
    html.getElementsByClassName("rc")(0).getElementsByTagName("a")(0).Click
    Application.Wait Now + TimeValue("00:00:05")

    For Each ele In html.getElementsByClassName("imdbRating")
        With ele.getElementsByClassName("ratingValue")
            If .Length Then r = r + 1: Cells(r, 1) = .Item(0).innerText
            MsgBox .Item(0).innerText
            sht.Range("B2") = .Item(0).innerText
        End With
        With ele.getElementsByClassName("small")
            If .Length Then Cells(r, 2) = .Item(0).innerText
             sht.Range("C2") = .Item(0).innerText
        End With
    Next ele
    IE.Quit

End Sub

I have simply added "The incredibles " instead of sht.Range("A2"), thus I am sure that it will load a movie and it will find ElementByClassName("imdbRating") in imdb.com.

Furthermore, it is a good practice to mention the libraries you are using for early binding, whenever you are asking in StackOverflow. Thus, you save about 3-4 minutes of search to everyone. In your case, you are using:

Microsoft Internet Controls
Microsoft HTML Object Library

The standard ones could be skipped:

Or simply use late binding. At the end, if you think about internet crawling, it could be a bit illegal but if this is not your case, probably it is a better idea to do it with Beautiful Soup and Python.

I’m a little lost as to how I would edit it the code to reflect that sheet2 is the activesheet, do I remove the cells portion of the code? — John Gendi, Dec 13 '17 at 17:06
@JohnGendi - compare my code and the part of your code here - https://text-compare.com. I have simply added twice `sht`. — Vityata, Dec 13 '17 at 17:07
@JohnGendi - so do you get the information you need at the `MsgBox()` — Vityata, Dec 13 '17 at 17:22
No, no msgbox even appears. Would that mean that I am possibly using inner text incorrectly? — John Gendi, Dec 13 '17 at 17:26
It means it does not enter the loop at all. Thus this collection is empty `html.getElementsByClassName("imdbRating")` — Vityata, Dec 13 '17 at 17:27
any idea why, all the classes that are mentioned are done correctly? — John Gendi, Dec 13 '17 at 17:31
are you sure this worked for you, i still have no msgbox or anything appearing — John Gendi, Dec 13 '17 at 17:56

Unable to index output results into excel

1 Answers1