PDA

View Full Version : WinHttp.WinHttpRequest question



efz
09-30-2011, 12:22 AM
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

p45cal
10-03-2011, 06:39 AM
Does the following produce prices? run test and change the ISBN numbers too.
Sub test()
Dim ISBN As String
'Book 1:
ISBN = "0002247399"
GetAmazonPrices ISBN, List, Actual
MsgBox "list: " & List & vbLf & "actual: " & Actual

'Book 2:
ISBN = "3937718524"
GetAmazonPrices ISBN, List, Actual
MsgBox "list: " & List & vbLf & "actual: " & Actual

'Book 3:
ISBN = "1444710338"
GetAmazonPrices ISBN, List, Actual
MsgBox "list: " & List & vbLf & "actual: " & Actual
End Sub

Sub GetAmazonPrices(ISBN10 As String, lp, ap)
Dim URL As String
Dim XMLHttpRequest As Object
Dim HTMLdoc As Object
actualprice = "Not Found"
listPrice = "Not Found"
URL = "http://www.amazon.co.uk/dp/" & ISBN10
Set XMLHttpRequest = CreateObject("Microsoft.XMLHTTP")
Set HTMLdoc = CreateObject("HTMLFile")

With XMLHttpRequest
.Open "GET", URL, False
.send
HTMLdoc.body.innerHTML = .responseText
End With

Set xxx = HTMLdoc.getelementbyid("listPriceValue")
If Not xxx Is Nothing Then listPrice = xxx.innertext
Set xxx = HTMLdoc.getelementbyid("actualPriceValue")
If Not xxx Is Nothing Then actualprice = xxx.innertext
Set XMLHttpRequest = Nothing
Set HTMLdoc = Nothing
lp = listPrice
ap = actualprice
End Sub

p45cal
10-05-2011, 02:05 AM
Cross post.
http://www.mrexcel.com/forum/showthread.php?p=2882682