In May we published a tutorial that walked you through how to make HTTP GET requests to APIs. If you haven’t read that tutorial yet and you know nothing about APIs, I strongly recommend you read it before this one on HTTP POST requests. In it, I discussed types of APIs, the different kinds of calls, and explained authentication. This tutorial will assume you understand the concepts described in the GET article.

As I mentioned in the GET article, these API tutorials will be some of the most complicated on this site. They bring together several concepts in networking, security, and remote computing. However, the basic methods of making API requests is relatively simple, so don’t get discouraged. Your programs will become immeasurably more powerful when you incorporate remote data and processing, and you can impress your coworkers, boss, and friends with your newfound capabilities.

Let’s get started!

The Vocabulary

Before we start programming, let me define and clarify some terms.

I will use the words API call and API request interchangeably.

We have a few words from networking and computing to define, too.

Servers are remote machines that do processing upon request. Every website is hosted on a server (big ones are hosted on multiple servers). It is just a remote machine that serves requests.

Services are entities that do the processing or business for you. The API is often called a service. They can be operated by private businesses, governments, or organizations. Servers are the machines, and the services are the entities performing the operations to benefit the users.

In networking, there are client-side and server-side actions. Client-side actions occur and are processed on the user’s machine, while server-side actions occur, as you probably guessed, on the server. A server-side action might be for the server to gather all the data you requested, while a client-side action might be for the Excel macro on your computer to create a chart from the data it received from the server. The division defines who takes action and where the computation is physically happening.

Finally, in computing, there are two basic disk operations: read and write. Read gets data off the disk, while write puts or changes data on the disk. Just remember that these are disk operations, so even though the operation might occur remotely, there is a physical disk somewhere in the world that must do the read or write operation.

The POST Request and Computing Background

You may recall from the VBA HTTP GET tutorial that GET calls request information from the API service. It could be stock prices, weather data, or menu entries taken from a restaurant aggregation and rating service.

The basic concept is that you ask for information and the server sends you a response. In fact, any time you navigate to a webpage, there is a GET request made by your browser to retrieve the information.

If the information you requested was sensitive, like account information or medical data, you probably had to authenticate yourself before the service would respond with the data you wanted.

GET requests do just that: they get information. Your HTTP GET call may require the server to do some processing, such as aggregating data from several places or calculating metrics, but in the end, there are no permanent write operations on the server. The server only reads data, possibly manipulates it, and sends you the results.

If you want to make any changes server-side, though, you will need to use POST requests. In other words, if you want to make any changes to accounts or perform certain actions, you will need to use POST (or another HTTP action, like DELETE). These calls will cause the server to do permanent server-side write operations.

The VBA Code Skeleton

Let’s set up the code skeleton so we can jump right in. Make sure to set the Tools > References to the Microsoft XML, v6.0 library from your VBA Editor so you can access the MSXML2.ServerXMLHTTP60 object.

Sub skeleton_for_POST()
'Enable Microsoft XML, v6.0 under Tools > References
Dim apiURL, requestString, endpoint As String
Dim id_header_name, id_key, secret_header_name, secret_key As String
Dim request As MSXML2.ServerXMLHTTP60

id_header_name = "logon-id-key"
secret_header_name = "secret-pass-key"

id_key = "john.smith55"
secret_key = "SKjdfli23nmvfklj23lkjasklj3KLJDflk2j3r3"

apiURL = "" 'enter your URL here
endpoint = "" 'enter your endpoint info here


requestString = apiURL & endpoint


Set request = New ServerXMLHTTP60
request.Open "POST", requestString, False

request.setRequestHeader id_header_name, id_key
request.setRequestHeader secret_header_name, secret_key


request.send

'process the request.responseText with string manipulation functions or your own JSON parser
'  parseJSON request.responseText, parsedResponse

'do stuff with data


End Sub

Make powerful macros with our free VBA Developer Kit

Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit and our Big Book of Excel VBA Macros to supplement this tutorial. Grab them below and you’ll be writing powerful macros in no time.

I'll take a free VBA Developer Kit

The skeleton might look more like a full-fleshed body, but there’s a lot of blanks that must be filled in. Our VBA HTTP GET article explains the specifics for the initialization and setup of the MSXML2.ServerXMLHTTP60 object, how to create header references, and authentication.

Since you are asking the server to make changes or do extra processing, it is almost always necessary to authenticate yourself. If you don’t, the service won’t know under which account to record the changes. Moreover, the service may reject any of your requests that are not authenticated. This helps prevent some resource-wasting attack vectors that might otherwise be used against the service.

