As I have promised to begin with MSXML blog, I giving you a snippet of VBA code to retrieve data amazon.com for a particular ISBN
Option Explicit
Sub getDatafromAmazon()
Dim baseurl As String
Dim oXML As MSXML2.XMLHTTP
Dim mainurl As String
Dim tag1 As String
Dim tag2 As String
Dim shtml
Dim htmlbody
Dim authorname As String
Dim i As Long
Dim j As Long
On Error GoTo Errorhandler
baseurl = "http://www.amazon.com/s/ref=nb_sb_noss?url=search-alias%3Daps&field-keywords="
tag1 = "<a href=""/Adam-Greenspan/e/B001IQW882/"
tag2 = "</a>"
Set oXML = New MSXML2.XMLHTTP
mainurl = baseurl & Range("A3").Value
Range("B3").Value = mainurl
oXML.Open "GET", mainurl, True
oXML.send
Do
DoEvents
Loop Until oXML.readyState = 4
shtml = oXML.responseText
i = InStr(shtml, tag1)
i = i + 83
j = InStr(i, shtml, tag2)
authorname = Mid(shtml, i, j - i)
Range("C3").Value = authorname
Exit Sub
Errorhandler:
MsgBox "Error" & Err.Description
End Sub
Option Explicit
Sub getDatafromAmazon()
Dim baseurl As String
Dim oXML As MSXML2.XMLHTTP
Dim mainurl As String
Dim tag1 As String
Dim tag2 As String
Dim shtml
Dim htmlbody
Dim authorname As String
Dim i As Long
Dim j As Long
On Error GoTo Errorhandler
baseurl = "http://www.amazon.com/s/ref=nb_sb_noss?url=search-alias%3Daps&field-keywords="
tag1 = "<a href=""/Adam-Greenspan/e/B001IQW882/"
tag2 = "</a>"
Set oXML = New MSXML2.XMLHTTP
mainurl = baseurl & Range("A3").Value
Range("B3").Value = mainurl
oXML.Open "GET", mainurl, True
oXML.send
Do
DoEvents
Loop Until oXML.readyState = 4
shtml = oXML.responseText
i = InStr(shtml, tag1)
i = i + 83
j = InStr(i, shtml, tag2)
authorname = Mid(shtml, i, j - i)
Range("C3").Value = authorname
Exit Sub
Errorhandler:
MsgBox "Error" & Err.Description
End Sub
No comments:
Post a Comment