Get Prices form Amazon

[TheMo]

Good evening together,

I'm searching for a small solution to get prices from amazon.com or amazon.de (.de would be the preffered solution) for some books.
My worksheet looks like the following table (Column a,b,c):

ISBN of book	Price (new)	Price (used)
3442376327	15,57 EUR	2,57 EUR
3442469015	...	...
3517068624
3499267764
3499267039
3499267365
3442432626
3442466598
3451058189
3442465362

<tbody>
</tbody>

And now it would be perfect to have an vba-macro, that extracts the new price and the used-price (if available) from the Amazon page.
At the moment I am using the folling macro but it isn't working yet.

Do you have any idea?
Thank you very much.

TheMo

Option Explicit
' set references to Microsoft XML and Microsoft HTML Object Library
' (through Tools>References...)

Sub getAmazonData()
    
    Dim oHttp As MSXML2.XMLHTTP
    Dim sURL As String
    Dim HTMLDoc As HTMLDocument
    Dim c As Range
    
    ' Create an XMLHTTP object
    Set oHttp = New MSXML2.XMLHTTP
     
    For Each c In Range("A1", Range("A24").End(xlDown)) 'change to however you want to loop through the items
        
        ' get the URL to open
        'sURL = "http://www.amazon.co.uk/dp/" & c
        sURL = "http://www.amazon.de/s/ref=nb_sb_noss?__mk_de_DE=%C3%85M%C3%85%C5%BD%C3%95%C3%91&url=search-alias%3Daps&field-keywords=3836507080"
           MsgBox sURL
        ' Open socket and get website html
        oHttp.Open "GET", sURL, False
        oHttp.Send
        Set HTMLDoc = New HTMLDocument
        With HTMLDoc
            'Assign the returned text to a HTML document
            
            
            .body.innerHTML = oHttp.responseText
           Cells(30, 1) = .body.innerHTML
            
                       
            ' find and place the returned text in the sheet (skip errors)
            On Error Resume Next
                c.Offset(0, 1) = .getElementsByClassName("a-size-base a-color-price s-price a-text-bold").Item(0).innerHTML
                c.Offset(0, 2) = .getElementsByClassName("a-size-base a-color-price a-text-bold").Item(0).innerHTML
            On Error GoTo 0
        End With
    
    Next c
    
    'Clean up
    Set oHttp = Nothing

End Sub

 

[Grummet]

I do not have much experience with retrieving data from HTML, but I have a tip that you may find useful.
Try using this link instead:

http://www.amazon.de/gp/aw/s/ref=is_box_?__mk_de_DE=%C5M%C5%B4%D5%D1&k=3442376327

This is for a mobile version of the site and gives you A LOT less data to work through. If you want to try the page for yourself just go to m.amazon.de

Good luck!

 

[TheMo]

Thanks, a very good idea.
The performance is much better now and it's much easier to find the correct prices.

I will post the code tomorrow.

Thank you very much!