Extracting data from website using VBA

[viktor4e]

Hi everyone,

I am trying to extract data from a website using VBA. This is the web site:
Burberry Men Burberry cologne - a fragrance for men 1995

The result that I need is "3.94" which is in the line "Perfume rating: 3.94 out of 5 with 868 votes." on the website.

This is the code in VBA I am currently using (based on research here an on YouTube) but with no success as I am very new to web scrapping (I have entered the web site in Range("K1") below):

Sub Extract()
Application.ScreenUpdating = False


Dim IE As New InternetExplorer
'IE.Visible = True


IE.Navigate Sheet1.Range("K1").Value


Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE


Dim Doc As HTMLDocument
Set Doc = IE.document
Dim Extract As String




Extract = Doc.getElementsByTagName("span")(o).innerText


Range("A1").Value = Extract


Application.ScreenUpdating = True
End Sub

 

[Ombir]

Set Reference to Microsoft Internet Controls and Microsoft HTML object library from "VBE--->Tools-->>References" before running the code:

Sub Ombir_11Dec2016()
Dim ie      As InternetExplorer
Dim doc     As HTMLDocument
Dim rating  As String
Dim pname   As String

Set ie = New InternetExplorer

With ie
    .Navigate "http://www.fragrantica.com/perfume/Elizabeth-Taylor/Diamonds-and-Emeralds-1704.html"
    .Visible = True
    Do While .ReadyState <> 4: DoEvents: Loop

    Set doc = ie.Document
   
    With doc
        rating = .getElementsByClassName("effect6")(0).PreviousSibling.getElementsByTagName("span")(0).innerText
        pname = .getElementById("col1").getElementsByTagName("h1")(0).innerText
    End With
End With
ie.Quit
MsgBox pname & " : " & rating
End Sub

 

[viktor4e]

This works great, but there is just a small issue that I am sure can be easily resolved:
Anytime I enter a web site link for which there is no rating available (for example Onekh Bvlgari cologne - a new fragrance for men 2016) the macro crashes.
As my macro loops through a number of these web site links, I would like in a case where there is no rating the outcome to be blank and the loop to continue to the next link and not crash.

Much appreciated in advance!

 

[Ombir]

Try this, I've changed the logic now:

Sub Ombir_11Dec2016()
Dim ie          As InternetExplorer
Dim doc         As HTMLDocument
Dim rating      As Object
Dim prodRating  As String
Dim prodName    As String

Set ie = New InternetExplorer

With ie
    .Navigate "http://www.fragrantica.com/perfume/Bvlgari/Onekh-41220.html"
    .Visible = True
    Do While .ReadyState <> 4: DoEvents: Loop
    
    Set doc = ie.Document
    
    With doc
        Set rating = .querySelectorAll("[itemprop=aggregateRating]")(0)
        If Not rating Is Nothing Then
            prodRating = rating.getElementsByTagName("span")(0).innerText
            Else
                prodRating = "Rating not available"
                    End If
        prodName = doc.getElementById("col1").getElementsByTagName("h1")(0).innerText
    End With
    
End With
ie.Quit
MsgBox prodName & " : " & prodRating
End Sub

 

[viktor4e]

Thank you very much, this solves the issue!