0

I'm trying to automate going to a website and pulling the ratings from several apps.

I've figured out how to navigate and login to the page.

How do I pull the element - the number "3.3" in this case - from this specific section into Excel.

Being unfamiliar with HTML in VBA, I got this far following tutorials/other questions.

Rating on website and the code behind it
enter image description here

Sub PullRating()
    
    Dim HTMLDoc As HTMLDocument
    Dim ie As InternetExplorer
    
    Dim oHTML_Element As IHTMLElement
    Dim sURL As String
    
    On Error GoTo Err_Clear
    sURL = "https://www.appannie.com/account/login/xxxxxxxxxx"
    Set ie = New InternetExplorer
    ie.Silent = True
    ie.navigate sURL
    ie.Visible = True
    
    Do
        'Wait until the Browser is loaded
    Loop Until ie.readyState = READYSTATE_COMPLETE
    
    Set HTMLDoc = ie.Document
    
    HTMLDoc.all.Email.Value = "[email protected]"
    HTMLDoc.all.Password.Value = "xxxxx"
    
    For Each oHTML_Element In HTMLDoc.getElementById("login-form")
        If oHTML_Element.Type = "submit" Then oHTML_Element.Click: Exit For
    Next
           
    Dim rating As Variant
    Set rating = HTMLDoc.getElementsByClassName("rating-number ng-binding")
    Range("A1").Value = rating
           
    'ie.Refresh 'Refresh if required
    Err_Clear:
    If Err <> 0 Then
        Err.Clear
        Resume Next
    End If
    
End Sub
Improve this question
2
  • 1
    You are returning a HTMLElementCollection so htmldoc.getelementsbyclassname("")(x) where x=0 to however many there are. Commented Jan 9, 2017 at 23:50
  • 1
    ...and getElementById only returns a single element, not a collection of elements so you cannot loop over it. Commented Jan 10, 2017 at 0:28

2 Answers 2

Reset to default
1

The code below will let you extract text from first element with class name "rating-number ng-binding" in HTML document. By the way GetElementsByClassName is supported since IE 9.0. I use coding compatible also with older versions in my example.

Dim htmlEle1 as IHTMLElement

For Each htmlEle1 in HTMLDoc.getElementsByTagName("div")
   If htmlEle1.className = "rating-number ng-binding" then
      Range("A1").Value = htmlEle1.InnerText
      Exit For
   End if
Next htmlEle1
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Thanks for the reply. I gave that a go but am still getting no values returning to excel. For what it's worth, also tried replacing <.getElementsByTagName("div")> with <.getElementById("aa-app")> and <.getElementsByClassName("summary-section")> and still getting nothing.
Remove On Error GoTo Err_Clear to see what is the actual problem. I am also confused by all these <pre> and <code> tags in the comment above. Also use: Do Until ie.readyState = 4 And ie.Busy =False: DoEvents: Loop as a replacement for "Wait until the Browser is loaded" part of the code.
Using Ryszards code, step through line by line making sure the page loads after you click the submit button. You may find that it works. Then when you run it on its own it doesn't. That would be because in your original code you have not told vba to wait for the browser to load between submitting and getting the value. As a side note you could look into using the addon power query. (if using the latest office its not an addon but default part of the software)
Yep, I've amended with Ryszard's comment and done as you suggested. Now I'm getting a "Run-time error '91': Object variable or With Block variable not set'" message triggered on the For Each htmlEle1 In HTMLDoc.getElementsByTagName("div") line.
Please use Do Until ie.readyState = 4 And ie.Busy =False: DoEvents: Loop not only after navigating, but also after clicking submit button. I must warn though, that sometimes this loop falsely assumes that page has fully loaded, if website uses JavaScript. You can check if the code works properly by stepping line by line with F8 and wait a few seconds after executing oHTML_Element.Click line.
|
0

While Ryszards code should do the trick if you want to use the code you have already written then here is the alterations I believe you need to make.

For Each oHTML_Element In HTMLDoc.getElementById("login-form")
    If oHTML_Element.Type = "submit" Then oHTML_Element.Click: Exit For
Next

'Need to wait for page to load before collecting the value
Loop Until ie.readyState = READYSTATE_COMPLETE

Dim rating As IHTMLElement
Set rating = HTMLDoc.getElementsByClassName("rating-number ng-binding")
'Need to get the innerhtml of the element
Range("A1").Value = rating.innerhtml
Improve this answer

4 Comments

Hey, I've just put your code in a new module and went through everything line by line where I get "Object doesn't support this property or method" triggered on Range("A1").Value = rating.innerHTML. The .innerHTML bit doesn't seem to be recognised as anything - is there some reference that I need for this? I already have Microsoft Internet Controls and HTML Object Library checked.
try .textContent instead of .innerHTML Right click on rating and add to watch. Then expand it to see if it gets set correctly. If so you should be able to see all the possible properties and pick the correct one. If its not being set then perhaps it was unable to find this tag? Note you wont be able to expand until the variable is set. so you will need to step through the code again. the different .xxx property depends on the browser version you are using.
.innerHTML and .textContent doesn't work with the Variant type. It works with when the type is defined as IHTMLElement but that only works when using .getElementByID. Still trying to figure out how to get an innerText to work with a class or tag.
No matter if you call .getElementByID or get getElementsByClassName the return type will be an element. So if what you are saying is that it doesnt work if the type is variant only if the type is IHTMLElement then making the following change should work. Change Dim rating As Variant To Dim rating As IHTMLElement I have updated the original comment.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.