Web Scraping in Excel VBA Using XMLHTTP Method – Beginner to Advanced

What is XMLHTTP?

XMLHTTP is an object in VBA that allows Excel to communicate directly with a web server using the HTTP protocol. In simple terms, it helps Excel send requests to a website and receive data in response, without opening a browser.

Using XMLHTTP, Excel can fetch website content such as HTML, JSON, or XML data and then process that data using VBA. This makes XMLHTTP one of the fastest and most efficient methods for web scraping in Excel VBA.

2.1 XMLHTTP Method (Most Popular)

Yah kaise kaam karta hai: Website ko HTTP request bhejta hai aur HTML response lata hai.

Object library required:-

(Tools → References → Microsoft XML, v6.0)
(Tools → References → Microsoft HTML Object Library)

Features:-

Fast

Background me kaam karta hai

API + static websites ke liye best

Limitations:-

JavaScript load data nahi milta

Best for:-

Static sites
APIs
Tables & text data

Basic XMLHTTP VBA Code

Excel VBA Code

 
Sub XMLHttp_Web_Scraping()

    ' 1 Create XMLHTTP object (Early Binding)
    Dim http As MSXML2.XMLHTTP60
    Set http = New MSXML2.XMLHTTP60
    
    ' 2. Send HTTP GET request
    http.Open "GET", "https://stats.espncricinfo.com/ci/engine/player/253802.html?class=3;template=results;type=allround", False
    http.send
    
    ' 3 Check request status
    If http.Status <> 200 Then
        MsgBox "Request Failed. Status: " & http.Status
        Exit Sub
    End If

    ' 4. Load response into HTMLDoc
    Dim HTMLDoc As MSHTML.HTMLDocument
    Set HTMLDoc = New MSHTML.HTMLDocument
    HTMLDoc.body.innerHTML = http.responseText
    
    ' 5 Ready to extract
    ' lets extract all tables
    Dim HTMLTables As MSHTML.IHTMLElementCollection
    Set HTMLTables = HTMLDoc.getElementsByTagName("table")
    
    Debug.Print "No of tables: " & HTMLTables.Length ' Total tables count karke batayega
    
    ' 6 Loop through tables & print class name
    Dim HTMLTable As MSHTML.IHTMLElement
    
    For Each HTMLTable In HTMLTables
        Debug.Print _
            "TagName: " & HTMLTable.tagName & _
            " | Class: " & HTMLTable.className & _
            " | ID: " & HTMLTable.ID
    Next HTMLTable
    
End Sub

h

doc