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

What is XMLHttpRequest

We are coming up with some excited blogs on MSXML2.XMLHTTP Application. Before that you need to get some brief idea about XMLHttpRequest to understand code

                            XMLHttpRequest (XHR) is an API available in web browser scripting languages such as JavaScript.

It is used to send HTTP or HTTPS requests directly to a web server and load the server response data directlyback into the script. The data might be received from the server as  XML[4], HTML, or as plain text.Data from the response can be used directly to alter the DOM of the currently active document in the browser window without loading a new web page document. The response data can also be evaluated/manipulated by client-side scripting.

               The following sections demonstrate how a request using the XMLHttpRequest object functions within a conforming user agent based on the W3C Working Draft.The HTTP and HTTPS requests of the XMLHttpRequest object must be initialized through the open method.This method must be invoked prior to the actual sending of a request to validate and resolve the request method, URL, and URI user information to be used for the request.This method does not assure that the URL exists or the user information is correct.This method can accept up to five parameters, but requires only two, to initialize a request.

                              open( Method, URL, Asynchronous, UserName, Password ) 

               The first parameter of the method is a text string indicating the HTTP request method to use.The request methods that must be supported by a conforming user agent, defined by the W3C draft for the XMLHttpRequest object, are currently listed as the following.

                                                            GET (Supported by Internet Explorer 7 (and later), Mozilla 1+)

                                                            POST (Supported by Internet Explorer 7 (and later), Mozilla 1 (and later))

                                                HEAD (Supported by Internet Explorer 7 (and later))

                                                PUT

                                                DELETE

                                                OPTIONS (Supported by Internet Explorer 7 (and later))

               However, request methods are not limited to the ones listed above. The W3C draft states that a browser may support additional request methods at their own discretion. The second parameter of the method is another text string, this one indicating the URL of the HTTP request. The W3C recommends that browsers should raise an error and not allow the request of a URL with either a different port or ihost URI component from the current document.The third parameter, a boolean value indicating whether or not the request will be asynchronous, is not a required parameter by the W3C draft.

The default value of this parameter should be assumed to be true by a W3C conforming user agent if it is not provided. An asynchronous request ("true") will not wait on a server response before continuing on with the execution of the current script. It will instead invoke the onreadystatechange event listener of the XMLHttpRequest object throughout the various stages of the request. A synchronous request ("false") however will block execution of the current script until the request has been completed, thus not invoking the onreadystatechange event listener.

               The fourth and fifth parameters are the username and password, respectively. These parameters, or just the username, may be provided for authentication and authorization if required by the server for this request.

The send method

To send an HTTP request, the send method of the XMLHttpRequest must be invoked. This method accepts a single parameter containing the content to be sent with the request.

                                                      Send( Data )

             This parameter may be omitted if no content needs to be sent.

After a successful and completed call to the send method of the XMLHttpRequest,responseText will contain the response of the server in plain text by a conforming user agent, regardless of whether or not it was understood as XML.

Integrate Google Chrome With VBA

Instead of opening url with Internet Explorer u can use Google Chrome to navigate l using VBA.







Sub test()

  Dim chromePath As String

  chromePath = "C:\Users\Username\AppData\Local\Google\Chrome\Application\chrome.exe"

  Shell (chromePath & " -url http:matrix.in")

End Sub