Excel VBA Introduction Part 47.5 - Basic HTTP GET and POST Requests

[@dinhbaotran8827]

Cảm ơn bạn!

[@WiseOwlTutorials]

Thank you so much!

[@AdamBowen93]

It's sad, but I am an amateur developer and have spent DAYS trying to get Excel to make an API Request to the US Census Bureau and it is only after watching this that I have succeeded. Thank you!!!

[@WiseOwlTutorials]

Hi Adam! Happy to hear that the video helped you, thanks for watching and taking the time to leave a comment!

[@paulucheoma9266]

This was by far hands down one of the best videos I've watched on this topic. And it's free? Saying thank you isn't even enough! God bless you!

[@WiseOwlTutorials]

Thanks to you for watching it Paul!

[@joaofilipecastrosantos9947]

Your work is outstanding!!! And making it available for free is incredibly generous.

[@WiseOwlTutorials]

Thanks Joao, glad to hear your found it useful!

[@sribalajispaceship]

You are the best!!!!!!! I am able to code and make my tasks automated after watching your videos..very simple ..clean..and elegant explanations

[@WiseOwlTutorials]

Thank you, Sri! Very happy to hear that you're enjoying the videos, thank you for the feedback and for watching!

[@mareklux9743]

Thank you! Do you think it would be possible to load a site. Post username+password. Click Javascript button. Do until status confirmed it is loaded and then Navigate until you at your goal?

[@WiseOwlTutorials]

Hi Marek! I think that you'll have difficulty interacting with Javascript elements when using XMLHTTP requests. You may have more success automating a web browser such as Chrome using SeleniumBasic for VBA. You can see some videos on how to do this in this playlist kzbin.info/aero/PLNIs-AWhQzcl3xKvF8sVL4sWRWICj_clM I hope it helps and thanks for your support!

[@LearnYouAndMe]

I liked the way you explain. You are just amazing providing advance stuff free of cost.

[@WiseOwlTutorials]

Thanks, glad you're finding it useful!

[@LD-nx8oe]

Such exceptional videos. I will be donating within the next couple of days. Thanks so much.

[@WiseOwlTutorials]

Glad you liked it, thanks for the support!

[@Aarmaxian]

That is a fantastic video. Better than anything I have seen. Thank you very much.

[@cesarchicaiza4831]

You are my HERO good sir!!!, thanks so much for the awesome tutorials. Keep up the good work!!

[@WiseOwlTutorials]

You're very welcome Cesar, thanks for watching!

[@krn14242]

Great video Andrew. This is very useful information.

[@WiseOwlTutorials]

Thanks! It feels as though the VBA videos are gradually becoming more techy and narrowly focused. It's good to know that people still find them useful!

[@sanjibpramanik9268]

Thanks to make it easy, waiting for next tutorial, and also want to learn how to parse json by excel vba from you.

[@wellskian5387]

Dunno if anyone gives a damn but if you guys are bored like me during the covid times then you can watch pretty much all of the new series on InstaFlixxer. Been binge watching with my gf these days xD

[@rhettulises4818]

@Wells Kian Yea, been watching on instaflixxer for since december myself :D

[@piotr780]

you are best canal with VBA videos, this awful language will never die in corpo env, so you help us :)

[@WiseOwlTutorials]

Thank you Piotr, I'm happy that the videos are helping!

[@giuliko]

Wow... That's pure gold my friend. Thank you very much for that. God bless you.

[@WiseOwlTutorials]

You're very welcome, glad you enjoyed it and thanks for watching!

[@BenLinfordUK]

I make it pure 'Gould'. 🙃

[@gianpaolo19]

Thank you very much!!!This is not gold but platinum!!Tanta ROBAAA si dice in Italia!!!!

[@shahbazkhan2283]

Hello Andrew, I am stuck. I can't find form data for any site. Where should i look.

[@manideepu2817]

