XmlHttpRequest - Http Requests In Excel VBA - Coding Is Love

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.

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 the 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 protected]" 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.

Read How to build a simple weather app in Excel VBA where I make a HTTP Get request to weather API

Read JSON Api in Excel VBA where I call JSON Apis using HTTP GET and POST requests.

If you have and questions or feedback, comment below.

Get notified when there's a new post by clicking onin bottom left

Need some help? Post your questions on our forum

Leave this field empty if you're human:

• Author
• Recent Posts

Ranjith kumarA CA- by education, self taught coder by passion, loves to explore new technologies and believes in learn by doing. Latest posts by Ranjith kumar (see all)

• Ultimate Guide: Build A Mobile E-commerce App With React Native And Medusa.js - February 15, 2025
• Flutter lookup failed in @fields error (solved) - July 14, 2023
• Free open source alternative to Notion along with AI - July 13, 2023