WinHttp.WinHttpRequest question

[Razr]

Hello everyone,

I would like your help with a quite tricky (in my opinion) vba project.

I would like to make an excel that willl contain the following columns

ISBN10 | AMAZON.CO.UK RRP | AMAZON.CO.UK FINAL PRICE

so basicaly what i want it to do is i put ISBN10 values in column A and in column B and column C excel through WinHttp.WinHttpRequest would go to amazon.co.uk that has url = http://www.amazon.co.uk/dp/[ISBN10] and will get the prices so for example:

A          B            C
ISBN10        RRP           AMAZON FINAL PRICE
0002247399   25.00       12.50

so in the above case excel would go to url
http://www.amazon.co.uk/dp/0002247399

and would grab from the source this line


class="listprice">£25.00

and then this line


priceLarge">£12.50
if error occurs i.e. no prices then continue to the following

if only fianl price like this one http://www.amazon.co.uk/dp/3937718524 would only post this price.

I REALLY hope this is possible.

Thank you in advance

 

[circledchicken]

Hi,

Is there a specific reason you need to use WinHTTP?

Perhaps try the following alternative using the XMLHTTP Object (I've added a number of comments that might be useful):

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("A2", Range("A2").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
            
        ' 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
            
            ' find and place the returned text in the sheet (skip errors)
            On Error Resume Next
                c.Offset(0, 1) = .getElementsByClassName("listprice").Item(0).innerHTML
                c.Offset(0, 2) = .getElementsByClassName("priceLarge").Item(0).innerHTML
            On Error GoTo 0
        End With
    
    Next c
    
    'Clean up
    Set oHttp = Nothing

End Sub

Finally, here are some nice resources incase your interested:

• http://www.tushar-mehta.com/publish_train/xl_vba_cases/vba_web_pages_services/
• http://www.dailydoseofexcel.com/archives/2006/10/09/async-xmlhttp-calls/
• http://scriptorium.serve-it.nl/view.php?sid=40

 

[Razr]

Hello circledchicken

No there is no specific reason i wanted to use WinHTTP

Thanks a lot for your help! I tried your code but it crashes the excel for some reason it says [running] and never ends and i only put 1 isbn value to test it As regards the references i ticked xml 6.0 on excel 2007, i also tried on xml 2000 with xml 2.6 but still no luck.

Any idea what could go wrong?

Thanks a lot in advance for your help

 

[circledchicken]

Hi,

Yes 6.0 is correct, make sure you also select the Microsoft HTML Object Library.

To handle just 1 entry try changing this line:

For Each c In Range("A2", Range("A2").End(xlDown))

to this (and make sure your column headings are in Row 1 with the ISBN codes directly underneath it, without any blank rows in the column):

For Each c In Range("A2", Range("A1").End(xlDown))

 

[Razr]

Still no luck it crashes for some reason but i did found a way to do it WinHttp.WinHttpRequest. I was wondering though if XMLHTTP Object is faster if i have lots of values to download?

 

[Norie]

The code could be crashing for a lot of reasons.

For example you might be getting in to some sort of infinite loop or a loop that appears to be infinite.

One thing I would suggest you use in xlUp instead of down.

Also you should add a worksheet refence to range.

 

[circledchicken]

Hi,

Unfortunately I'm not sure of the relative merits of WinHTTP vs XMLHTTP.

If you still want to test the XMLHTTP version let me know a few more details about where its crashing/your spreadsheet setup etc. and I can try and help (it seems to be working fine when I try it).
Otherwise, I'm glad you got it to work your way so at least you have something working!

 

[John_w]

            On Error Resume Next
                c.Offset(0, 1) = .getElementsByClassName("listprice").Item(0).innerHTML
                c.Offset(0, 2) = .getElementsByClassName("priceLarge").Item(0).innerHTML
            On Error GoTo 0

I think getElementsByClassName is only available in IE9, so try commenting out the On Error lines and see if you get any run-time errors.

 

[xenou]

<td id="actualPriceContent">span id="[COLOR="Red"]actualPriceValue[/COLOR]">
<b class="priceLarge">£12.50</b></span>

The page source has an ID for the actual price element ... that might be better to use than the class name.