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

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.

128 Replies to “XmlHttpRequest – Http requests in Excel VBA”

  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’

    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

    I have

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

    in get method

    1. Hi Ketan,

      do you find anything related to your query? I am also looking for vba code using OAuth1.0 and downloading data.

  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"

    Range(“A1”) = iXMLHTTP.responseText


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

    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
    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
    Set-Cookie:.ASPXAUTH=0B9A76034C6659905F9D6B4F1881D6DA51889592443; path=/
    Remote Address:

    Request Headers
    Accept-Encoding:gzip, deflate, br
    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
    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

    Form Data

      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

        1. Hey Mathieu, I forgot 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 try to automate the login using Selenium or Internet explorer instead – https://codingislove.com/browser-automation-in-excel-selenium/

  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.

    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


    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"

    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! 🙂

  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 🙂

      1. Hi J, You need not send multiple requests here. When you open that login page in a browser, It makes a GET request first and then makes a POST request to the same page with login credentials. If you want to login to this page then just make a POST request with form data.

        1. Thank you. The page is a JSF page. The first .post accomplishes the navigation to the second page.

          I cannot make a second POST or GET without an OPEN then SEND.

          I do not know what URL to use as the second URL or the format of the second GET or POST request.

          'Dim xXML As Object
          'Set xXML = CreateObject("MSXML2.ServerXMLHTTP")
          'xXML.Open "GET",??? , True
          'xXML.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
          'xXML.send "name=searchForm:lastName=testlastname"
          'MsgBox (xXML.responseText)


  14. Thank you for this wonderful blog. As soon as I complete and clean the data extraction code, I want to work on the JSON lecture that you present. I think I may have a way to bring traffic to your JSON lecture regarding Excel to JSON.

  15. Hello, I am having trouble with the API requesting multiple headers.
    How would I go about writing a header section for this?
    I have all of the required information, I just need help with the code.
    Thank you in advance.

    All REST requests must contain the following headers:

    CB-ACCESS-KEY : The api key as a string.
    CB-ACCESS-SIGN : The base64-encoded signature .
    CB-ACCESS-TIMESTAMP : A timestamp for your request.
    CB-ACCESS-PASSPHRASE : The passphrase you specified when creating the API key.

    All request bodies should have content type application/json and be valid JSON.

    1. Hi Tyler, Check the requests headers section in the article. You can set headers like this

      xmlhttp.setRequestHeader "CB-ACCESS-KEY", "yourapikey"
      xmlhttp.setRequestHeader "CB-ACCESS-SIGN", Base64Encode("yoursignature string")

      You can also find a utility function in the article which helps in encoding a string to base64

  16. Ranjith, I may have spoke too soon. I am having trouble generating the sign in my question above. I really appreciate your help on my last question, could you help me out with this as well?


    This link describes the process, it’s simple but when I attempt to join the different parts it throws a “you must use cb-access-sign”

  17. Hello Ranjith,

    Thank you for the excellent tutorial for XHR.

    Would you have additional courses on XHRs that have mandatory cookie responses.

    I have mapped out the navigation / CLICKS with the XHR that occurs after the CLICKS. There are four (4) navigation’s:

    Cookie acceptance
    Enter Search terms
    Click DETAIL link from search results
    Next page navigation

    Of these four, I have collected:

    GENERAL XHR Request URL and Request Method

    Please, would you have examples of:

    Click DETAIL link from search results
    Next page navigation

    I have searched for examples and have not found any examples of your quality, that executes XHR POST and GET.

    Any suggestions greatly appreciated.

  18. Bonjour les pros,

    Je bute sur un pb entre xml et mshtml dans l’instruction :
    Dim IE As New InternetExplorer
    Dim IEDoc As HTMLDocument
    set htmlinput = IEDoc.all(“OK”)
    htmlinput.Click : qui fonctionne correctement (object HTMLInputElement)

    Dim XMLReq As New MSXML2.XMLHTTP60
    Dim HTMLDoc As New MSHTML.HTMLDocument
    Set htmlinput = HTMLDoc.all(“OK”)
    Debug.Print htmlinput, HTMLDoc.all(“OK”)
    htmlinput.Click : qui ne fonctionne pas correctement (objet vide)

    merci de me dire ce qui ne va pas

  19. Hi Ranjith,

    Below is the code snippet about trying to add custom HTTP header before sending the request.
    Code stops on .setRequestHeader “X-SPP-API-Token”, apiToken and the VBA error is “The parameter is incorrect” Any idea what is wrong with this?

    Dim xmlhtp As New WinHttpRequest
    Dim sURL As String
    Dim apiToken As String
        With xmlhtp
            .SetClientCertificate "CURRENT_USER\MY\xxxxxxxxxx"
            .Open "post", sURL, False
            .setRequestHeader "Host", "api.spp.org"
            .setRequestHeader "Content-Type", "text/xml; charset=utf-8"
            .setRequestHeader "X-SPP-API-Token", 
            .send sXML
        end with

    Thanks again for your help

      1. Thanks for prompt reply Ranjith.
        The apiToken is a string of 147 characters and it includes special characters like “-“, “:”, “=”, “/”. I tried passing a dummy string of <50 characters along with special characters and it didn’t return an error on setRequestHeader “X-SPP-API-Token”

        So, I guess the problem may be with length of string/header? Do you have any idea about max length of header that can be passed using WinHttpRequest? And if it is less than 147, do you happen to know an alternative solution to this problem?

        Thanks again Ranjith, this is really helpful.


        1. Hey Chirag, There’s no limit on header length. The issue might be with the special characters. Check the API’s documentation also.

  20. Hi, i uses this function to.
    Can i disable the responseText.
    I’ am not interested in the response, because i think it takes a lot of time to wait for the reponse.

  21. Hello Ranjith,
    Just wanted to say thanks for the example. I had been trying to move a “WEBSERVICE(url)” in Excel into a VBA call and could not figure it out. Your example helped tremendously. And it turned out to be much simpler than other ways I saw on the web.

    Here is the routing for getting a stock quote in case it can help anyone: https://bin.codingislove.com/ayaqosovot.vbs

  22. Hello CondingIsLove !

    I am struggling to retrieve the text shown below, from the web https://www.six-swiss-exchange.com/bonds/ (you can find by going to the html editor, and searching HK) .

    It looks like this content in the HTML code is a dynamically loaded json… but using your method there is little I can retrieve. Maybe you have some brilliant idea?


  23. Hi Ranjith,
    Thank you very much for the awesome blog.
    I have below query.
    The web-service i am going to access is using https and TLS 1.2 protocol.
    We were sending request to the web service using “WinHttp.WinHttpRequest.5.1” but since it is upgraded to https and TLS 1.2 protocol it is not working.
    Will MSXML2.serverXMLHTTP supports https and TLS 1.2 ?
    Also is it possible to sent the certificate as header with MSXML2.serverXMLHTTP object?


    1. Hi Sreejith, MSXML2 does support https. There is nothing like “Sending certificate as header”! SSL certificate is installed on your server and you cannot send it anywhere.

  24. Please, any idea why this is not working below:

    ‘Microsoft Internet Controls Library
    ‘Microsoft HTML Object Library
    ‘Microsoft XML 6.0
    Dim xmlhttp As Object, xmlhtml As New HTMLDocument
    Set xmlhttp = CreateObject(“MSXML2.XMLHTTP”)
    ‘Dim xmlhttp As New MSXML2.xmlhttp
    xmlhttp.Open “GET”, “https://www.google.com/search?q=” & “San+Francisco”, False ‘ Cells(xx, “B”).Value, False
    xmlhttp.setRequestHeader “Content-Type”, “application/x-www-form-urlencoded”
    Application.Wait Now + TimeValue(“0:00:03”) ‘SLOW DOWN
    xmlhtml.body.innerHTML = xmlhttp.responseText
    Debug.Print xmlhttp.responseText

    1. Sub teste1()

      ‘Microsoft Internet Controls Library
      ‘Microsoft HTML Object Library
      ‘Microsoft XML 6.0
      Dim xmlhttp As Object, xmlhtml As New HTMLDocument
      Set xmlhttp = CreateObject(“MSXML2.XMLHTTP”)
      ‘Dim xmlhttp As New MSXML2.xmlhttp
      xmlhttp.Open “GET”, “https://www.google.com/search?q=” & “San+Francisco”, False ‘ Cells(xx, “B”).Value, False
      xmlhttp.setRequestHeader “Content – Type”, “application / x – www – form – urlencoded”
      Application.Wait Now + TimeValue(“0:00:03”) ‘SLOW DOWN
      xmlhtml.body.innerHTML = xmlhttp.responseText
      Debug.Print xmlhttp.responseText

      End Sub

  25. I am trying to send an Outlook msg file using Post, but can’t get the file to Post. I can only get the string of the filepath and name. Can you help with that?

  26. Hi, It seems one of my existing excel vba marco stopped working when I upgraded from WINDOWS7 to WINDOWS10. Can someone help to locate the issue and provide guidance to fix it.
    I did tried by adding Microsoft XML 3.0 or 6.0 reference but in both attempt I still get a blank xmlhttp.responseText.

    PS: I am using similar code what was shared here https://bin.codingislove.com/ayaqosovot.vbs
    The only diff is that I am using MSXML2.xmlhttp instead of MSXML2.xmlhttp60

  27. Hi Ranjith,
    I am learning VBA for xsl and automating a project.I am able to provide input data to an API which currently works .But i need to provide input data from from a xsl file ,lets say data.xsl which has input data in column 1 to 30 i,e C2 to C30 in sheet 1 . I tried few things but don’t seem to get the right syntax.Can you please help with that?
    I want parameter ‘st’ to read data from a xsl file .
    Here is how i am providing data .

    Dim http As Object
    Dim st As String

    Set http = CreateObject(“MSXML2.XMLHTTP”)

    st = “Site=SBA_Modeler_V30&Data={‘input_BirthDate’:’1991-12-08′,’input_HireDate’:’2012-01-01′,’input_Gender’:’M’,’input_AnnualPlanComp’:33500.22,’input_EmployeeClass’:’Special Risk’,’input_AnnualPayGrowth’:1,’input_MarketPerformance’:’Average’,’input_PVD’:34,’input_NRA’:56,’input_MBAA’:56,’input_Custom_BCD’:45,’input_Custom_TermAge’:27,’input_RemainingElections’:1,’input_Pre2011ServiceYears’:0,’input_ProjectedServiceYears’:1.75,’input_ProjectedBenefitAmount’:0,’input_DROP_LumpSum’:2285.95,’input_ProjBuyBackABO’:0,’input_DateABO’:2018-01-31,’input_ProjectedABO’:2285.95,’input_ProjectedAAL’:0,’input_DateAAL’:1900-01-01,’input_InvestmentBalanceTBA’:0,’input_CurrentABO’:1331.08}”

    http.Open “POST”, “https://beqlb02.poolt.hewitt.com/dsi0042/calculator”, False
    http.setRequestHeader “Content-type”, “application/x-www-form-urlencoded”
    http.send (st)

    1. Hi Lisa, You should be able to open another workbook using this code –

       myfilename = "C:\Users\....\data.xls"
       Set wb = Workbooks.Open(myfilename) 
  28. Actually I am working on a VBA project and got stuck in it with no help from anyone looks like the problem can not be resolved. But I have very high hopes from you .

    I want to scrap some stock data from a website called “https://dps.psx.com.pk/&#8221; I just need to input the quote symbol “EFOODS” on the top right corner box and it gives the details.That details I want to to scrap in excel using VBA as there are a lot of symbols and the task can be automated by scraping data to excel.

  29. Actually I am working on a VBA project and got stuck in it with no help from anyone looks like the problem can not be resolved. But I have very high hopes from you .

    I want to scrap some stock data from a website called “https://dps.psx.com.pk/” I just need to input the quote symbol “EFOODS” on the top right corner box and it gives the details.That details I want to to scrap in excel using VBA as there are a lot of symbols and the task can be automated by scraping data to excel.

  30. Thanks so much for this. Awesome website and tutorial.
    Could you please help?

    I have an issue, I need to have 2 authorization keys like in the below CURL format;
    curl -v -X GET “https://api.****.com/api/track?tracking_number={string}”
    -H “******-Api-Key: ”
    -H “Ocp-Apim-Subscription-Key: {subscription key}”

    I am getting an Error StatusCode:403 message: Forbidden

    I am using the below code (along with the base64encode function) and have scrubbed the passwords of course and tracking ID. I feel it is something to do with the 2 authorisation keys being on different lines.


    1. Hi, I don’t think that any API needs 2 authorization keys in the same request. If you set the Authorization header 2 times then the latter would just replace the former. Do check the documentation again.

  31. This is really awesome!! Thank you so much for sharing .

    I have a situation where users want to update data into webapp directly from excel. i am working on design where I need to
    a. ping the jave web server
    b. call a remote java method
    c. send the excel data to the server via java method.

    I managed to do the ‘part a’ but struggling with rest.

    Could you please help.


  32. …If you need VBA’s Intellisense autocomplete then do it this way …

    Excellent article except that you make it sound like Intellisense is the only benefit to using Early Binding (adding “Microsoft XML” through Tools > References, as opposed to Late Binding which, in the example uses CreateObject(“MSXML2.serverXMLHTTP”)) .

    There are many other benefits to using Early Binding including a substantial difference in performance/speed, and improved security, which is why “Microsoft recommends early binding in almost all cases.”

    See section “Which form of binding should I use?” in the official documentation here: https://support.microsoft.com/help/245115/using-early-binding-and-late-binding-in-automation

    The benefits of Early Binding far outweigh the “trouble” (or confusion) of having to check a checkbox in the ‘References’ dialog.

  33. Hi! Ranjit
    Everything here is awesome!! Thank you .
    By the way…can you help…
    how could be possible to use REST API to get a file an folder list on sharepoint?
    and have back an array to be then used in excel? I it right to prefer this strategy instead of drive mapping (and DOS commands) and FIleSystemObjects?
    tx for you reply

  34. Hi Ranjith Kumar

    I’m the man in the world who discovered a high performance for successful trading using Bitfinex Exchange, all analyzed, but … I could not open / update / close ORDERS. See the documentation here: https://docs.bitfinex.com/v2/reference#ws-input-order-new

    I’d rather use VBA in Microsoft Access, so the open data point is already ok, but the endpoints are not authenticated yet.

    Can you help me?
    Later I will donate part of my success to you!

    I see you!

  35. Hello Ranjith, I would like attribute to a variable or array, a xml tag only, because I need attribute it to a text box specific. Is this possible?

    file xml:

    sucesso – cep completo
    Padre Augusto

  36. HI I am using VBA macro for connecting Sharepoint. I am able to to get response but it is not in what I ma expecting.

    Private Sub ReadData2()

    Dim SOAPURL_List As String, SOAPListName As String, SOAPViewName As String, User As String, Password As String
    SOAPURL_List = “https://sitename.sharepoint.com/sites/site/_vti_bin/Lists.asmx”
    SOAPListName = “multiFile”

    Dim SOAPAction As String
    SOAPAction = “http://schemas.microsoft.com/sharepoint/soap/GetListItems”
    Dim SOAPEnvelope_Pre As String, SOAPEnvelope_Pst As String
    SOAPEnvelope_Pre = “” & _
    “” & _
    SOAPEnvelope_Pst = “” & _

    Dim SOAPMessage As String

    SOAPMessage = SOAPEnvelope_Pre & _
    ” ” & _
    ” ” & SOAPListName & “” & _
    ” ” & _

    ‘Dim Request As MSXML2.ServerXMLHTTP60
    ‘Set Request = New MSXML2.ServerXMLHTTP60

    Dim Request As MSXML2.XMLHTTP60
    Set Request = New MSXML2.XMLHTTP60

    Request.Open “POST”, SOAPURL_List, False
    Request.setRequestHeader “Content-Type”, “text/xml; charset=””UTF-8″””
    Request.setRequestHeader “SOAPAction”, SOAPAction
    ‘Request.setRequestHeader “X-FORMS_BASED_AUTH_ACCEPTED”, “f”

    Request.Send (SOAPMessage)
    MsgBox Request.readyState
    MsgBox Request.responseText

    Set getDataFromSharepoint = Request.responseXML.getElementsByTagName(“z:row”)

    MsgBox “End”

    End Sub

    Here I am getting responsetext but responsexml is empty .. Any way can I get responsexml. so I can convert the data to required format.

  37. i am trying to send a pdf file from my harddrive to docparser.com we have
    a login and secret key. they have an api with example but my application
    is in excel vba

    i keep getting a status of 400 i dont know what that is
    and i dont know how to set this up properly
    the goal
    having proper username, secretapikey, and parsercode
    i would send that pdf file to the parsercode once parsed back to excel
    it will return a for retrieval

    Sub ZDocParser()
    Dim URL As String, strResponse As String, fileContent As String, FileName As String
    Dim objHTTP As Object
    Dim objResults As Object

    FileName = "c:\users \documents\JOEK\Excel5\Automann\POG6050243.pdf"
    fileContent = "application/pdf"
    URL = "https://api.docparser.com/v1/document/upload/<parsercode>?api_key=<secretapikey>"

    Set objHTTP = CreateObject("MSXML2.XMLHTTP") '

    With objHTTP
    .Open "POST", URL, False
    .SetRequestHeader "Authorization", "Basic " & Base64Encode("<username>" & ":" & “<password”
    .SetRequestHeader "Content-Type", "application/pdf"
    .Send FileName

    ‘ strResponse = .ResponsetText

    End With

    Set objResults = Nothing
    Set objHTTP = Nothing

    End Sub

  38. Hello i have my code:
    Private Sub CommandButton1_Click()

    Dim xmlhttp As New MSXML2.xmlhttp, myurl As String

    myurl = “https://www.indicepa.gov.it:443/public-ws/WS01_SFE_CF.php”

    Dim Codice As String

    Codice = Cod_Fis.Text

    xmlhttp.Open “POST”, myurl, False

    xmlhttp.SetRequestHeader “Content-Type”, “application/x-www-form-urlencoded”

    xmlhttp.Send “AUTH_ID=XCUVNPWS&CF=Codice”

    MsgBox (xmlhttp.ResponseText)

    End Sub

    I need to send the Codice variable in the post parameters and i need to parse the json result in another text input box in my word document and i need the “cod_amm” data result in this box.
    Please help me.

  39. Greetings
    form Google translate web Page
    How to get url of Audio from Network after click Liston Button ?

    Public Sub Google_TTS()

    ClosingIE ' Close open IE
    Dim IE As InternetExplorer, Myurl As String
    Set IE = Nothing
    Set IE = CreateObject("InternetExplorer.application")
    IELang = "hl=" & "en"
    TrnsFrom = "sl=" & "en"
    TrnsTo = "tl=" & "ar"
    TXT = "Hellow" ' orActiveCell
    StrUrl = "https://translate.google.com/?" & IELang & "&tab=wT1&authuser=0#view=home&op=translate&" & TrnsFrom & "&" & TrnsTo & "&text=" & TXT
    With IE
    .Navigate StrUrl
    .Visible = True

    SendKeys String:="{F12}" ' call Inspect Elements

    While .Busy Or .ReadyState < 4: DoEvents: Wend

    Application.Wait Now + TimeValue("00:00:01")
    .Visible = True ' reactive IE for "Ctrl+4 " SendKeys String:="^4"

    ' Some time works smoe not work on 64 go to Network Tab from DOM Explorer
    SendKeys String:="^4" ' call Inspect Elements Network
    SendKeys String:="{NumLock}" 'Turn ON NumLock
    While .Busy Or .ReadyState < 4: DoEvents: Wend
    '.Document.queryselector(".ttsbutton").Click ' or
    .Document.queryselector(".src-tts").Click ' all of them work will
    'clicking "Liston" Button generate below Web address on Network Tab
    Myurl = "https://translate.google.com/translate_tts?ie=UTF-8&q=My%20Car&tl=en&total=1&idx=0&textlen=6&tk=944031.579890&client=webapp&prev=input"
    '.setRequestHeader "Content-Type", "audio/mpeg"

    ' see Public Sub XmlHttpTutorial()
    Call XmlHttpTutorial(Myurl)

    Set IE = Nothing
    End With

    End Sub
    Public Sub XmlHttpTutorial(Myurl As String)
    Dim xmlhttp As Object, DfltPth As String, FldPth As String, FilePath As String
    Set xmlhttp = CreateObject("MSXML2.serverXMLHTTP")

    DfltPth = Environ("USERPROFILE") & "\Desktop\"
    FldPth = DfltPth & "Audio Media Folder\"
    If Dir(Trim(FldPth)) <> "" Then
    On Error Resume Next
    MkDir FldPth
    On Error GoTo 0
    End If

    FilePath = FldPth & "TTs.mp3"
    Myurl = "https://translate.google.com/translate_tts?ie=UTF-8&q=My%20Car&tl=en&total=1&idx=0&textlen=6&tk=944031.579890&client=webapp&prev=input"
    With xmlhttp
    .Open "GET", Myurl, False
    .setRequestHeader "Content-Type", "audio/mpeg"

    Call DwnLdTTS2MP3(Myurl, FilePath)

    MsgBox "tk=" & Split(Split(Replace(Split(Split(.responseText, "/")(1), "")(0), "amp;", ""), "tk=")(1), "&")(0)

    End With

    End Sub
    Function DwnLdTTS2MP3(URLs As String, strHDLocation As String)
    Dim objXMLHTTP As Object, objStream As Object
    Dim U As Long
    Url = Split(URLs, "|")
    Set objXMLHTTP = CreateObject("Microsoft.XMLHTTP")
    Set objStream = CreateObject("Adodb.Stream")
    With objStream
    .Type = 1
    For U = LBound(Url) To UBound(Url)
    With objXMLHTTP
    .Open "GET", Url(U), False
    .setRequestHeader "Content-Type", "audio/mpeg"
    objStream.Write .responseBody
    End With
    Next U
    On Error GoTo ErrNote
    If Dir(strHDLocation) <> "" Then
    Kill strHDLocation
    End If

    .savetofile strHDLocation, 2
    End With
    MsgBox "Please go to:" & Chr(10) & strHDLocation & Chr(10) & Chr(10) & "And run Audio File MP3"

    Set objStream = Nothing
    Set objXMLHTTP = Nothing
    Exit Function
    MsgBox "Close Audio Fole"
    End Function

    Private Sub ClosingIE()

    On Error Resume Next
    Dim objWMI As Object, objProcess As Object, objProcesses As Object
    Set objWMI = GetObject("winmgmts://.")
    Set objProcesses = objWMI.ExecQuery("SELECT * FROM Win32_Process WHERE Name = 'iexplore.exe'")
    For Each objProcess In objProcesses
    Call objProcess.Terminate
    Set objProcesses = Nothing: Set objWMI = Nothing
    On Error GoTo 0
    End Sub

Leave a Reply

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