Web Scrape Help....

[ermccarthy]

Good Day!!

I am a little lost, and hoping someone can at least point me in the right direction. Every month, I need to review 20 hotels on TripAdvisor.com and see what their rankings are. One example here for Hilton Cleveland is #3 of 38 (as of right now, may change at some point). Here is the weblink:

https://www.tripadvisor.com/Hotel_R...Hilton_Cleveland_Downtown-Cleveland_Ohio.html

I was attempting to do a web query for this using the get data function, BUT, the data does not appear to be in a table, so while I can see it in the web view screen of the wizard, I can not seem to grab that tiny piece of data.

Should I be looking at a VBA function to try to grab it....it is the same location for all the pages I review.....

Any help or direction would be GREATLY, GREATLY, GREATLY appreciated!!

 

[Leith Ross]

Hello Russell,

This requires using VBA. This function returns only the rating from the web page. The rating information is in the same HTML elements for each page.

Add a new VBA Module to your workbook. Copy the code below and paste the code into the new module.

Module Code

Function GetElemText(ByRef Elem As Object, ByRef ElemText As String) As String

    Dim NV  As String   ' Node value
            
        ' Cleanup the stack.
        If Elem Is Nothing Then ElemText = "": Exit Function
        
        ' Is this element a text value?
        If Elem.NodeType = 3 Then
            ' Replace Non-Breaking Spaces with White Spaces.
            NV = Replace(Elem.NodeValue, Chr(160), Chr(32))
            
            ' Concatenate text elements.
            If ElemText = "" Then
                ElemText = NV
            Else
                ElemText = ElemText & NV
            End If
        Else
            ' Keep parsing - Element contains other elements.
            For Each Elem In Elem.ChildNodes
                Call GetElemText(Elem, ElemText)
            Next Elem
        End If
        
    GetElemText = ElemText
    
End Function


Function GetRating(ByVal URL As String)


    Dim divClass    As String
    Dim Elem        As Object
    Dim ElemText    As String
    Dim oDiv        As Object
    Dim HTMLdoc     As Object
    Dim PageSrc     As String
    Dim Text        As String
    
        With CreateObject("MSXML2.XMLHTTP")
            .Open "GET", URL, False
            .Send
            If .Status <> 200 Then
                GetRating = .Status & " - " & .statusText
                Exit Function
            End If
            PageSrc = .responseText
        End With
        
        Set HTMLdoc = CreateObject("htmlfile")
        HTMLdoc.Write PageSrc
        
        divClass = "prw_rup prw_common_header_pop_index popIndex"
        
        For Each oDiv In HTMLdoc.getElementsByTagName("div")
            If oDiv.className = divClass Then
                Text = ""
                GetRating = GetElemText(oDiv, Text)
                Exit For
            End If
        Next oDiv
            
End Function

Example of Using the Function
A1: https://www.mrexcel.com/forum/redir...Hilton_Cleveland_Downtown-Cleveland_Ohio.html
B1: =GetRating(A1)

Results in B1: #3 of 38 Hotels in Cleveland

 

[ermccarthy]

So this worked great.....I do have a question. How do I get it to update? This pulled in the information from the website when I typed in the formula (=GetRating()) However, the only way I can figure out how to update and pull the data again, is to click in the cell, and press enter. Is there a way to have it refresh periodically or maybe with a button click? With 15 or so of these, I would hate to add the manual step of clicking into each cell.

Thanks Again!!

 

[Leith Ross]

Hello,

I purposely did not add any repeat functionality since I did not know how the links were setup. I can update the macro for you but I need to know how your data is laid out on the worksheet. Can you provide an example?

 

[ermccarthy]

So it is a very simple worksheet with four columns. A= Date (I manually update the first line and formulas copy it down) B = Name of Hotel C= The GetRanking Formula and D= the URL for the GetRanking Formula.

I will need to run an update weekly. Once it does update, perhaps with a click of a button, I save the file as a Text Tab Delimited File. From there we use it in databases and Power BI.