Making API Requests in VBA | JSON
HTML-код
- Опубликовано: 30 июл 2024
- In our previous video, we saw how to request data from an API using VBA, this video was intended for APIs that offered XML versions of their API. However, not all APIs will provide their data in this format, so we need to explore other options as well. This video focuses on APIs as well but will cover JSON formatted data and how to parse it.
Video Resources:
--------------------------------------------------
Resource: GitHub File
Link: github.com/areed1192/sigma_co...
Resource: Resource JSON Parser
Link: github.com/VBA-tools/VBA-JSON
Resources:
--------------------------------------------------
Facebook Page: / codingsigma
Facebook Group: / sigmacoding
GitHub Sigma Coding: github.com/areed1192/sigma_co...
Support Sigma Coding:
--------------------------------------------------
Patreon: / sigmacoding
Amazon Associates: amzn.to/3bsTI5P **
Related Topics:
--------------------------------------------------
Title: How To Programmatically Add Library References Using VBA
Link: • How To Programmaticall...
Title: How to Work With the Command Bar Object in VBA
Link: • How to Work With the C...
Title: How to Work With the Command Bar Control Object in VBA
Link: • How to Work With the C...
Title: How to Export Macros Using VBA
Link: • How to Export Macros U...
Title: How To Run A Python Script Using Excel VBA
Link: • How To Run A Python Sc...
Title: How to Use the Shell Automation Library in VBA | Part 1
Link: • How to Use the Shell A...
Title: How To Control The Visual Basic Editor With VBA
Link: • How To Control The Vis...
Title: Web Scraping With VBA | Part One
Link: • Web Scraping With VBA ...
Title: Web Scraping With VBA | Part Three
Link: • Web Scraping With VBA ...
Title: Web Scraping With VBA | Part Two
Link: • Web Scraping With VBA ...
Title: Web Scraping With VBA | Scraping Data Tables
Link: • Web Scraping With VBA ...
Title: Making API Requests in VBA | JSON
Link: • Making API Requests in...
Title: Making API Requests in VBA | XML
Link: • Making API Requests in...
Title: How To Pull Data From Access Using VBA
Link: • How To Pull Data From ...
Title: Importing Text Files In VBA
Link: • Importing Text Files I...
Title: How to Use the File System Object in VBA
Link: • How to Use the File Sy...
Title: How to Build Windows Forms Applications in Python | Pt. 2
Link: • How to Schedule a Macr...
**Amazon Associates Disclosure:
--------------------------------------------------
I am a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for sites to earn advertising fees by advertising and linking to Amazon.com. One of the ways I support the channel is by using Amazon Associates to earn fees on purchases you make. That means when you use the link above, it will track any purchases made from that link and give a small portion of it to the Sigma Coding. I love this approach because it allows you to do what you're already doing (shopping) but also helps support the channels you care about. Also, it makes it where I can invest that revenue to help improve and grow the channel.
Tags:
--------------------------------------------------
#Excel #VBA #APIs
Thank you Sigma! This JSON parser gave me hard time, I was not sure how to dig through the structure with VBA code. This explained everything! Saved me hours of work, thanks!
Hello!
Thank you so much for your perfect video. This is exactly what I was looking for.
I am trying to index my 70,000+ digital photos using Azure Computer Vision APIs so that I can search for photos that has sunsets for example, or photos that have birthday cakes.
Next step is to train a model to recognize faces of my family so that I can search for photos that contain my wife and my daughter.
I will use Access as my database and I was stuck on how to parse the JSON file that I will get back from the API. Your video is super helpful. Thank you so much :)
Hello sir. I would like to ask if you were able to connect excel to access using this method?
Yes.. Sydney Australia.. and YES.. they are good... been to 2 of them .. :) - great vid by the way..
This is the great video. Thank you.
Great instruction for API newbie. Thanks.
Glad you enjoyed it!
Thanks for this, it was really useful
Glad it was helpful!
I think it's easier to parse a json file with the split function. Nice tutorial!.
Amazing tutorial 🙌
Great Job. Very nicely explained. Thanks/
Glad you liked it
I need to know if I can make a Api request to a website that requires oauth? I'm still trying to learn this. The code is in json format. Is there no way we can use power query
Edit: Figured out you need to ReDim with the number of values in the key, ex. ReDim myarray(Json("values").Count, 0)
Any advice for storing the values in a temporary array to manipulate without have to put them on a worksheet? I'm wanting to add the values to comboboxes, etc. for use within an add-in for users to be able to pick values that then uses the picked value to grab more JSON data from the API. But I cannot for the life of me get it to store the values into an array without having it use a worksheet as a medium.
Hey Sigma thanks for this video - very useful tutorial. You use the Google maps API here, but can you tell me if it's possible to make API requests to return regular Google Search results?
Thanks
Hi, I'm using Excel 2010 and, adding paramters to api requeste, I receive an empty string.
If I apiurl?key=mykey I receive response with the xml file content and I can work with that.
If I apiurl?page=1&key=mykey I receive an empty response. If I put this request in browser path I receive the xml without problem.
I don't undestand why it happens.
Can you help me in solving this issue?
Thanks
Hi Sigma, great video but I appear to be stuck in the basic steps maybe you could give me some type of hint! I downloaded the son vba on my laptop and I can find it on my desk however when I go on excel and try to import it from my vba it does not seem to appear. If you have any idea this would help me a lot! Thank you in advance
hey sigma i have a question for you is there any way to talk to you direct as i would like to learn how its done so i can do it for other things
how safe is the file you used from github? I'm trying to learn ways to make something similar to this work at my workplace but with all the licensing and stuff, I'm hesitating to possibly use it.
It should be, I haven't had any issues with it. If he gave it an Open Source License, you could use it then. I would just cite where you got it, so if other people want to see the source code they know where to go.
Hi Sigma, Great Video . Can we make code a little dynamic for Column Headings input manually. For example we are putting each and every heading from Jason data for writing in excel like ID, Name, Latitude etc. So can that be made dynamic from the request and then make use it in the loop for any sort of headings it receives. As I am thinking to use the code for importing different type of Jason file for API testing . . thanks
Great stuff. Any reason why you don't write Dim xml_obj as NEW MSXML2... rather than setting it on a separate line?
This is really great and it has helped me a lot. I am trying to pull stock index data an it worked just fine. Is there a way we could pull json data for multiple urls concurrently. An in my case I use two index data and I am able to pull only 1 at a time so how can we pull for 2 urls and then put into excel?
Hello I have a question. I'm very new in this topic. Is it possible to get data from google sheet to excel using this method and vice versa? I have read that google sheet has an API and it is JSON format. It is possible to do with this method? Thank you.
Hi Eric, I got task to POST a JSON formated excel data through a API and get response. Can we do it with EXCEL POWER QUERY?
This is a great example, I have to admit it help me a lot with my project . Is there any chance of you making a video or just a code sample of a json post request? I am trying to update a woocommerce product with the json api. I have recreated the request with insomnia and everything works ok but with vba I dont know how to pass this argument { "regular_price": "58.35"}. Thanks again for the great work.
I'm using your example for sending Api SMS from a Ms Access module. It works great!!! Thank you!!
Glad it helped!
Hi do you have a similar example loading into an Access database table?
I need to know that how can we add a body content in REST API call using VBA like .SetRequestHeader "body", "grant_type:client_credentials"!!!
My API key is invalid, what should I put in Worksheets("api_key").Range("A1")?
I really need your helping building the url. I have the base url and I have my Api keys. I have 4 pieces of info they provided for the Api key which requires oauth. I just need help making the url right. Once the url works and I can run it in a browser and it returns proper data I should be able to use the url with power query. Are you able to help set this uo
:D !! I am also struggling with oAuth to join the variables containing API keys into the JSON string for the same attempts like you. It's a bit challenging and not sure at this time. But this video is helpful.
Nice video. I am trying to find a better method to pull stock data from the alpha vantage API. Currently using Power BI which crashes half the time.
Two questions.
In the first example if you change "a" to another letter, example "b" you get a "wrong number of arguments or invalid property assignment" error.
In the second example I get a "runtime error 424 / Object not defined" at "For Each result in Json("results")". Json is defined as an object.
Any thoughts would be appreciated.
Did you copy over the JSON source code into a separate module? It sounds like it can find the object.
@@SigmaCoding JSON source code is in a separate module.
Hi Sigma! Thanks for the video. It is very informative. I was able to download and parse json data. However, If I try to download complete data, excel starts to not responding. I have started .SetTimeouts 0, 0, 0, 0 and/or waitforresponse but the problem still persist. Any tips? Note: My data contains nearly half a million records. Thanks in advance for your help.
Thanks for the great tutorial. But i have a problem in json_parseObject at "set json _Parseobject = new Dictionary". I get "invalid use of the key new" . The Ms scripting runtime is already bound. I would appreciate any idea. I reproduced your sample in video 1.1 so i have no explanation what it could be.
Hi, had the same problem here, solved using Scripting.Dictionary instead. Hope can help
@@dolomix5688 I solved the problem by changing the priority of the library and getting this to the top.
I went down the path where I do the REST call and the JSON handling in a PowerShell module that I call from VBA. The return value will then be used in the VBA. Clunky but it works.
I like the creative approach. I'm assuming you found the parsing of JSON much easier inside of PowerShell compared to VBA?
@@SigmaCoding Absolutely! PS provides so much support for the JSON format and parsing isn't required at all.
You simply get the content of a JSON file with GET-Content and use ConvertFrom-Json to access the Json object. Small sample of how I get the name of a log file that I use:
# Get Json file content. Use the property RAW!
$ConfigJson = Get-Content ".\config\config.json" -Raw
# Read configuration settings
$Control = $ConfigJson | ConvertFrom-Json | Select-Object -ExpandProperty CONTROL
# Set Default / Start values
$LogFile = $Control.logfile
JSON (config.json)
{
"CONTROL": {
"synopsis": "Main controller configuration values",
"logfile": "RunLog.txt",
@@kimfucku8074 Wow, thank you for sharing! I'll definitely keep this in mind when parsing JSON in VBA. Thank you again. :)
@@SigmaCoding You are welcome! Sharing is caring. Stay safe and healthy!
Hey Sigma ! Thanks for the great video. Maybe you can help me. How can i add a header on this code ? I have a token that i cant add in the url.
You just use the setRequestHeader method:
' For Content Type.
xmlhttp.setRequestHeader "Content-Type", "text/json"
' An example of Basic Auth
username = "someusername"
password = "somepassword"
xmlhttp.setRequestHeader "Authorization", "Basic " + Base64Encode(username + ":" + password)
hi there i hope you can h elp me or point me in some kind of direction .. my goal - i wanna grab data from basicly a website in my case the value of my portfolio on a crypto currency service website into excel. they provide that data over api but when i try to connect it always endsup with authorization error. of course i have the public and private key but im unable to find out so far what exactly to do with them. importing public data which doesnt require authorization works fine only this authorizastion i have no idea and for weeks im searching online not finding anything. by looking at what you are doing here i thought it might be worth trying to ask you for advice or a hint maybe you know some place where one could find a example cheet to look at how things are supposed to be done. any kind of help is much aprectiated im desprate by now and dont know what else to do than to start asking people directly for help
Hi there,
If you could, please send me the code you already have to my email coding.sigma@gmail.com and a link to the API you're using so I can read the documentation.
i am receiving Variable not defined in base_url, are you declaring this variable in another place?
In the video, I had `Option Explicit` on, you need to turn that off so you don't get that error. If you don't you'll need to declare each variable at the top of the code. For example, `Dim base_url As String`.
Sigma Coding thanks sir!
Hello Sigma, thanks for this informative video! :)
I have some few question what if my JSON data it's kind like this:
{"results" : {
"values": [
[1,2],
[3.4]
[5,6]
]
}
I want to get all my data in values, but my values is in Array format not dictionary.
How can I get for example the One(1) value in the first Array.
Thank you.
I hope you notice this!
God bless !
You just need to index the array, that's all.
@@SigmaCoding Thanks Sigma! I actually found a work around.
But I have other problem and I already look around internet if there is work around on my problem, but I got no luck searching for answer.
I was hoping that you know the answer, or at least you already encounter this.
My API server has been shutdown, and when I request on the excel (and since server is shutdown, it doesn't give response), what happen is Excel is hang up waiting for the response.
Is there a way to set timeout on MSXML.xmlhttp60 ? it seems like there is no settings for that.
Again, thank you for responsing.
@@henroljeogutierrez1294 Take a look at this post on Stack Overflow that should do the trick. stackoverflow.com/questions/14052543/how-to-set-http-timeout-using-asp
Please do a video on this for TD-Think or Swim with VBA! I'm able to establish a successful 200 code connection but, my parsing returns nothing! VBA will allow people to skip python>pandas>excel process!. Thanks!
Just an FYI, it'll be hard to handle all the factors related to authentication using VBA. Not to say it can't be done, but it might take a significant amount of effort. I had a client who wanted to use the TD API from VBA, and my solution to him was writing a COM Server in Python that controlled the TD API Library and then have it be ingested by VBA.
I call it a happy medium solution. You can still get VBA, but you also have the complicated parts in Python. Would you be interested in a topic like that?
@@SigmaCoding Ya that would be awesome!!! Btw any idea why TD ameritrade's API can't get real time qoutes for Forex? it seems to work fine for everything else...
@@slave2karma In my adventures of trying to access TDA API for forex it appears that the only way is to use their streaming API to access forex history prices which is probably what you want. They have sample code written in javascript for it but like Sigma said it looks like it would be a bear to emulate that code in vba. I've had some success using Excel's RTD function to access forex prices but in order for that to work for anything really useful it appears to me that you would have to create your own history DB and port price changes to records in one minute increments. I've managed to get live real data pricing but it's next to useless without a history of price changes. FWIW.
Thank you for great sharing. Please can you share codes for sending file to Telegram by VBA.