API calls from Excel (2024)

API calls from Excel (2)

Nobody can deny that Microsoft Excel is still a powerful tool even if you know Python, SQL, Javascript… It provides a super-friendly interface, has many functionalities and is a very popular software.

On the other hand, being able to make API calls and process the response provides a new world of endless possibilities. Nowadays many companies give access to their data via certain endpoints.

Why not put these 2 tools together? In this article we’ll explain how to do it.

There are 2 main ways in Excel to do it:

  • Via Visual Basic script
  • Via making a “query” from the data menu

API Call from Visual Basic script

The first thing is enable the “developer” menu. This can be done in File → Options → Customize Ribbon:

API calls from Excel (3)

Once this is done we have to open the VBA editor.

API calls from Excel (4)

In order to process the JSON response of the API call, we need to add the JsonConverter module, which can be found in the following url: https://github.com/VBA-tools/VBA-JSON/releases, then import JsonConverter.bas into the project. In the VBA Editor, go to File → Import.

API calls from Excel (5)

Then we also need to import 2 references into the project from the “Tools” menu.

  • Microsoft XML, v6.0
  • Microsoft Scripting Runtime
API calls from Excel (6)

Next we have to create a new module to write the code that will make the api call. Here I present 2 examples:

If you want to save the excel file, remember to use the “xlsm” extension, which allows macros.

API calls from Excel (7)

API Call from “New Query”, in “Data” tab

Excel 2016 has a built-in feature that allows to make API calls. Previous versions can also make it, but installing the PowerQuery plugin. To make an API call we must go to the “Data” tab and click on “New Query” → “From Other Sources” → “From Web”.

API calls from Excel (8)

Then we click on “Advanced”. Here we put the url, and if credentials are needed, they can be entered as a header.

API calls from Excel (9)
API calls from Excel (10)

Hope it was helpful!

API calls from Excel (2024)
Top Articles
Latest Posts
Article information

Author: Catherine Tremblay

Last Updated:

Views: 6076

Rating: 4.7 / 5 (47 voted)

Reviews: 86% of readers found this page helpful

Author information

Name: Catherine Tremblay

Birthday: 1999-09-23

Address: Suite 461 73643 Sherril Loaf, Dickinsonland, AZ 47941-2379

Phone: +2678139151039

Job: International Administration Supervisor

Hobby: Dowsing, Snowboarding, Rowing, Beekeeping, Calligraphy, Shooting, Air sports

Introduction: My name is Catherine Tremblay, I am a precious, perfect, tasty, enthusiastic, inexpensive, vast, kind person who loves writing and wants to share my knowledge and understanding with you.