Integrating API in Excel using macros

Introduction

Integrating APIs into Excel using macros allows you to fetch live data or perform advanced computations directly within your spreadsheets. By combining the power of APIs with VBA (Visual Basic for Applications), you can automate data retrieval, streamline workflows, and enhance the functionality of Excel for real-time analysis and decision-making.

Let’s start

In the previous blog, we created an API link using Flask.

You can find the blog here: http://sanaitics.com/research-paper.aspx?id=227.

Now, we will integrate that link into Excel.

Create a Button in Excel

• Open Excel and go to Developer > Insert > Button to create a button.

Note-If the Developer tab is not visible, enable it by navigating to File > Options > Customize Ribbon and checking the “Developer” box.

• After creating the button, the “Assign Macro” window will pop up. Click New to create a macro for the button (e.g., Button1), then click OK

• Go to Tools in the VBA editor and enable Microsoft Scripting Runtime

Why JSONConverter is needed?

• APIs commonly return data in JSON format, which Excel cannot natively interpret. To handle this, import the JsonConverter.bas file in the VBA editor (File > Import File), allowing you to parse JSON into Excel-compatible structures like dictionaries or arrays.

Download the module here: https://github.com/VBA-tools/VBA-JSONO

• In the VBA editor, write your code to fetch data from the deployed server and insert it into Excel:

• Now, when you click on the button in Excel, it will fetch the data from the deployed API and populate the Excel sheet

• Save the workbook as a Macro-Enabled Excel Workbook (.xlsm) to ensure the macro runs properly

Note - The button created can be used in any other Excel sheet as well

Additional:

  • Password-Protect Your VBA Code

  • In the VBA editor, go to Tools > VBAProject Properties.

  • Under the Protection tab, check Lock project for viewing.

  • Set and confirm a password.

  • Save, close, and reopen the workbook to ensure the VBA project is protected.

Now, anyone trying to access the VBA code will need the password.

Conclusion

Integrating APIs with Excel using VBA bridges the gap between dynamic data sources and spreadsheets, enabling real-time updates and automation. This powerful integration enhances functionality, streamlines data analysis, and supports better decision-making. It’s a game-changer for professionals looking to innovate and boost productivity.