Setting Up the POST

We’re going to outline a template that many APIs will follow, but you’ll need to check the documentation of your particular API for the exact syntax. Checking the documentation is required since all APIs are different. With that said, this outline and our example using Google Forms will be a great start for you.

Because VBA is not very popular for API applications, writing API programs in VBA demands that you be resourceful and inventive. There’s not a lot of help online, so this tutorial will be critically important for getting you familiar with the skeleton of APIs and HTTP POST connections.

In all instances, though, you start to establish your POST connection by passing the string "POST", along with your URL request string to the .Open method of the MSXML2.ServerXMLHTTP60 variable. In the example above, this variable is named request.

Finding Your Credentials

Since you’ll need to authenticate most requests, somewhere in your account (for the service), you should find a term similar to API Key or Private Key or Generate API Secret Key. This will generate the secret key you need to paste into the secret_key variable in our skeleton example above. It will serve as the password for authentication so the API knows an authorized person is trying to connect.

You’ll also need to know your username. Sometimes the service will generate a unique identifying key for you. It might be a hashed or encrypted version of your plaintext username to prevent exposure over an unsecure connection. It might consist solely of alphanumeric characters to avoid complications with processing special characters (like the @ in most email-based usernames). Whatever it is, you’ll likely need some way to identify yourself before finalizing the API connection.

You can usually find these two pieces of information in the same place. It might be under Account or Dev Tools or some other sensical header. Sometimes your API key will be hidden every time you navigate away from the page, and you’ll need to generate a new one if you want to see it. Keep this in mind, as it can break your program if you forget to update your credentials in the code of your program.

Hiding your key is just an extra layer of security to stop unauthorized (but not unauthenticated) requests by someone who breaks into your account. This could be very bad if your account holds tens of thousands of dollars in stock, for example.

Identifying the Identifying Information

Now that you have your credentials, you’ll need to send them in the correct way to the server. Many APIs use headers to accomplish this.

Again, you’ll need to reference your API’s documentation for exact naming, but you might have something like these header names and values:

key_id = "PKLA502DMES8EA63U1HG"
key_header_name = "ABC-API-KEY-ID"

secret_key = "eRN3sZ9PWrQ2hLZB3h8jy/JhvVLJutgejPOn0qdTv"
secret_header_name = "ABC-API-SECRET-KEY"

When you add these headers to the request object, it will attach these pieces of information to the data sent over the wire. The API server will see the headers, authenticate the request, and start processing whatever is in the body of the request.

To add the header, you can use the .setRequestHeader method of the request object. Since we’ve already assigned identifying text to our strings, we can add the headers with these two lines of code:

request.setRequestHeader key_header_name, key_id
request.setRequestHeader secret_header_name, secret_key

The header name is first, then the header payload comes next. Simple, no?

Finding the Right Endpoint

Endpoints are the URL to which you make the request. The same endpoint can have both GET and POST methods. For example, an endpoint like www.apisample.com/v1/account will probably have both GET and POST capabilities. They’ll be related to account actions, assuming the API developers at the service were behaving logically.

Building the Request Body

Depending on your API, to action GET requests, you may simply append what you need to the end of the endpoint URL. For many stock price APIs, you might have the endpoint /stocks, and you just need to add a query string like ?ticker=aapl,msft&startdate=05162019&limit=20. You can then fire off the request with request.Send and you’ll get up to 20 results for stock prices for Apple and Microsoft starting on May 16th.

Usually for POST calls, though, you’ll need to send more information. Let’s say you want to purchase some stock. You’ll need to at least tell the service:

  1. the ticker
  2. the direction (buy/sell), and
  3. the number of shares

But most stock trading platforms will also let you choose:

  • type of order (market, limit, stop)
  • time in force (good-til-cancel, good-til-filled, etc.)
  • whether the order should be filled all at once or increments are acceptable
  • if pre/after market trading is acceptable, and
  • limits and stops (for non-market orders)

Limits and stops may be percentages, trailing, or definite numbers.

While it is perfectly acceptable to place all of this information in the URL string - and some APIs might actually do that - it is more common to structure everything into a JSON dictionary, which is how many APIs are set up to process POSTs. The Google Forms example we’re going to present later simply places all of this information in the URL String.

Formatting the Request

If your API does not explicitly explain how to format your request, you will probably have to experiment. This is one of the most frustrating parts about connecting to APIs using POSTs. Fortunately, most APIs have a non-committal or testing mode so you can test your program without making permanent changes to your account.

You’ll probably need to set it up in a JSON format that looks similar to {key1:item1, key2:item2, key3:item3}.

You can guess at many of the requirements, but sometimes the API’s implementation will demand a counterintuitive format. The most common I’ve experienced is the need for numbers to be represented as strings. Sometimes it is hard to catch the issue, too. If you’re constantly getting “bad format” errors, take a deep breath and check the datatypes expected by the API.

To illustrate, even if you set this as the body:

order_string = "{ticker:msft, quantity:5, type:market}"
request.Send order_string

the service might reject your request! To properly process the request, you might need to use double quotes to present your string as a string of strings to the server:

order_string = " { ""ticker"":""msft"", ""quantity"":""5"", ""type"":""market"" } "
request.Send order_string

Here you can see the first and last quote marks encapsulate the entire string representing the dictionary, which itself is marked by the curly braces. Then, each key and each item is also explicitly forced into a string, creating a dictionary of strings within a single order string. The Key1 string is ticker while the Item1 string is msft.

Now we have a string for the body that contains the dictionary made up of strings. Phew.

Not all APIs will require such explicitness. Sometimes they might allow the first example and attempt to parse your dictionary-string interally. That would require API devs being user-friendly-oriented. Even so, there are certainly ways server-side parsing can go wrong, so be prepared to enforce datatypes explicitly for the benefit of the server.

POST Responses

Even though you are making server-side changes with your HTTP POST command, you’ll still receive some response from the API service. This response will tell you what the server did, and it may return information related to your specific request.

There are response status codes that are easy to check. These are useful for error-catching and automating some of your processes. For example, if request.status is 200, your request was probably okay. You can easily set your program to check the status code, and if it’s 200, you can move to the next task. As always, different APIs have different status codes, but developers try to standardize the most common.

If there was a problem or the API returns important information about your API call, this information will be in the request.ResponseText property. The server might tell you why the call was rejected (like improper formatting), or it could tell you the details of an account change. If you are buying 50 shares of AAPL, the response for the POST will probably tell you the order ID, the order time, and any other relevant information.

If you’re implementing an API call for something like a stock purchasing tool, you’ll have to send a separate GET call in order to check the status of the order. Orders on the open market exchanges cannot be filled instantaneously, so the API probably splits the order ID and the order status information. On the other hand, if your request action is entirely on-server, like you changed your username on a forum, the response might confirm the change immediately in the response text.

Other HTTP Actions

GET and POST are the most common so-called HTTP actions, but they’re not the only ones. PUT and DELETE are quite common as well. The former is rather similar to POST and the nuances are outside the scope of this tutorial, while DELETE is usually used to delete a change previously actioned. Not all APIs support all actions, and some might even support their own proprietary actions. As a client-side developer, use whatever is recommended in the documentation. If you want to be a server-side API developer, you’ll probably need many more, and more extensive, tutorials.

Like most things in computing, these terms are not subject to natural law, only standardization. If a hobbyist API service developer wanted to be playful (and confusing), they might switch POST and GET and they might recode statuses such that 200 means error while 500 (a common error code) means OK.

Testing and Debugging

Sometimes the API response is cryptic or seemingly inapplicable to your situation. This problem could arise because you’re using a version 1.0 of the API that needs more maintenance, or maybe the request is so mangled the server cannot even guess what’s wrong.

Unfortunately, most services won’t respond with what data they received on their end. They’ll only respond with an error code and maybe an error description. To see what is received on the other side, there are several bin services where you can send the request and check the bin (the website), and you can see exactly what was received on the API/bin website’s end. I don’t like to endorse any particular service, so search api bin and you’ll find plenty in the search results.

You’ll probably also get accustomed to using the Immediate Window and

debug.print request.status
debug.print request.ResponseText

which will write the status and response to the Immediate window on each run.

A Working Example with Interaction (Google Forms)

Throughout the tutorial I’ve been quite general, and I haven’t provided a full example for you. Now I want to cement your understanding by doing actions rather than just reading them, so Ryan created this example for us.

While this example doesn’t interact with what is commonly considered an API, it does let you practice with HTTP POST. After all, HTTP actions (POST, GET, etc.) are used by browsers and even apps to transmit data, and this will help you practice with data transmission over a network, the basis of APIs.

