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 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.

Author: Ranjith kumar

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

53 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.

  4. Hello, my request in VBA needs login but I don’t manage to write the right code, this is the request (Chrome) :

    General
    Request URL:https://www.crt.asso.fr/NEA/Account/…nUrl=%2FNEA%2F
    Request MethodOST
    Status Code:302 Found

    Response Headers
    Cache-Control:max-age=0, no-cache, no-store, must-revalidate
    Connection:Keep-Alive
    Content-Length:122
    Content-Type:text/html; charset=utf-8
    Date:Fri, 10 Mar 2017 12:42:01 GMT
    Expires:Wed, 11 Jan 1984 05:00:00 GMT
    Keep-Alive:timeout=5, max=99
    Location:/NEA/
    Pragma:no-cache
    Server:Microsoft-IIS/7.5
    Set-Cookie:.ASPXAUTH=0B9A76034C6659905F9D6B4F1881D6DA51889592443; path=/
    X-AspNet-Version:4.0.30319
    X-AspNetMvc-Version:4.0
    X-Content-Type-Options:nosniff
    X-Frame-Options:sameorigin
    X-Powered-By:ASP.NET
    Remote Address:81.255.195.114:443

    Request Headers
    Accept:text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,/;q=0.8
    Accept-Encoding:gzip, deflate, br
    Accept-Language:fr-FR,fr;q=0.8
    Cache-Control:max-age=0
    Connection:keep-alive
    Content-Length:205
    Content-Type:application/x-www-form-urlencoded
    Cookie:ASP.NET_SessionId=k1ixtazl1a1gu; __RequestVerificationToken_L05FQQ2=6qV_EElO1LcuuIxun2_cnZb54TZJe-pbVjG0VnLkmjdE88hNWXL-k3c4PY89uP7STiP8j7USwPN57wIzqjQggRSJINz9SqXDh7FQwwW2GLSgXAg2; __utma=191880309.452072024.1488573681.1489145502.1489148060.3; __utmb=191880309.9.10.1489148060; __utmc=191880309; __utmz=191880309.1488573681.1.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(none); fe_typo_user=758ccd26892baf680fd9fb0560abb767
    Host:www.crt.asso.fr
    Origin:https://www.crt.asso.fr
    Referer:https://www.crt.asso.fr/NEA/Account/…nUrl=%2fNEA%2f
    Upgrade-Insecure-Requests:1
    User-Agent:Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/56.0.2924.87 Safari/537.36

    Query String Parameters
    returnUrl:/NEA/

    Form Data
    __RequestVerificationToken:iFUBQb5dMsakBQAgHdELh0VOb
    codeAffilie:XXXXXX
    key:X
    password:XXXX

      1. Public Sub httplogin()
        Dim result As String
        Dim XMLHTTP As New MSXML2.XMLHTTP60, myurl As String
        myurl = “https://www.crt.asso.fr/NEA/Account/Login?returnUrl=%2FNEA%2F”
        XMLHTTP.Open “POST”, myurl, False
        XMLHTTP.setRequestHeader “Content-Type”, “application/x-www-form-urlencoded”
        XMLHTTP.Send “codeAffilie=XXXXXX&key=X&password=XXXX”
        MsgBox (XMLHTTP.responseText)
        End Sub

        Many thanks

  5. Hey am trying to achieve the following use case i got a authorization URL, actual URL , user name password and have to use 64 encode on the actual URL to get a valid cookie to extract the data i need. I am trying to use the winhttp class in VBA to pass the user and and password but not sure what syntax do i need to pass the 64 bit encode URL, can you please help.

    1. Hi Amit, You need not encode the authorisation URL itself. Just encode the username and password concatenated by a semicolon. Custom function to encode a string is mention in the article.

      Then pass the encoded string with header named “Authorization”

      "Authorization", "Basic " + Base64Encode(user + ":" + password)

      You may use any client like WinHTTP on XMLHTTP but the “Basic” authentication process remains the same.

  6. I have the following curl code which I am trying to convert to VBA for excel

    Endpoint : http://api.datadoctorit.com/email-validate
    Request Type : POST
    curl_setopt( $ch, CURLOPT_HTTPHEADER, array(
    ‘Content-Type: application/json’,
    ‘Content-Length: ‘ . strlen( $data ),
    ‘X-Auth-Token: XXXXXXX’
    ) );
    Here is the code that I came up with:

    Dim strURL As String
    Dim hReq As WinHttpRequest: Set hReq = CreateObject("WinHttp.WinHttpRequest.5.1")
    Dim strResp As String
    Dim Token As String
    Token = mytoken
    strURL = "http://api.datadoctorit.com/email-validate"
    hReq.Open "POST", strURL, False
    hReq.SetRequestHeader "Content-Type", "application/json"
    hReq.Send "X-Auth-Token" & ":" & token
    strResp = hReq.ResponseText
    MsgBox strResp
    

    I keep getting this error:

    ” {“”error””:””You need to sign in or sign up before continuing.””}”

    1. Hi Dave,
      X-Auth-Token has to be sent as header according to the CURL request but you are sending it in the POST body so your request is not authenticated. Try this –

      hReq.SetRequestHeader "X-Auth-Token", token
      hReq.Send "Any other data to be sent in body"
      
  7. Hi Ranjith,

    Is there any way to get a particular cookie during run time and pass it as a header while sending this request. I want to do that in chrome browser.

    Thanks,
    Arun James

        1. Then you can send the cookie in header like this – XMLHTTP.setRequestHeader “Cookie”, “cookiename=cookievalue”

          1. Thanks for the reply. That helped , is there a way I can read those cookie dynamically during run time and pass it. Coz the cookie that I am looking is session id which will vary always.

    1. Hi Arun, VBA takes care of cookies and sessions automatically but if you want to read them then you can use XMLHTTP.getAllResponseHeaders() to get all response headers and parse it to get your cookie or session.

      1. We have tried the following code snippet for getting the Cookies from Chrome Browser in Run Time.

        Set Xobj = CreateObject(MSXML2.XMLHTTP”)
        ChromePath = “”C:\Program Files (x86)\Google\Chrome\Application\chrome.exe”””
        Returnvalue = Shell(ChromePath & https://google.com”, vbNormalFocus)
        StrCookie =Xobj.getAllResponseHeaders()
        End Sub
        

        While running the same, we are getting the below error.
        “Run-time error ‘-2147467259 (800004005) Unspecified Error.”
        We have tried an alternative way, by using an object of Chrome/IE Browser. We are able to Create Object for IE, but for Chrome its displaying error.
        Is there any way for Creating an object for Chrome browser, like the below line of code
        “ Set MyIE=CreateObject(“InternetExplorer.Application”) which is creating Object for IE browser

        We are just started with Macros, and It will be really helpful If you could help us on this.

        1. Hi Vishnu, There’s no way to access chrome cookies in VBA. getAllResponseHeaders() will give access to cookies which were set in xmlhttp’s response. Let’s say you logged in to some website using a POST request then the website responds with a cookie which can be used to authenticate you further requests.

          Why are you trying to access chrome cookies?

  8. hi Ranjit,

    Thanks for writing this tutorial.
    I tried using your code above for sending a POST call and got an error message for the following definition: xmlhttp As New MSXML2.xmlhttp
    “Compile error: User-Defined type not defined”

    Any clue what the error could be?

    Some notes:
    1) I did add the reference to XML 6.0
    2) I’m on Excel 2013 and Windows 10 Enterprise

    Thanks!

    1. Hi Karim,

      You missed to read this –

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

    Thank you for posting this.

    I am trying to access an API whose response to the http post request is an excel file (.xlsx). Do you know how I could handle this response, such as loading the response into a workbook object?

    Thank you.

  10. Dear Ranjith,

    I’m trying to implement the following CURL command into Excel VBA Code :

    curl -H “api_key:5e8e9773-78ce-11e6-884b-f1ceeb96” -X POST –data-urlencode [email protected]:\work\Master.csv http://kf-xxxxx.appspot.com/api/1/TestingMaster/csv

    But In the HTTP Request of VBA Code, I don’t know how to use my local csv file. Could you please help me for that? My VBA codes are as follow:

    Dim oRequest As Object
    
    Set oRequest = CreateObject("MSXML2.XMLHTTP")
    
    oRequest.Open "POST", "http://kf-xxxxx.appspot.com/api/1/TestingMaster/csv", False
    oRequest.setRequestHeader "Content-Type", "text/csv"
    oRequest.setRequestHeader "api_key", "5e8e9773-78ce-11e6-884b-f1ceeb96"
    oRequest.setRequestHeader "Content-disposition", "attachment; filename=Master.csv"
    
    oRequest.send
    

    Thank you very much.

  11. Hi there!

    Using the example shared regarding “POST” requests in VBA – I tried changing the POST method to UPDATE but it creates duplicate entries instead of updating the date of the API.

    I’ve tested the API and the UPDATE method is supported and works fine but when to do this from Excel, a duplicate record is created.

    Do you have any suggestion for making UPDATE requests from Excel? I’m using VBA JSON latest version.

    Thank you!

    1. Hi Rehan, You should use PUT for updating. Its not UPDATE. If PUT doesn’t work then try PATCH. It depends on the way API is designed.

      1. Ranjith,

        Thank you for your reply. I’m using PUT (Sorry for not mentioning this earlier) request but that is creating duplicates. Can you please confirm two things:

        Is the Convert.bas causing this issue?

        Do you have any example of PUT request that I can double check my
        request with?

        I was under impression that the API has a bug but tested that with Postman and it worked fine.

        Really appreciate your help with this! 🙂

          1. Hey!

            Here is the Code.

            Public Sub exceljson()
            Dim http As Object, JSON As Object, i As Integer
            Set http = CreateObject("MSXML2.XMLHTTP")
            http.Open "PUT", "https://www.humanity.com/api/v2/employees/3579550?access_token=d397baea8833d805a1a4a474209d6703e5208a06", False
            http.Send
            Set JSON = ParseJson(http.ResponseText)
            i = 2
            For Each Item In JSON("data")
            Sheets(1).Cells(i, 1).Value = Item("name")
            i = i + 1
            Next
            MsgBox ("complete")
            End Sub
            
          2. Reading documentation solves most of the problems. First, read the documentation from here clearly – https://www.humanity.com/api/rest/. They don’t follow the REST architecture completely.

            They follow this approach – Every request either POST or UPDATE or DELETE is made through POST request only. Appropriate method and request data should be sent in the data field of a POST request. Also, This data should be URL Encoded and a Content-Type header application/x-www-form-urlencoded should also be sent.

            If you want to update an employee’s email then here’s the code – https://bin.codingislove.com/uqihoxinej.vbs Assuming that you have already setup VBA-JSON library from JSON in VBA article

            It’s not about copy-pasting code snippets. Read the documentation, understand the code and adapt the code!

  12. Hi,

    I am making an API call from Excel VBA. Sometimes the Jsoncoverter gives an error, since the string i receive from the API is incorrect. I was told i need to check the “http response header code” first to validate the response. Although i dont see a http response header code. Do you know where i can find this? Thanks!

  13. Thanks very much for writing this tutorial and for taking the time to answer the questions!
    I need to access a file which is in an intranet site. When I download the file through the browser I get authenticated with my windows credentials, the browsers sends a negotiate call. I’d like to access the files directly from excel with vba, but I don’t know how to create the code so that the server authenticates the user.
    Could you point me to right direction? I’ve searched the entire web looking for an answer.
    Most grateful.

      1. Hi!I’ve no technical knowledge of exactly what type of intranet it is, but we have several self produced sites which requires single sign-on/ windows authentication. These sites are not available from outside the network. What I know is that there is no Google, Amazon or Sharepoint involved 🙂

Leave a Reply

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