WinHttp.WinHttpRequest question

Razr

New Member
Joined
Jan 13, 2009
Messages
35
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:

Code:
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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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):
Code:
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:
 
Upvote 0
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:eek: 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
 
Upvote 0
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:

Code:
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):

Code:
For Each c In Range("A2", Range("A1").End(xlDown))
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
Code:
            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.
 
Upvote 0
Code:
<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.
 
Upvote 0

Forum statistics

Threads
1,215,965
Messages
6,127,969
Members
449,414
Latest member
sameri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top