XmlHttpRequest – Http requests ter Excel VBA – Coding is Love
Excel is a powerful and most popular implement for gegevens analysis! HTTP requests te VBA gives extra capabilities to Excel. XmlHttpRequest object is used to make HTTP requests ter 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 verkeersopstopping and open VBA editor (Altstem + f11) > fresh module and begin writing code te a sub
Define http client using following code
If you need VBA’s Intellisense autocomplete then do it this way :
Very first, Add a reference to MSXML (Contraptions > references)
Select adequate version based on your PC :
1. Microsoft XML, v Trio.0.
Two. Microsoft XML, v Four.0 (if you have installed MSXML Four.0 separately).
Trio. Microsoft XML, v Five.0 (if you have installed Office 2003 – 2007 which provides MSXML Five.0 for Microsoft Office Applications).
Four. Microsoft XML, v 6.0 for latest versions of MS Office.
Then define http client
VBA Intellisense will vertoning you the right one when you embark typing.
Requests can be made using open and send methods. Open method syntax is spil goes after :
I’m using requestBin to test requests. Create a bin there and send requests to that URL to test requests.
A ordinary GET request would be :
Run this code, a message opbergruimte is displayed with the response of the request.
Request headers can be set using setRequestHeader method. Examples :
Elementary Postbode request to send formdata
Postbode requests are used to send some gegevens, gegevens can be sent te Send method. A elementary Postbode request to send form gegevens :
Basic Authentication ter VBA
When wij 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 :
Here’s a paste of utility function that helps to encode string to Base64
Practical use cases
Practical use cases of http requests te VBA are unlimited. Some of them are pulling gegevens from Yahoo finance API, weather API, pulling orders from Ecommerce store admin panel, uploading products, retrieving web form gegevens to excel etc.
Read : Parse HTML te Excel VBA – Learn by parsing hacker news huis pagina where I retrieve a web pagina using HTTP GET request and parse its HTML to get gegevens from a web pagina.
Read JSON Api ter Excel VBA where I call JSON Apis using HTTP GET and Postbode requests.
If you have and questions or terugkoppeling, comment below.
Author: Ranjith kumar
A CA student by education, self trained coder by passion, loves to explore fresh technologies and believes ter learn by doing. View all posts by Ranjith kumar
what is the syntax to open Techopedia resfull service ter Excel VBA?
They provided they following informatie using curl
curl -H ‘Authorization: apikey foo:19765acd5546655c1a2888’
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 te VBA :
xmlhttp.setRequestHeader “Authorization”, “apikey ” + “abc123”
Hope that helps.
Hi Can you please provide information for Authorization Oauth 1.0
Hi Ketan, You have to send the access token along with the request ter a header. Here’s an example – https://codingislove.com/make-oauth-requests/
do you find anything related to your query? I am also looking for vba code using OAuth1.0 and downloading gegevens.
I can get the gegevens from the API but I am having trouble pasting the gegevens into Excel. My code sample is below.
When I check the responseText with msgBox, I see all the gegevens. But when I attempt pasting it into Excel, it only pastes the headers for some reason.
I also attempted putting the gegevens into a variable very first and then paste into Excel, but it’s the same result.
Dim iXMLHTTP Spil Object
Set iXMLHTTP = CreateObject(“MSXML2.ServerXMLHTTP”)
Dim sURL Spil String
iXMLHTTP.Open “GET”, sURL, False
iXMLHTTP.setRequestHeader “Content-Type”, “text/xml”
can you tell your API URL used to samenvatting gegevens?
[API KEY] being the flagrante key
It might be because of the formatting or special characters te the response. Can’t tell without accessing the API with a key. Check the response text for special characters and line violates.
You are using their CSV API now. You can also attempt using their XML and JSON API.
It doesn’t seem to support xml.
But here is a sample of the output. It only pastes the very first line.
Moving the discussion to Forum. It is effortless to postbode code and communicate there. You can find the discussion here – https://forum.codingislove.com/topic/Trio/unable-to-paste-api-data-to-excel
hi scott, can u send mij the accomplish code for oauth1.0 ter excel?
this is the curl i use for my api. how do i write it te vba?
thanks ter advance
Thank you very much
How can I send a postbode to a specific web pagina and also open and display this web pagina at the same time ?
You can make a Postbode request and get its response but you cannot actaully simulate a verdadero browser postbode request. If you are looking for browser automation then have a look at this – https://codingislove.com/browser-automation-in-excel-selenium/
Albeit Postbode request cannot be made directly.
Hello, my request te VBA needs login but I don’t manage to write the right code, this is the request (Chrome) :
Status Code:302 Found
Cache-Control:max-age=0, no-cache, no-store, must-revalidate
Date:Fri, Ten Mar 2018 12:42:01 GMT
Expires:Wed, 11 Jan 1984 05:00:00 GMT
Accept-Encoding:gzip, deflate, br
Cookie:ASP.NET_SessionId=k1ixtazl1a1gu, __RequestVerificationToken_L05FQQ2=6qV_EElO1LcuuIxun2_cnZb54TZJe-pbVjG0VnLkmjdE88hNWXL-k3c4PY89uP7STiP8j7USwPN57wIzqjQggRSJINz9SqXDh7FQwwW2GLSgXAg2, __utma=191880309.452072024.1488573681.1489145502.1489148060.Three, __utmb=191880309.9.Ten.1489148060, __utmc=191880309, __utmz=191880309.1488573681.1.1.utmcsr=(ongezouten)|utmccn=(onmiddellijk)|utmcmd=(none), fe_typo_user=758ccd26892baf680fd9fb0560abb767
User-Agent:Mozilla/Five.0 (Windows NT 6.1, Win64, x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/56.0.2924.87 Safari/537.36
Query String Parameters
Hi, share your existing VBA code.
Public Sub httplogin()
Dim result Spil String
Dim XMLHTTP Spil Fresh MSXML2.XMLHTTP60, myurl Spil String
XMLHTTP.Open “POST”, myurl, False
XMLHTTP.setRequestHeader “Content-Type”, “application/x-www-form-urlencoded”
did you everzwijn solve this?
I think it may be related to https, I’m having trouble posting to a login form ter https too.
Hey Mathieu, I left behind to reply to this comment! You are doing everything right except that there’s another form parameter that needs to be sent which is __RequestVerificationToken
This token is generated on the client side using some encryption. But I’m not sure whats the logic for generating this token. You can attempt to automate the login using Selenium or Internet explorer instead – https://codingislove.com/browser-automation-in-excel-selenium/
Hello – I am attempting to download .zip files containing one single .csv verkeersopstopping. Is there a plain way to do this? Below is the url with the .zip files…
Hi Lito, There’s a script here – Automatic download of browser content using Excel linksom and VBA. You can switch it a bit to make it work for zip files. The idea is to use URLDownloadToFile method.
Hey am attempting to achieve the following use case i got a authorization URL, contemporáneo URL , user name password and have to use 64 encode on the coetáneo URL to get a valid cookie to samenvatting the gegevens i need. I am attempting to use the winhttp class ter 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.
Hi Amit, You need not encode the authorisation URL itself. Just encode the username and password concatenated by a semicolon. Custom-built function to encode a string is mention te 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.
I have the following curl code which I am attempting to convert to VBA for excel
Request Type : Postbode
curl_setopt( $ch, CURLOPT_HTTPHEADER, array(
Here is the code that I came up with:
I keep getting this error:
X-Auth-Token has to be sent spil header according to the CURL request but you are sending it te the Postbode bod so your request is not authenticated. Attempt this –
Related movie: Binance API Key Setup
Is there any way to get a particular cookie during run time and pass it spil a header while sending this request. I want to do that ter chrome browser.
Did you mean copy a cookie from chrome dev contraptions and use it te VBA XML HTTP?
yes Ranjith, you are right
Then you can send the cookie te header like this – XMLHTTP.setRequestHeader “Cookie”, “cookiename=cookievalue”
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.
@Ranjith Can you look on the above question when you get time ?
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.
Wij have attempted the following code snippet for getting the Cookies from Chrome Browser ter Run Time.
While running the same, wij are getting the below error.
Run-time error -2147467259 (800004005) Unspecified Error.
Wij have attempted an alternative way, by using an object of Chrome/IE Browser. Wij 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
Wij are just commenced with Macros, and It will be indeed helpful If you could help us on this.
Hi Vishnu, There’s no way to access chrome cookies te VBA. getAllResponseHeaders() will give access to cookies which were set te xmlhttp’s response. Let’s say you logged ter to some webstek using a Postbode request then the webstek responds with a cookie which can be used to authenticate you further requests.
Why are you attempting to access chrome cookies?
Thanks for writing this tutorial.
I attempted using your code above for sending a Postbode call and got an error message for the following definition: xmlhttp Spil Fresh MSXML2.xmlhttp
“Compile error: User-Defined type not defined”
Any clue what the error could be?
1) I did add the reference to XML 6.0
Two) I’m on Excel 2013 and Windows Ten Enterprise
You missed to read this –
Thank you for posting this.
I am attempting to access an API whose response to the http postbode request is an excel verkeersopstopping (.xlsx). Do you know how I could treat this response, such spil loading the response into a workbook object?
Hi Robert, you have to save the response to XLSX opstopping (download ter ordinary terms) very first then access the gegevens ter it using regular VBA methods like workbooks.open
I’m attempting to implement the following CURL directive into Excel VBA Code :
But Te the HTTP Request of VBA Code, I don’t know how to use my específico csv verkeersopstopping. Could you please help mij for that? My VBA codes are spil go after: