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!
I was preparing a series of videos to upload to my upcoming RUclips channel and one of them was APIs. Interesting. I have a project of mine which uses a WebAPI from an Online dictionary.
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.
Hey Paul, great video as always! Any chance to have a tutorial explaining how to get apis that utilizes oauth such as Google sheets or Twitter? Thanks!
@@shinrafahell I see. Thank you. Python and JavaScript are in my list but sometimes we still want to achieved what we really want to achieved. 😁 I know that learning to manipulate google sheet using VBA might be overkill but that what makes us happy. Anyways thank you for the info i think i will try to learn if first.
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!!
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.
FLASH SALE: 25% off my Effective Excel VBA course before Saturday 14th September: 👉courses.excelmacromastery.com/courses/Effective_Excel_VBA Learn how to write real-world Excel VBA code: 👉courses.excelmacromastery.com/ I hope you enjoy my new video. Please let me know if you plan to use Web APIs. *Important Note:* To use each API on the RapidAPI website you must subscribe on the API page.
question - what about customers? I do a lot of paid freelance work with VBA (and Google script) but I've always avoided any addons as customers might not have Scripting Runtime or WinHTTP loaded). Is that a concern? Any suggestions on how to ensure a client has those addins added... maybe an auto-load button?
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!
Very interesting video, Paul. I'm currently using Integromat to integrate web apps. Whilst Integromat is very good, I feel I could do with having more control and your video has got me thinking! A follow-up video on writing to an API would be useful.
This is so awesome. I’m gonna. Be working on a lot of personal projects from this rapidapi website. I’m a data analyst so I’ll be inserting the responses in a database :)
this is great, one question could be how would you do it with a POST request, that also you need a request body website, that is very common within intranet company's websites.
15:35 It could be a mixture of dictionaries/collections and also classes . 'Country' could well be a class. But that is a matter of preference - for simplicity reason that is ok. When using s dictionary I would verify if the key-value pair exist. A great tutorial, thak you
A very interesting lesson. My question is, I want to create an invoice automatically. I want to create a QR code on the same invoice, and when scanning the code, the same invoice appears.
Excellent video and explanation, I ask you seeing the use of the api, could we make it possible for us to interact with VBA to upload or download files from google drive?
Very beneficial video I'm trying to build a connection with e invoicing portal in my country using json files and api and they're talking about sth called token Could you plz add more detailed tutorial on this topic
Thanks for this video! I also have a doubt. How do i handle the API call when the API response have pagination? should i use a for an remake a colletion?
It would have been nice to get more specifics on how to get an API Key. The Rapid-site is very confusing for a beginner. I could not run your code because I could not figure out how to get an API Key.
Hi Paul, great tutorial, with almost zero knowledge i managed to get data of EURO currency from Polish National Bank...but what I'm missing in your tutorial is assigning values to specific cells...for example when I filter a dataset I'd like to paste currency rate to filtered column.....no idea how to do it... I mention that I've this rate but can't paste it...could you help int hat matter? your movies are top quality, great job
Wonderful video. Coul VBA connect with a dspace repository? The latest version of dspace repository is made in Angular (front end) and use a backend built on spring boot (java web app)
is it possible to get some suggestions on how to use the travel distance calculator? I have tried it against your templates and cannot get it to work. TrueWay Matrix: Calculate Driving Distance Matrix
Hi Paul, thanks for the excellent video. How do you catch errors if you have a miss match? If you have a collection of stocks cods and you getting the close price, if one code if not available you’ll have a error and the loop will brake.. how can you skip the error and finish the loop? Thank you.
I am working on a vba to fill web form, it works on first IE page and submit the form, but when a new page loads with different web address, my code don't recognize the active web page...to keep on filling this page Any advice??? No selenium used
This is a really good video. I've had a go at this before but ran into the problem of authentication. I was trying to access data in Xero (the cloud accounting solution). How do you authenticate through VBA if you only have a email address, password and 2FA code? Authentication for other web services creates a similar issue. Suggestions welcome.
hi can you do to copy data from private apps then paste it in excel, also include the screenshot of data and paste in the excel too, but for this it included multiples data to do in one sheet before move to next sheet
Hi Paul, I remember watching this when you first posted it and thought I'd put it on a backburner until I needed it. I just tried downloading the source code but unfortunately the link doesn't work anymore. Is there an up to date link?
hey nicely explained... I am trying to build something where user can define a Get URL and my code can parse any JSON without knowing the Structure and show it in tabular form in excel. How can i do that?
Hi Paul in the example you have used with the name and country, can a filter be applied on the country id so that when a name is selected on data for the filtered country is retrieved? if possible how would I add this to the code?
Thank you very much. Awesome tutorial. I have signed up in rapidapi but how can I get the API that I would insert in the VBA code (I couldn't find such API section)
I'm working on a mac. When you do the references part, is there a mac equivalent to WinHTTP Services. I don't have as many options as you do. Thanks, John
Hi Paul! Thanks for the video. It is very informative. I was able to download and parse json data for small dataset. However, If I try to download complete data (~600,000), excel goes to not responding. I tried with .SetTimeouts 0, 0, 0, 0 and/or waitforresponse but the problem still persist. Any tips? Thanks in advance for your help.
Hi Paul, Love this but I'm hoping you can help me with something. I can't get the msgbox to display the "request.responsetext. I just get a blank box. If I put the address in a google address box, values return but my message box remains blank. Can you give me some ideas?
Great video. I'm a little late to the party. :) I followed along several times and tried typing out the code as you went along, but my code failed. I keep getting a Compile error: User-defined type not defined on the 'Dim country As Dictionary' portion of the code. Not sure what I missed. Hopefully you or someone here will have an idea. Thanks!
Hi Paul, thank you for the great video. I had an issue with downloading the source code. I am not sure if the link is broken or if I need to create a login or something. Is there another means by which I can get the source code?
whats the difference between Extracting data via API and extracting data via web scrapping ! or they both same ! can we use same code for web scrapping !
That's a good question. While they both read from a website they are completely different. *Web Scraping*: When you access any website in your browser you are essentially receiving a HTML file from the website. HTML is text and what web scraping does is read through this HTML and extracts data from it. *API*: An API is a programming interface created by the website that allows you to access certain functionality. This means you have direct access to the web application with support and documentation.
It just reassures me Excel is capable of doing anything you need it to do. Sadly DataBases are not one of them. Could let me know if this would apply to MS Access too? I almost certain it is…
Yes. You can use this code with VBA for MS Accesss. You do need to change the parts that write and read from spreadsheets. The Web API code will work the same.
Hey Paul, I am from India. I have designed a project for which I want to get the Train No. and name of the train from the web in my excel worksheet. Please help the execute the same.
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!
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!!
Glad you found it useful Timmy.
next level VBA 👍
good stuff. hope beginners realize that is for a specific (web) api . Not generic API's.
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!
Incredible video, super clear, and great teacher. Thanks!
Awesome job.. Thanks, and greetings from Brazil !
I was preparing a series of videos to upload to my upcoming RUclips channel and one of them was APIs. Interesting. I have a project of mine which uses a WebAPI from an Online dictionary.
Hi Paul ,
Great video. Can you make it a dynamic code to read data of any api and write it to excel ? Please explain it in next video . Thanks
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.
Hey Paul, great video as always! Any chance to have a tutorial explaining how to get apis that utilizes oauth such as Google sheets or Twitter? Thanks!
Exactly what i need.
This is the last wish that i want to achieved. To learn to modify google sheets using vba.
@@MyAudioBookCompilation using python it is a breeze. Unfortunately I didn't find anything related to vba.
@@shinrafahell I see. Thank you. Python and JavaScript are in my list but sometimes we still want to achieved what we really want to achieved. 😁 I know that learning to manipulate google sheet using VBA might be overkill but that what makes us happy. Anyways thank you for the info i think i will try to learn if first.
thank Paul , you explained it easy to understand.
Solid video. Shows exactly what you should do for a given objective.
This is awesome👍
Happy to learn APIs and how to use with VBA.
This example blew me away very informative and helpful, thank you Paul
Thanks Ken.
thanks for this tutorial Paul!! this one is very cool!!!
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!!
Thanks a lot Wayne. Glad you enjoyed it.
Excellent explanations (as usual). Good teacher! Thanks Paul *
Great job as always, thank you Paul
You're welcome Anthony
THANK YOUUUUU, you made my day...
Great video, thanks for sharing and explaining
Outstanding as always! Bravo
Thanks Leon.
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.
Awesome video, thank you. Will make time to work through it all.
FLASH SALE: 25% off my Effective Excel VBA course before Saturday 14th September: 👉courses.excelmacromastery.com/courses/Effective_Excel_VBA
Learn how to write real-world Excel VBA code: 👉courses.excelmacromastery.com/
I hope you enjoy my new video. Please let me know if you plan to use Web APIs.
*Important Note:* To use each API on the RapidAPI website you must subscribe on the API page.
@@yashchawla8360 Thanks yash. I hope you like it.
I will use in Microstrategy
I plan to use API on price comparision website
question - what about customers? I do a lot of paid freelance work with VBA (and Google script) but I've always avoided any addons as customers might not have Scripting Runtime or WinHTTP loaded). Is that a concern? Any suggestions on how to ensure a client has those addins added... maybe an auto-load button?
Hi @@JasonTorpy , you can do it with late binding so that your clients don't need to turn on the reference library needed.
Really looking forward to this
Hi Paul, very good stuff. Thank you.
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!
Excellent!!!
Thanks, that was a very good tutorial
Hi Paul! Thank you so much!
You're welcome
Hi Paul, love your tutorial
Great video, thank you so much for posting this, really helps
You're welcome John.
Very interesting video, Paul. I'm currently using Integromat to integrate web apps. Whilst Integromat is very good, I feel I could do with having more control and your video has got me thinking! A follow-up video on writing to an API would be useful.
Also some examples writing to API using GraphQL (mutation) as well as REST API would be fantastic.
Hi Paul, I had no idea you could use VBA for this purpose! Thanks for sharing the knowledge 😀😀
You're welcome Brian.
Amazing video ! I liked it , thanks a lot !
Thanks Mallesh.
This is so awesome. I’m gonna. Be working on a lot of personal projects from this rapidapi website. I’m a data analyst so I’ll be inserting the responses in a database :)
That's great to hear Luis.
Thank you very much 👍
Wow , que impresionante! 👏👏
wouuuuuuuuuuuuuuuuuuu amazing ... you got a suscriber to your channel !!!
Welcome aboard
Great video!!! Congrats!
Thanks
thanks my man your a genious
Thank your video.
this is great, one question could be how would you do it with a POST request, that also you need a request body website, that is very common within intranet company's websites.
You are awesome!! (Guruji)
thanks for sharing!
15:35 It could be a mixture of dictionaries/collections and also classes . 'Country' could well be a class. But that is a matter of preference - for simplicity reason that is ok. When using s dictionary I would verify if the key-value pair exist. A great tutorial, thak you
A very interesting lesson. My question is, I want to create an invoice automatically. I want to create a QR code on the same invoice, and when scanning the code, the same invoice appears.
Great video. I have to use a post command and need to pass a JSON request together with the http request and headers, How can I do that with VBA?
That is awesome, but can you tell how did you make those two API examples currencies & recipes from the excel
Excellent video and great Spanish name picking UwU
Excellent video and explanation, I ask you seeing the use of the api, could we make it possible for us to interact with VBA to upload or download files from google drive?
Thank you
Very beneficial video
I'm trying to build a connection with e invoicing portal in my country using json files and api and they're talking about sth called token
Could you plz add more detailed tutorial on this topic
By chance 've read this: "Programs must be written for people to read, and only incidentally for machines to execute". :)
Great video. Your channel is awesome. How can I use a private API (requires authentication) in VBA?
Thanks for this video! I also have a doubt. How do i handle the API call when the API response have pagination? should i use a for an remake a colletion?
It would have been nice to get more specifics on how to get an API Key. The Rapid-site is very confusing for a beginner. I could not run your code because I could not figure out how to get an API Key.
Hi Paul, great tutorial, with almost zero knowledge i managed to get data of EURO currency from Polish National Bank...but what I'm missing in your tutorial is assigning values to specific cells...for example when I filter a dataset I'd like to paste currency rate to filtered column.....no idea how to do it... I mention that I've this rate but can't paste it...could you help int hat matter? your movies are top quality, great job
Wonderful video. Coul VBA connect with a dspace repository? The latest version of dspace repository is made in Angular (front end) and use a backend built on spring boot (java web app)
very informative
Glad you like it George.
is it possible to get some suggestions on how to use the travel distance calculator? I have tried it against your templates and cannot get it to work. TrueWay Matrix: Calculate Driving Distance Matrix
Would it be possible to see how to implement OAuth 2.0 authentication using the examples in this video?
Amazing!
Glad you like it.
Just out of curiosity, how would you store multiple API keys in your module such that the correct key was called by each subroutine?
Trims
Hi Paul, thanks for the excellent video. How do you catch errors if you have a miss match? If you have a collection of stocks cods and you getting the close price, if one code if not available you’ll have a error and the loop will brake.. how can you skip the error and finish the loop?
Thank you.
Very useful video thanks.. can we define the jsonconverter in classes..
You're welcome.
Can you please also tell me how to send parameters along with API Key in Post request?
I am working on a vba to fill web form, it works on first IE page and submit the form, but when a new page loads with different web address, my code don't recognize the active web page...to keep on filling this page Any advice??? No selenium used
This is a really good video. I've had a go at this before but ran into the problem of authentication. I was trying to access data in Xero (the cloud accounting solution). How do you authenticate through VBA if you only have a email address, password and 2FA code? Authentication for other web services creates a similar issue. Suggestions welcome.
Hi Paul. You can guide How to scrape data from a website that requires login? thank U
hi can you do to copy data from private apps then paste it in excel, also include the screenshot of data and paste in the excel too, but for this it included multiples data to do in one sheet before move to next sheet
Can you show an example of a post request instead of a get request?
Could you please let me know how to get API response text in VBA ? For example for 200 response , response text is ok .
Hi Paul, I remember watching this when you first posted it and thought I'd put it on a backburner until I needed it. I just tried downloading the source code but unfortunately the link doesn't work anymore. Is there an up to date link?
Can you make a video for windows api to control an open third party application and get the text?
hey nicely explained... I am trying to build something where user can define a Get URL and my code can parse any JSON without knowing the Structure and show it in tabular form in excel. How can i do that?
Hi Paul in the example you have used with the name and country, can a filter be applied on the country id so that when a name is selected on data for the filtered country is retrieved? if possible how would I add this to the code?
Hello,
is it possible to use Excel as a backend and set up an API with VBA? 🤔
How can we parse api response in XML? Is there any library available for the same like VBA-JSON library?
How do you do nested dictionaries?
LinkedIn to scrap using macro is it possible
Thank you very much. Awesome tutorial. I have signed up in rapidapi but how can I get the API that I would insert in the VBA code (I couldn't find such API section)
You have to subscribe to each api you wish to use.
@@Excelmacromastery I have tested again now and it is working. Yesterday, something went wrong.
I'm working on a mac. When you do the references part, is there a mac equivalent to WinHTTP Services. I don't have as many options as you do.
Thanks,
John
I have this question too
Hi Great video thank you for sharing. Cant see the code link though, cant wait to practice this
The link is there now👌
Hi Paul! Thanks for the video. It is very informative. I was able to download and parse json data for small dataset. However, If I try to download complete data (~600,000), excel goes to not responding. I tried with .SetTimeouts 0, 0, 0, 0 and/or waitforresponse but the problem still persist. Any tips? Thanks in advance for your help.
Hi Paul, Love this but I'm hoping you can help me with something. I can't get the msgbox to display the "request.responsetext. I just get a blank box. If I put the address in a google address box, values return but my message box remains blank. Can you give me some ideas?
But how do you add it to th excel sheet?
Great video. I'm a little late to the party. :) I followed along several times and tried typing out the code as you went along, but my code failed. I keep getting a Compile error: User-defined type not defined on the 'Dim country As Dictionary' portion of the code. Not sure what I missed. Hopefully you or someone here will have an idea. Thanks!
You need check "Microsoft Scriping Runtime" under Tools->References
Hi Paul, thank you for the great video. I had an issue with downloading the source code. I am not sure if the link is broken or if I need to create a login or something. Is there another means by which I can get the source code?
Can you send me an email? It's Paul at the channel name.
we could not find your APIKEY module and without it the excel does not work. could you share with us? module or xlsm excel file? thanks
I cant get just a list to print. it doesn't have a key value pair inside but just a list
Is it possible run R script on VBA?
talent
can not find the download link of this video
Sorry, I got it.
With your code about name and countries, I have an error on the line Request.Send and I verify references WinHTTP and Scripting are check…. :/
whats the difference between Extracting data via API and extracting data via web scrapping ! or they both same ! can we use same code for web scrapping !
That's a good question. While they both read from a website they are completely different.
*Web Scraping*: When you access any website in your browser you are essentially receiving a HTML file from the website. HTML is text and what web scraping does is read through this HTML and extracts data from it.
*API*: An API is a programming interface created by the website that allows you to access certain functionality. This means you have direct access to the web application with support and documentation.
It just reassures me Excel is capable of doing anything you need it to do. Sadly DataBases are not one of them.
Could let me know if this would apply to MS Access too? I almost certain it is…
Yes. You can use this code with VBA for MS Accesss. You do need to change the parts that write and read from spreadsheets. The Web API code will work the same.
Hey Paul, I am from India. I have designed a project for which I want to get the Train No. and name of the train from the web in my excel worksheet. Please help the execute the same.