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.

Postbode navigation

98 Thoughts

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) :

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:

3 Responses

Yes, I will do a celebratory dance te gevelbreedte of the idiots who repeated the same misinformation on this thread. Again, simply because a financial company warns people ahead of time of potential misconduct does not eliminate the liability.

“Customers who wish to access both bitcoin (BTC) and bitcoin contant (BCC) need to withdraw bitcoin stored on Coinbase before 11.59 pm PT July 31, 2018. If you do not wish to access bitcoin metselspecie (BCC) then no activity is required.”

Yes, I will do a celebratory dance ter pui of the idiots who repeated the same misinformation on this thread. Again, simply because a financial company warns people ahead of time of potential misconduct does not eliminate the liability.