Excel VBA 2a:Learning MSXML2.XMLHTTP

Introduction

What is MSXML2.XMLHTTP?

It is an object in Excel VBA for sending request to the server. YOu can use this object to get the response from the website.

MSXML2 objects in Excel VBA

There are many object in Excel VBA in the MSXML2
There should be a little diference between them, but I really don’t know the difference. Ha, maybe I will know in the future.
Now, 6.0 is the updated version.
So far, I just MSXML.XMLHTTP is work fine for me in Excel2010

Microsoft.XMLHTTP
Microsoft.XMLHTTP.1.0
Msxml2.ServerXMLHTTP
Msxml2.ServerXMLHTTP.3.0
Msxml2.ServerXMLHTTP.5.0
Msxml2.ServerXMLHTTP.6.0
Msxml2.XMLHTTP.3.0
Msxml2.XMLHTTP.5.0
Msxml2.XMLHTTP.6.0

---

Different between XMLHTTP &ServerXMLHTTP

From the web investigation, XMLHTTP is for client side application, ServerXMLHTTP is for server side, but either of them can do the same things, send to GET/POST request to a web-site.
But for some how, seems serverXMLHTTP is more powerful and secure than XMLHTTP.
Moreover, I will receive an error “refuse to connect” if using XMLHTTP in Excel VBA. BUt it is OK for ServerXMLHTTP.
More information, we can go here for more details.

Why I learn it?

There are 2 methods in Excel to scraping data from web:
1. Using IE object
2. Using XMLHTTP request
As I have tested in first method, it is too slow and sometimes give some run-time errors. It is not robust and efficiency. Therefore, I need to find another way to implement my simple Excel VBA and make it more faster.

Learning XMLHTTP

First of all, the simplest form of code:

Sub Hello()
    'Create an object

    Set XMLHTTP = CreateObject("MSXML2.XMLHTTP") 
     'Using .Open to send GET request
     XMLHTTP.Open "GET", "http://www.aastocks.com/tc/default.aspx", False
    XMLHTTP.send

    '.responseText, will give the response to string.
    MsgBox XMLHTTP.responseText
'
End Sub

However, XMLHTTP is not work for all website. If we go to http://www.google.com, it fails….I don’t know why…..
One solution is using ServerXMLHTTP.
If sucessful, we can see below result.

A string format of html documents, and that is what I need.
Using Excel VB, find the exact element on in the target html document.
What I need is to find the URL of the particular image so I can download form the web.
Next step is how to find that element using VB.

Conclusion

-Know what is MSXML
-Know how to use XMLHTTP in Excel
-Note the failure of XMLHTTP

Reference

[1]Good Tutorial on simple web scraping
http://www.wiseowl.co.uk/blog/s393/scrape-website-html.htm
[2]To save a response to a file
http://www.vbaexpress.com/kb/getarticle.php?kb_id=799
[3]Sample of using MSXML2.XMLHTTP
http://scriptorium.serve-it.nl/view.php?sid=40
[4]使用正确版本的XMLHTTP
http://demon.tw/programming/the-right-version-of-xmlhttp.html