XmlHttpRequest – Http requests in Excel VBA

Excel is a powerful and most popular tool for data analysis! HTTP requests in VBA gives additional capabilities to Excel. XmlHttpRequest object is used to make HTTP requests in VBA. HTTP requests can be used to interact with a web service, API or even websites. Let’s understand how it works.

xmlhttprequest in excel vba

Open an excel file and open VBA editor (Alt + f11) > new module and start writing code in a sub

Public sub XmlHttpTutorial

End Sub

Define XMLHttpRequest

Define http client using following code

Dim xmlhttp as object
Set xmlhttp = CreateObject("MSXML2.serverXMLHTTP")

If you need VBA’s Intellisense autocomplete then do it this way :

First, Add a reference to MSXML (Tools > references)

Select appropriate version based on your PC :
1. Microsoft XML, v 3.0.
2. Microsoft XML, v 4.0 (if you have installed MSXML 4.0 separately).
3. Microsoft XML, v 5.0 (if you have installed Office 2003 – 2007 which provides MSXML 5.0 for Microsoft Office Applications).
4. Microsoft XML, v 6.0 for latest versions of MS Office.

Then define http client

Dim xmlhttp As New MSXML2.XMLHTTP
'Dim xmlhttp As New MSXML2.XMLHTTP60 for Microsoft XML, v 6.0 

VBA Intellisense will show you the right one when you start typing.

Make requests

Requests can be made using open and send methods. Open method syntax is as follows :

xmlhttp.Open Method, URL, async(true or false)

I’m using requestBin to test requests. Create a bin there and send requests to that URL to test requests.

A simple GET request would be :

Dim xmlhttp As New MSXML2.XMLHTTP60, myurl As String
myurl = "http://requestb.in/15oxrjh1" //replace with your URL
xmlhttp.Open "GET", myurl, False
xmlhttp.Send
MsgBox(xmlhttp.responseText)

Run this code, a message box is displayed with response of the request.

Request headers

Request headers can be set using setRequestHeader method. Examples :

xmlhttp.setRequestHeader "Content-Type", "text/json"
xmlhttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
xmlhttp.setRequestHeader "User-Agent", "Mozilla/5.0 (iPad; U; CPU OS 3_2_1 like Mac OS X; en-us) AppleWebKit/531.21.10 (KHTML, like Gecko) Mobile/7B405"
xmlhttp.setRequestHeader "Authorization", AuthCredentials

Simple POST request to send formdata

POST requests are used to send some data, data can be sent in Send method. A simple POST request to send form data :

Public Sub httpclient()
Dim xmlhttp As New MSXML2.XMLHTTP, myurl As String
myurl = "http://requestb.in/15oxrjh1"
xmlhttp.Open "POST", myurl, False
xmlhttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
xmlhttp.Send "name=codingislove&email=admin@codingislove.com"
MsgBox (xmlhttp.responseText)
End Sub

Basic Authentication in VBA

When we need to access web services with basic authentication, A username and password have to be sent with the Authorization header. Username and password should also be base64 encoded. Example :

user = "someusername"
password = "somepassword"
xmlhttp.setRequestHeader "Authorization", "Basic " + Base64Encode(user + ":" + password)

Here’s a paste of utility function that helps to encode string to Base64

Practical use cases

Practical use cases of http requests in VBA are unlimited. Some of them are pulling data from Yahoo finance API, weather API, pulling orders from Ecommerce store admin panel, uploading products, retrieving web form data to excel etc.

Read : Parse HTML in Excel VBA – Learn by parsing hacker news home page where I retrieve a web page using HTTP GET request and parse its HTML to get data from a web page.

If you have and questions or feedback, comment below.

Get notified when there's a new post.

Need some help? Post your questions on our forum

Author: Ranjith kumar

A CA student by education, self taught coder by passion, loves to explore new technologies and believes in learn by doing.

16 Thoughts

  1. what is the syntax to open Techopedia resfull service in Excel VBA?
    They provided they following info using curl

    curl -H ‘Authorization: apikey foo:19765acd5546655c1a2888’
    ‘http://api.technopedia.com/api/v1/manufacturer/?format=json’

    1. Hi George, That curl information explains the following : Send a header with name ‘Authorization’ and value ‘apikey ‘ + yourapikey

      If you API key is ‘abc123’, then you can do it this way in VBA :

      xmlhttp.setRequestHeader "Authorization", "apikey " + "abc123"

      Hope that helps.

  2. Hi Can you please provide information for Authorization Oauth 1.0
    type.

    I have

    consumer_key: CONSUMER_KEY
    , consumer_secret: CONSUMER_SECRET
    , token: token_no
    , token_secret: secret_key

    in get method

  3. Hi,

    I can get the data from the API but I am having trouble pasting the data into Excel. My code sample is below.

    When I check the responseText with msgBox, I see all the data. But when I try pasting it into Excel, it only pastes the headers for some reason.

    I also tried putting the data into a variable first and then paste into Excel, but it’s the same result.

    Dim iXMLHTTP As Object
    Set iXMLHTTP = CreateObject(“MSXML2.ServerXMLHTTP”)
    Dim sURL As String

    iXMLHTTP.Open "GET", sURL, False
    iXMLHTTP.setRequestHeader "Content-Type", "text/xml"
    iXMLHTTP.send

    Range(“A1”) = iXMLHTTP.responseText

    Thanks

        1. It might be because of the formatting or special characters in the response. Can’t tell without accessing the API with a key. Check the response text for special characters and line breaks.

          You are using their CSV API now. You can also try using their XML and JSON API.

Leave a Reply

Your email address will not be published. Required fields are marked *