Excellent..... One question on how to use Patch request like get and post? Any video or reference for that?

[@luisprieto5382]

Incredible tutorial. Thanks!

[@matthewyoung9250]

Very clear, thank you!

[@WiseOwlTutorials]

Cheers Matthew, glad you found it useful!

[@dharmaraju2908]

Nice one ....can you please provide that macro

[@mumbai4862]

So amazing and wonderful

[@WiseOwlTutorials]

Thanks!

[@jjtagh]

Would you consider doing a video on interacting with a GraphQL API using VBA?

[@WiseOwlTutorials]

Hi there! It's unlikely sorry, I don't know anything about GraphQL.

[@LearnYouAndMe]

Help - I dowloaded your file and ran BasicGETRequestWithQueryString but after opening text file in webbrowser it is showing nothing ( Just layout with no content).

[@WiseOwlTutorials]

It looks as though the KZbin example no longer works, probably due to changes in the way the site works since the video was recorded (this is always a problem when you're not in control of the site you're scraping). The other examples shown in the video still work.

[@The_Code_Father]

Awesome Tutorial Mr Andrew BTW i found alibrary for parsing and dealing with json files on github and i searched alot for detailed tutorials on how to use restful api's in excel vba and implement HTTP Requests using Excel VBA but I found nothing but very few tutorials about this topic The point that i have in my country a governmental E Invoice portal and they are using API and JSON files is there is anyway or any tutorial on how to do that using Excel vba and VBA JSON Parser library ??

[@WiseOwlTutorials]

Hi! I'm sorry that's not something I have any experience of so I can't offer any useful advice.

[@The_Code_Father]

@@WiseOwlTutorials no problem sir thanks any way for those useful videos

[@ivbit]

Our admin said we have linups server (or something like that). Will this code work with linups server?

[@WiseOwlTutorials]

Hi there, I'm guessing your admin said Linux which I have no experience with so I'm sorry but I don't know!

[@Got2bFit]

Thank you very much. Very informative tutorial.

[@zethlarsson4303]

Thank you for an exelent lesson. English is not my basic language, but it was no problem to understand you. I'v one problem with you examples. The function WorkSheetFunction.EncodeURL do not work for me. Have tryed several suggestions from the web, but no one works. I would be very happy for some advice about what can be wrong in my environment. 64 bit HP computer, Win11, Office 10.

[@WiseOwlTutorials]

Hi Zeth! Happy to hear that you're finding the videos useful. Are you working with Office 2010? The EncodeURL function was introduced in Excel 2013 so if you're using an earlier version I'm sorry but it won't work.

[@phoenixmoto5238]

Sir Andy Gould in this video,in subroutine parsewiseowlsearchresults to add image to sheet you have used addpicture and URL itself instead of first downloading and then refering to file path. So how this satisfies ?please comment

[@DM-py7pj]

The URL is still a "file path". docs.microsoft.com/en-us/office/vba/api/excel.shapes.addpictur

[@rodrigoaraujo922]

Hello, how are you? I really liked your explanation, but how do I show the field json below: I tried in several ways, but I couldn't. "physical capacity": [],

[@WiseOwlTutorials]

Hi Rodrigo! Sorry, I don't know the answer to that.

[@MeTube2014]

Is there a way to get an element by Xpath from a webpage in VBA but without installing Selenium?

[@WiseOwlTutorials]

Hi! Yes, you can use the MSXML2 library and use the SelectNodes method of a DOMDocument60 or XMLDocument object. Pass your XPath into the SelectNodes method. I hope it helps!

[@luiztechai]

Amazing tutorial. Thanks, I've learned a lot!

[@WiseOwlTutorials]

Thank you Luiz!

[@RupeshKumar-ms8bl]

Hey, The video was wonderfull. Can you please make a video to upload an image file to a server. Using POST multipart/form-data.

[@manikandanp1610]

Hi Andrew, Is there any way to interact website by using XML HTTP and HTML Library, Bz i could not see tag and data returning by http request as same like in browser for same website. Some tag and data missing in Http request because those are creating by script in browser. How this can be done?.

[@WiseOwlTutorials]

Hi Manikandad, if you need to interact with objects created by script in the browser then you'll need to automate a browser. We have some videos which explain how to do this in this playlist kzbin.info/aero/PLNIs-AWhQzcl3xKvF8sVL4sWRWICj_clM I hope it helps!

[@rayansalah5754]

Hi...I hope u make tutorial about oauth2 for vba

[@WiseOwlTutorials]

Hi there! We don't have any plans to make videos on this topic in the near future, sorry!

[@AntrikshSharma]

9:50 you rapped there for few seconds, I see you can surpass Busta Rhymes.

[@WiseOwlTutorials]

😂 I don't think that any rapper has anything to worry about!

[@simonjeras9269]

Hi, How to post # in the url request? system automaticaly replace with %23

[@Myrslokstok]

I need an Oauth-token to retrive data is this comon? Can they be eternal valid time?

[@WiseOwlTutorials]

I'm sorry, I don't know.

[@MrMgrPL]

Run-time error '91': Object variable or With block variable not set WTF?

[@hadibq]

Brilliant! all clear! TY ! 🙂

[@WiseOwlTutorials]

You're very welcome, thanks for the support!

[@mr.r6373]

How do i run subsequent xml requests ? ,when i open "SomeURL" on browser it runs other 267 subsequent requests(gets images &icons and JavaScript ), and gives output, but when i do with xml request, the response text only gives response for 1st,

[@mr.r6373]

Forgot to mention, you are my a true VBA guru, while watching other VBA Videos on KZbin i usually fast forward them but not with your videos , they are in great details and covers almost all possible scenarios related to it.❤️❤️

[@WiseOwlTutorials]

Hi Ravi, thanks for the kind words! I think the point of using XML HTTP requests is to avoid all of the other items that you've mentioned. The idea is to get only the response text of the initial request, not the results of javascript code, etc. If you want the results of the other items on the page then the best thing to do is use a web browser. I don't know if you've seen the recent videos on using Google Chrome from Excel VBA but there are several videos on the topic in this playlist starting at Part 57.1 kzbin.info/aero/PLNIs-AWhQzcl3xKvF8sVL4sWRWICj_clM I hope that helps!

[@mr.r6373]

@@WiseOwlTutorialsI am glad you responded ❤️, Yes i have seen chrome playlist, however due to organisation policy, IT department does not allow third party libraries/applications to install, hence can't use selenium.😭,

[@WiseOwlTutorials]

@@mr.r6373 That's so frustrating! I guess that the best option that you have is to use Internet Explorer (the videos which cover IE are in the same playlist I linked to above).

[@Aarmaxian]

If I use this in production to post and get data from a database (through a server), how can I protect the routes so that someone who gets hold of my workbook cannot see the data? I know this is a very unrelated question but I am really interested in knowing an answer to that one. Thanks.

[@DM-py7pj]

Nothing in Excel is secure except perhaps some of the Microsoft code.

[@ashb899]

This is some good shit man, good work

[@WiseOwlTutorials]

Cheers Ashley, glad you liked it!

[@mimocrocodile5069]

Hi. Thanks a lot. But I ran into a problem. In sub ParseBOEExchangeRates: HTMLString = "..." and after HTMLDoc.body.innerHTML = HTMLString HTMLDoc.body.innerHTML = begins with "

[@WiseOwlTutorials]

Hi Igor, did you try downloading the completed example file? This works correctly as shown in the video. You can find the link for this in the video description. I hope that helps!

[@thomasfergusen5144]

hello what is the disadvantages xlsb over xlsm

[@DM-py7pj]

www.google.com/search?q=xlsb+versus+xlsm

[@kashifkhanspecial]

