Use Excel VBA to Read API Data

Today I'm going to show you how to use Web API's with Excel VBA.

Web APIs may seem a bit daunting at first but by the time you finish this video, you’ll be able to read data from any Web API with ease.

The first part of the video explains the 3 core parts of dealing with Web APIs. It explains each in detail and provides coding examples.

Don't miss the final part of the video where I show a simple method for writing the code for any API.

All the code used in this video is available to download from the link in the description below.

#VBAAPI #ExcelVBAAPI #VBAWebAPI #VBARequestAPI

Free Excel VBA Resources

Useful VBA Shortcut Keys
========================

Debugging:
Compile the code: Alt + D + C OR Alt + D + Enter
Run the code from the current sub: F5
Step into the code line by line: F8
Add a breakpoint to pause the code: F9(or click left margin)

Windows:
View the Immediate Window: Ctrl + G
View the Watch Window: Alt + V + H
View the Properties Window: F4
Switch between Excel and the VBA Editor: Alt + F11
View the Project Explorer Window: Ctrl + R

Writing Code:
Search keyword under cursor: Ctrl + F3
Search the word last searched for: F3
Auto complete word: Ctrl + Space
Get the definition of the item under the cursor: Shift + F2
Go to the last cursor position: Ctrl + Shift + F2
Get the current region on a worksheet: Ctrl + Shift + 8(or Ctrl + *)
To move lines of code to the right(Indent): Tab
To move lines of code to the left(Outdent): Shift + Tab
Delete a Line: Ctrl + Y(note: this clears the clipboard)

Table of Contents:

00:00 - Introduction
00:37 - What is an API?
04:37 - API Code example
07:41 - JSON Converter Library
09:27 - Reading the converted data
12:20 - Understanding the returned data
15:46 - Fast method to write any API code

[guidopiotrowski]

Incredible video, super clear, and great teacher. Thanks!

[TuncanGaming]

I didn't know anything about VBA and was able to do what I wanted to just by watching your video. Great work, thank you so much!

[mikem]

Solid video. Shows exactly what you should do for a given objective.

[magalisantos]

Thanks very much for this tutorial!! I created a macro which retrieves data from an API and thus it saved me lots of hours (~6h) from checking them individually!! Thank you VERY MUCH AGAIN!

[bongzsmalawu]

Thank you so much, this has made things lighter for me, I have no programming background, but was looking forward to leverage this lesson to help me import run statistics from strava, garmin connect and polar websites for my team.

[wayneedmondson]

Hi Paul. Very cool tutorial! Thanks for access to all the resources used. I worked through the name example and connected successfully with the same results. Can't say I totally understand it all yet, but it's a great start. Always something new and interesting at Excel Macro Mastery :)) Thumbs up!!

[garethwoodall]

Excellent explanations (as usual). Good teacher! Thanks Paul *

[trueevilangel]

thank Paul, you explained it easy to understand.

[edrobinson]

good stuff. hope beginners realize that is for a specific (web) api . Not generic API's.

[vbaclasses]

Awesome video, thank you. Will make time to work through it all.

[marcellino]

Awesome job.. Thanks, and greetings from Brazil !

[tgirard]

Easy as 1-2-tree. Thank you so much. I could not figure this out for the life of me. Now I got a working sheet!!

[chrisouellet]

thanks for this tutorial Paul!! this one is very cool!!!

[DLAmurph]

This example blew me away very informative and helpful, thank you Paul

[hf]

Thanks for a great video Paul, keep it up

[davidlinesq]

Thank you so much for this video. This has helped me with a program that I needed to create for work. One request for a follow up video would be how to make paginated api calls and combine the results into one array. Thanks as always!

[CivilEngSpreadsheets]

Hi Paul! I'm learning a lot from your channel. Would really appreciate if you could do a tutorial about VBA and Google Drive API like how can you use VBA to upload or download a file from Google Drive.

[oz]

I was preparing a series of videos to upload to my upcoming Youtube channel and one of them was APIs. Interesting. I have a project of mine which uses a WebAPI from an Online dictionary.

[ashkumar]

Great video, thanks for sharing and explaining

[nickroe]

Thanks, that was a very good tutorial