Ryan set up a Google Form, to which you may submit through a graphical interface here. This is the most common way to interact with a Google Form, but there are other ways. For example, you can submit your responses programmatically using the HTTP POST method. The results you post get saved to a spreadsheet, which I’ll link to after this macro, which transmits data through a Google Form to a spreadsheet using VBA and HTTP POST. There is no need for authentication, because this is an anonymous, public-facing form.

Sub VBA_HTTP_POST_toGoogleForms()
'DESCRIPTION: VBA HTTP POST demo where the variable stored
'             in MyString is automatically transmitted to a Google Form
'             Requires Reference to "Microsoft XML, v6.0" (Tools > References)
'DEVELOPER: Ryan Wells (wellsr.com)
'OUTPUT: The output of your POST command can be viewed by visiting:
'        https://docs.google.com/spreadsheets/d/e/2PACX-1vR5Qt80LQ7-F4xVtHplyf_PahUusgpPzIpZjte9fQM7XieKV-rIGE54KdMC2ZX86bZlIwLzoQeEvWsG/pubhtml?gid=1079888132&single=true
'        Please be patient and allow a minute or two for the webpage to be updated with your post.

Dim apiURL As String, requestString As String, endpoint As String
Dim id_header_name As String, id_key As String
Dim request As MSXML2.ServerXMLHTTP60
Dim myString As String, varUserInput As Variant
id_header_name = "Content-Type"
id_key = "application/x-www-form-urlencoded; charset=utf-8"
varUserInput = Application.InputBox("This a VBA HTTP POST demo that posts results to an online Google Form. What would you like to tell the world?", "POST a string to Google Forms with VBA", "Hello World")
' continue if the user didn't cancel or typed in nothing
If Not varUserInput = False Then
    myString = CStr(varUserInput)
    apiURL = "https://docs.google.com/forms/d/e/1FAIpQLScnoQqXe8im5KPtDh_pIm8oo33cfROTvfkJ_jCw34hA2DRRbA/formResponse?ifq" 
    endpoint = "&entry.1948193223=" & CStr(myString) & "&submit=Submit" 
   
    requestString = apiURL & endpoint
   
    Set request = New ServerXMLHTTP60
    request.Open "POST", requestString, False
   
    request.setRequestHeader id_header_name, id_key
   
    request.send
   
    'no need to do anything with the request.ResponseText
    'You can confirm the POST was successful with: 
    debug.Print request.statusText
End If
End Sub

This asks the user to enter whatever text they want to post (using the HTTP POST command) to the form using a VBA Application.InputBox. It takes this string of data and sends it off to the Google Sheet spreadsheet (and prints the status text to the Immediate window).

If you’re like me, the wheels are already turning with possible application ideas for a tool like this. Imagine you made an add-in or workbook for multiple customers. You could have a “Submit Feedback” button that records their responses on a spreadsheet you have access to.

Once you finish your request, you can see your POSTed text here (give it a minute or two to update). That’s pretty cool, isn’t it?

Steps to connect VBA to your own Google Form

While we’re on the topic, here are some specific steps for connecting your own Google Form to a VBA macro using HTTP POST. Anyone who makes a Google Form can find their “postable” link by opening their form, clicking the more button in the upper right and selecting “Get pre-filled link.” In this example, my Google Form only had one field, and it was a long paragraph field.

  1. Find the “pre-filled link” associated with your form.
  2. Update the apiURL in the macro above with this link. Stop right before you get to something like &entry.
  3. Update the endpoint with the rest of the string, starting from the portion of the URL resembling &entry..
  4. To get it to properly post, you’ll need to change viewform?usp=pp_url to formResponse?ifq and you’ll need to add &submit=Submit to the end of your endpoint.

Those are pretty much the only changes you need to make to the macro above. Simple, right?

Conclusion

There are many parts to APIs, and you won’t master them with a single tutorial, but I hope we discussed enough to get you comfortable with the HTTP POST command itself. Each API is different, and using VBA makes it even more difficult because most publicly-available libraries and resources are not written for VBA. When debugging, you might be forced to look at what people did in C or Python and translate it to VBA.

Once you trudge through it the first time, though, your programs will be much more powerful. Excel and other MS Office programs offer great built-in visualization tools, and most people know how to use MS Office products to an extent. Few people outside IT know how to navigate around a command prompt or Linux terminal to use your API without a GUI.

So if you want beautiful graphics without interfacing between languages and applications, or you want to distribute your project, it may be worthwhile to spend a a bit of time figuring out how to implement your program via VBA.

Remember, if you’re serious about writing macros, subscribe for more VBA tips. Simply fill out the form below and we’ll share our best time-saving VBA tips.