Excel VBA Introduction Part 47.5 - Basic HTTP GET and POST Requests
ฝัง
- เผยแพร่เมื่อ 17 ต.ค. 2024
- By Andrew Gould
Download files here www.wiseowl.co...
POST requests begins @33:37
www.wiseowl.co.uk - This video explains the basic differences between the GET and POST methods and how you can use each method to send data along with an HTTP request. You'll learn about adding a query string to a URL for GET requests and how to send values in the body of a POST request. You'll also see how to ensure the values you pass to a request are properly encoded using the Excel EncodeURL function. You'll learn the basics of using the Chrome Developer Tools to work out how requests are sent by the Chrome browser to help writing your VBA code.
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos, you can click this link www.wiseowl.co... to make a donation. Thanks for watching!
You can buy our Introduction to Excel VBA book here www.lulu.com/sh...
Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
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!!!
Hi Adam! Happy to hear that the video helped you, thanks for watching and taking the time to leave a comment!
You are the best!!!!!!! I am able to code and make my tasks automated after watching your videos..very simple ..clean..and elegant explanations
Thank you, Sri! Very happy to hear that you're enjoying the videos, thank you for the feedback and for watching!
I liked the way you explain. You are just amazing providing advance stuff free of cost.
Thanks, glad you're finding it useful!
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!
Thanks to you for watching it Paul!
Your work is outstanding!!! And making it available for free is incredibly generous.
Thanks Joao, glad to hear your found it useful!
Such exceptional videos. I will be donating within the next couple of days. Thanks so much.
Glad you liked it, thanks for the support!
you are best canal with VBA videos, this awful language will never die in corpo env, so you help us :)
Thank you Piotr, I'm happy that the videos are helping!
You are my HERO good sir!!!, thanks so much for the awesome tutorials. Keep up the good work!!
You're very welcome Cesar, thanks for watching!
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?
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 th-cam.com/play/PLNIs-AWhQzcl3xKvF8sVL4sWRWICj_clM.html
I hope it helps and thanks for your support!
Help - I dowloaded your file and ran BasicGETRequestWithQueryString but after opening text file in webbrowser it is showing nothing ( Just layout with no content).
It looks as though the TH-cam 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.
That is a fantastic video. Better than anything I have seen. Thank you very much.
Hello Andrew,
I am stuck. I can't find form data for any site. Where should i look.
Great video Andrew. This is very useful information.
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!
Thank you very much!!!This is not gold but platinum!!Tanta ROBAAA si dice in Italia!!!!
Wow... That's pure gold my friend. Thank you very much for that. God bless you.
You're very welcome, glad you enjoyed it and thanks for watching!
I make it pure 'Gould'. 🙃
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
The URL is still a "file path". docs.microsoft.com/en-us/office/vba/api/excel.shapes.addpictur
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?.
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 th-cam.com/play/PLNIs-AWhQzcl3xKvF8sVL4sWRWICj_clM.html
I hope it helps!
Our admin said we have linups server (or something like that). Will this code work with linups server?
Hi there, I'm guessing your admin said Linux which I have no experience with so I'm sorry but I don't know!
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": [],
Hi Rodrigo! Sorry, I don't know the answer to that.
Thanks to make it easy, waiting for next tutorial, and also want to learn how to parse json by excel vba from you.
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
@Wells Kian Yea, been watching on instaflixxer for since december myself :D
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 "
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!
Is there a way to get an element by Xpath from a webpage in VBA but without installing Selenium?
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!
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,
Forgot to mention, you are my a true VBA guru, while watching other VBA Videos on TH-cam i usually fast forward them but not with your videos , they are in great details and covers almost all possible scenarios related to it.❤️❤️
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 th-cam.com/play/PLNIs-AWhQzcl3xKvF8sVL4sWRWICj_clM.html
I hope that helps!
@@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.😭,
@@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).
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.
Nothing in Excel is secure except perhaps some of the Microsoft code.
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 ??
Hi! I'm sorry that's not something I have any experience of so I can't offer any useful advice.
@@WiseOwlTutorials no problem sir thanks any way for those useful videos
Excellent.....
One question on how to use Patch request like get and post?
Any video or reference for that?
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.
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.
Amazing tutorial. Thanks, I've learned a lot!
Thank you Luiz!
Hi,
How to post # in the url request? system automaticaly replace with %23
Thank you very much. Very informative tutorial.
Cảm ơn bạn!
Thank you so much!
Incredible tutorial. Thanks!
I need an Oauth-token to retrive data is this comon?
Can they be eternal valid time?
I'm sorry, I don't know.
So amazing and wonderful
Thanks!
Very clear, thank you!
Cheers Matthew, glad you found it useful!
Run-time error '91': Object variable or With block variable not set WTF?
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.
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!
@@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?
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
@@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
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!
Would you consider doing a video on interacting with a GraphQL API using VBA?
Hi there! It's unlikely sorry, I don't know anything about GraphQL.
Brilliant! all clear! TY ! 🙂
You're very welcome, thanks for the support!
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
Hi Kashif I'll see what I can do!
WiseOwlTutorials
Thanks as always
Hey,
The video was wonderfull.
Can you please make a video to upload an image file to a server. Using POST multipart/form-data.
Nice one ....can you please provide that macro
hello what is the disadvantages xlsb over xlsm
www.google.com/search?q=xlsb+versus+xlsm
Thanks for the great explanation. Very useful indeed.
How may I send a binary file (an image, say) in a POST request?
You want multipart/form-data e.g. stackoverflow.com/a/51453795
This is some good shit man, good work
Cheers Ashley, glad you liked it!
You are a wiz!
Hi...I hope u make tutorial about oauth2 for vba
Hi there! We don't have any plans to make videos on this topic in the near future, sorry!
Great instructional video. One of the best on TH-cam 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?
Thank you so much 👍👍👍
You're very welcome Rajesh, thanks for watching!
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?
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!
@@WiseOwlTutorials I tried your code but the items in the MonthList combobox is still in Arabic ..
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"
Hi Yasser, you need the short month names "Jan", "Feb" etc.
I hope that helps!
@@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
9:50 you rapped there for few seconds, I see you can surpass Busta Rhymes.
😂 I don't think that any rapper has anything to worry about!
Thank you so much sir 🙏🙏🙏🙏🇮🇳
8:20