Thursday, September 20, 2012

Web Application using MSXML2 for VBA

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




No comments:

Post a Comment