Hi Andrew, I have one request to you I have very large PowerPoint file and in the file have around 15 to 20 slides and I want to update these slides with the updated data and also adjust height and width etc, can you make tutorial that how to take a reference of any previous shapes and add new data and adjust accordingly. Thanks Kashif

[@WiseOwlTutorials]

Hi Kashif I'll see what I can do!

[@kashifkhanspecial]

WiseOwlTutorials Thanks as always

[@KhalilYasser]

In the `BasicPOSTRequestMultipleValues` procedure, it works but in the saved HTML file I didn't find the results. Although I manually did it in the browser and got results .. I even tried to copy all the source code of the POST but the same problem.

[@WiseOwlTutorials]

Hi Yasser, I'm finding the same thing. This is the problem with writing examples using websites: things change! At least we can still use GET requests to retrieve the information we need with this particular site!

[@KhalilYasser]

@@WiseOwlTutorials Thanks a lot for reply. I know things change all the time. But I tried to use GET also and the same problem. What are the things that have changed?

[@WiseOwlTutorials]

Hi Yasser, the GET request still works Sub BasicGETRequestIMDB() Dim req As New MSXML2.XMLHTTP60 Dim reqURL As String reqURL = _ "www.imdb.com/search/title/?" & _ "title=john+wick" & _ "&release_date=2018-01-01," & _ "&genres=action" req.Open "GET", reqURL, False req.Send If req.Status 200 Then MsgBox req.Status & " - " & req.statusText Exit Sub End If 'Debug.Print req.responseText SaveHTMFile req.responseText End Sub

[@KhalilYasser]

@@WiseOwlTutorials Thank you. I know things change all the time but when trying GET I got the same problem. What are the things that have changed? I didn't notice any change

[@WiseOwlTutorials]

The code I've posted in my previous reply works, I've just tested it. I don't know what changed because I don't work for IMDB!

[@Amr-Ibrahim-AI]

Thanks for the great explanation. Very useful indeed. How may I send a binary file (an image, say) in a POST request?

[@DM-py7pj]

You want multipart/form-data e.g. stackoverflow.com/a/51453795

[@rajeshmajumdar4999]

Thank you so much 👍👍👍

[@WiseOwlTutorials]

You're very welcome Rajesh, thanks for watching!

[@eldarikus]

You are a wiz!

[@KhalilYasser]

Thank you very much for this plenty of information. I have learned a lot. As for this part For n = 1 To 12 MonthList.AddItem MonthName(n, True) Next n I got a list of months in Arabic. How can I made a list that generates months in English so as to make it work?

[@WiseOwlTutorials]

Hi Yasser, you can try this: Dim n As Long Dim EnglishMonthName As String For n = 1 To 12 EnglishMonthName = Format(DateSerial(2019, n, 1), "[$-409]mmm") MonthList.AddItem EnglishMonthName Next n I hope it helps!

[@KhalilYasser]

@@WiseOwlTutorials I tried your code but the items in the MonthList combobox is still in Arabic ..

[@KhalilYasser]

I could solve that point using this code Dim aMonths As Variant aMonths = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December") For n = 1 To 12 MonthList.AddItem aMonths(n - 1) Next n But when trying to run the code, I got the message "Select a valid date"

[@WiseOwlTutorials]

Hi Yasser, you need the short month names "Jan", "Feb" etc. I hope that helps!

[@KhalilYasser]

@@WiseOwlTutorials Thank you very much. I applied your idea and used the short names for months but I still got the same message (invalid date). I debugged the code and I changed `MonthList.Text` To `MonthList.ListIndex + 1` and this worked fine now. Thanks a lot for your patience

[@pradeeprawatvlogs8358]

Thank you so much sir 🙏🙏🙏🙏🇮🇳

[@deldube]

Great instructional video. One of the best on KZbin for this software. Just have one question... See below: The last Post request from IMDB John Wick did not work. Can you assist as to why?

[@노영진-g1y]

8:20