Making API Requests in VBA | JSON

  Рет қаралды 44,178

Sigma Coding

Sigma Coding

Күн бұрын

Пікірлер: 63
@Yoffa006
@Yoffa006 3 жыл бұрын
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!
@basiljob5407
@basiljob5407 8 ай бұрын
Hi do you have a similar example loading into an Access database table?
@Amr-Ibrahim-AI
@Amr-Ibrahim-AI 4 жыл бұрын
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 :)
@MakeItHappenWithVBA
@MakeItHappenWithVBA 2 жыл бұрын
Hello sir. I would like to ask if you were able to connect excel to access using this method?
@Budgetblueprint786
@Budgetblueprint786 4 жыл бұрын
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
@nestordavidplascenciagarci171
@nestordavidplascenciagarci171 4 жыл бұрын
I think it's easier to parse a json file with the split function. Nice tutorial!.
@ronpenrose3467
@ronpenrose3467 4 жыл бұрын
Great instruction for API newbie. Thanks.
@SigmaCoding
@SigmaCoding 4 жыл бұрын
Glad you enjoyed it!
@TheNooberd
@TheNooberd 3 жыл бұрын
Great stuff. Any reason why you don't write Dim xml_obj as NEW MSXML2... rather than setting it on a separate line?
@gilbertocampos9732
@gilbertocampos9732 3 жыл бұрын
My API key is invalid, what should I put in Worksheets("api_key").Range("A1")?
@martinbrien9104
@martinbrien9104 4 жыл бұрын
Yes.. Sydney Australia.. and YES.. they are good... been to 2 of them .. :) - great vid by the way..
@MakeItHappenWithVBA
@MakeItHappenWithVBA 2 жыл бұрын
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.
@ramakantjoshi6551
@ramakantjoshi6551 3 жыл бұрын
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?
@frehmanpk
@frehmanpk 3 жыл бұрын
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"!!!
@spidaspidy
@spidaspidy 4 жыл бұрын
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
@Budgetblueprint786
@Budgetblueprint786 4 жыл бұрын
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
@PramodRaiK
@PramodRaiK 4 жыл бұрын
: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.
@rizdizla
@rizdizla 4 жыл бұрын
Thanks for this, it was really useful
@SigmaCoding
@SigmaCoding 4 жыл бұрын
Glad it was helpful!
@barbaro9154
@barbaro9154 4 жыл бұрын
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
@djkujo007
@djkujo007 3 жыл бұрын
Amazing tutorial 🙌
@powergaming-tu6wj
@powergaming-tu6wj 4 жыл бұрын
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
@くろちー-b2g
@くろちー-b2g 4 жыл бұрын
This is the great video. Thank you.
@saxena1955
@saxena1955 3 жыл бұрын
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
@JakaRuiz
@JakaRuiz 5 жыл бұрын
i am receiving Variable not defined in base_url, are you declaring this variable in another place?
@SigmaCoding
@SigmaCoding 5 жыл бұрын
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`.
@JakaRuiz
@JakaRuiz 5 жыл бұрын
Sigma Coding thanks sir!
@chillax411
@chillax411 4 жыл бұрын
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.
@PSBale
@PSBale 4 жыл бұрын
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.
@SigmaCoding
@SigmaCoding 4 жыл бұрын
Did you copy over the JSON source code into a separate module? It sounds like it can find the object.
@PSBale
@PSBale 4 жыл бұрын
@@SigmaCoding JSON source code is in a separate module.
@kgolthi
@kgolthi 4 жыл бұрын
Great Job. Very nicely explained. Thanks/
@SigmaCoding
@SigmaCoding 4 жыл бұрын
Glad you liked it
@smarmanos
@smarmanos 3 жыл бұрын
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.
@lolarenan359
@lolarenan359 4 жыл бұрын
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
@amitmanolkar
@amitmanolkar 3 жыл бұрын
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?
@bioactiveSaha
@bioactiveSaha 2 жыл бұрын
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.
@stefanozito
@stefanozito 3 жыл бұрын
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.
@dolomix5688
@dolomix5688 3 жыл бұрын
Hi, had the same problem here, solved using Scripting.Dictionary instead. Hope can help
@stefanozito
@stefanozito 3 жыл бұрын
@@dolomix5688 I solved the problem by changing the priority of the library and getting this to the top.
@nenaGarciaable
@nenaGarciaable 4 жыл бұрын
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.
@SigmaCoding
@SigmaCoding 4 жыл бұрын
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.
@lapagliafamily
@lapagliafamily 3 жыл бұрын
I'm using your example for sending Api SMS from a Ms Access module. It works great!!! Thank you!!
@SigmaCoding
@SigmaCoding 3 жыл бұрын
Glad it helped!
@affoltersammy549
@affoltersammy549 4 жыл бұрын
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.
@SigmaCoding
@SigmaCoding 4 жыл бұрын
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)
@VillaOuk
@VillaOuk 3 жыл бұрын
Thank you for great sharing. Please can you share codes for sending file to Telegram by VBA.
@Dayta
@Dayta 4 жыл бұрын
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
@SigmaCoding
@SigmaCoding 4 жыл бұрын
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.
@slave2karma
@slave2karma 5 жыл бұрын
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!
@SigmaCoding
@SigmaCoding 5 жыл бұрын
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?
@slave2karma
@slave2karma 4 жыл бұрын
@@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...
@kakol6303
@kakol6303 4 жыл бұрын
@@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.
@henroljeogutierrez1294
@henroljeogutierrez1294 4 жыл бұрын
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 !
@SigmaCoding
@SigmaCoding 4 жыл бұрын
You just need to index the array, that's all.
@henroljeogutierrez1294
@henroljeogutierrez1294 4 жыл бұрын
@@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.
@SigmaCoding
@SigmaCoding 4 жыл бұрын
@@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
Building User Forms in Excel VBA | Export Manager Pt. 1
18:39
Sigma Coding
Рет қаралды 2,2 М.
Making API Requests in VBA | XML
32:06
Sigma Coding
Рет қаралды 18 М.
To Brawl AND BEYOND!
00:51
Brawl Stars
Рет қаралды 16 МЛН
She made herself an ear of corn from his marmalade candies🌽🌽🌽
00:38
Valja & Maxim Family
Рет қаралды 16 МЛН
Use Excel VBA to Read API Data
20:48
Excel Macro Mastery
Рет қаралды 143 М.
RIP: MY EXCEL VBA CAREER (2008-2024)
15:22
Tiger Spreadsheet Solutions
Рет қаралды 4,3 М.
Windows API in VBA - Strings (Part 1)
30:44
codekabinett.com/en
Рет қаралды 3,3 М.
Using API Keys in Excel
22:22
Skills With Sid
Рет қаралды 15 М.
Using VBA with JSON APIs - Harvest
48:17
kainC
Рет қаралды 10 М.
Using Pathlib in Python
29:34
Sigma Coding
Рет қаралды 12 М.
Get UNLIMITED Tweets by Python Without Twitter API
7:13
AI Spectrum
Рет қаралды 106 М.
Access VBA | Introduction
34:05
Sigma Coding
Рет қаралды 4,3 М.
The Easiest Way to Scrape Web Data with VBA
7:18
Excel Macro Mastery
Рет қаралды 114 М.