Data Extraction from Website - Macros Help

[hsingh2088]

I am working on a grad school project analyzing the average prices for used luxury cars in Houston. So far, I have been able to develop the following macro.

I am trying to extract price, make, model, year and miles.

Private Sub CommandButton1_Click()
Sheet9.WebBrowser1.Navigate (Sheet9.Range("K2"))


Do
DoEvents


Loop Until Sheet9.WebBrowser1.ReadyState = READYSTATE_COMPLETE


UserForm1.TextBox1.Text = Sheet9.WebBrowser1.Document.Body.InnerTEXT
'5650
the_html_code = Sheet9.WebBrowser1.Document.Body.InnerHTML


    'If InStr(the_html_code, "<span class="prices") > 0 Then MsgBox "ok"
    
    the_output_row = 1
    
    Do
    DoEvents
    
    start_of_value = InStr(the_html_code, "itemprop=")
    If start_of_value > 0 Then
        the_output_row = the_output_row + 1
        
        the_url = Mid(the_html_code, start_of_value + 6, Len(the_html_code))
        the_html_code = Mid(the_html_code, start_of_value + 6, Len(the_html_code))
        the_url = Mid(the_url, 1, InStr(the_url, Chr(36)))
        Sheet8.Range("A" & the_output_row) = the_url
                  
    End If
        
    Loop Until start_of_value = 0
        
    MsgBox "Complete"
    
End Sub

I get the a bunch of HTML output which contains the data I am searching for but can't figure out how I narrow it down just to the attributes listed above and paste it into my Excel document properly. I am close! I just need a little help getting it across the finish line...

 

[Rijnsent]

Hi hsingh2088,
what is the site you are getting this data from / what is in cell Sheet9.Range("K2") ? Knowing that would help me helping you with your code.
Ciao,
Koen

 

[hsingh2088]

Hi Koen,

The site is

[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl51883, width: 64"]http://www.northsidelexus.com/VehicleSearchResults?search=preowned[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rijnsent]

Hi hsingh,
I've been trying this morning a couple of times, but the site gives me a timeout error (in my browsers), I'll check again later this week.
Cheers,
Koen

 

[Rijnsent]

Hi hsingh,

sorry for the delay, what about this code:

Private Sub CommandButton1_Click()

URL = "http://www.northsidelexus.com/VehicleSearchResults?search=preowned"

'Sheet1.WebBrowser1.Navigate (URL)

    Dim i As Long
    Dim IE As Object
    Dim objElement As Object
    Dim objCollection As Object
 
    ' Create InternetExplorer Object
    Set IE = CreateObject("InternetExplorer.Application")
 
    ' You can uncoment Next line To see form results
    IE.Visible = False
 
    ' Send the form data To URL As POST binary request
    IE.Navigate URL
 
    ' Statusbar
    Application.StatusBar = "site is loading. Please wait..."
 
    ' Wait while IE loading...
    Do While IE.Busy
        Application.Wait DateAdd("s", 1, Now)
    Loop

    Do
    DoEvents
    
    Set objArticles = IE.document.getElementsByTagName("article")
    
    i = 0
    While i < objArticles.Length
        Set art = objArticles(i)
        
        Debug.Print art.getElementsByClassName("year").Item(0).outerhtml
        Debug.Print art.getElementsByClassName("year").Item(0).innerhtml
        
        Debug.Print art.getElementsByClassName("make").Item(0).outerhtml
        Debug.Print art.getElementsByClassName("make").Item(0).innerhtml
        
        Debug.Print art.getElementsByClassName("model").Item(0).outerhtml
        Debug.Print art.getElementsByClassName("model").Item(0).innerhtml
        
        Debug.Print art.getElementsByClassName("price").Item(0).outerhtml
        Debug.Print art.getElementsByClassName("price").Item(0).innerhtml
        
        i = i + 1
    Wend
    
    MsgBox "Complete"
    
    ' Clean up
    Set IE = Nothing
    Set objArticles = Nothing
    
End Sub

No clue how good you are with VBA, but these are some general tips for working with VBA: for macro development, do the following; go to the VBA screen (ALT+F11) and
-in the VBA screen, do open the "view->Locals window" and the "view->immediate window"
-put break points in the code at places where you want it to stop (F9 or click in front of the line, line becomes dark red with a circle in front of it)
-if you then run the code (F5), the code will stop at that point and the locals window will give you insights into what the variables are/have in them
-use F8 to go step by step through your code and hover over variables to see what values they hold.
-use debug.print command to show the variables passing by

Cheers,

Koen