Excel VBA Introduction Part 47.1 - Browsing to Websites and Scraping a Web Page
Vložit
- čas přidán 2. 08. 2024
- TRY THE GOOGLE CHROME VERSION • Excel VBA Introduction...
PLEASE NOTE: x-rates.com now uses https rather than http. Please update the code as necessary.
By Andrew Gould
Download files here www.wiseowl.co.uk/videos/exce...
www.wiseowl.co.uk - Scraping a web page in VBA involves getting a reference to an HTML document and then processing its elements to extract the useful parts and write them to another location in a readable format. This video shows you how to do that using both Internet Explorer and basic XML HTTP requests. You'll learn a bit about the Document Object Model, as well as how to identify HTML elements by name and by tag type. You'll also see how to loop through various collections of HTML elements and their children using an example involving exchange rate tables.
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.uk/donate?t=1 to make a donation. Thanks for watching!
You can buy our Introduction to Excel VBA book here www.lulu.com/shop/andrew-goul...
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
Andrew Gould = Genius!
No other words are needed!
Just what I needed. You, sir, give the best videos on VBA and are awesome.
I have never encountered any VBA tutorial video of this amazing quality! I was able to follow easily and do what I wanted. Thank you so much for creating this video and I will be learning from you more from now on.
Just an amazing series, crystal-clear explanation, lots of hands-on experience, and perfect pacing--bravo, and thanks for this!
You are a great presenter! One of the best I have viewed on the Internet....keep up this great work.
I have been long time dealing with scraping websites. I have learned on my own but this video is awesome because it put all the ideas I had together and perfectly consistent. Thank you a lot for this video, it is extreme helpful.
By far the best video tutorial regarding web scraping with VBA!
I have to say, this has been the best video I have watched on scraping. Very well explained.
Thanks Michael, happy to hear you found it useful and thanks for watching!
Watched many vba videos from other tutors but not so informative, constructive and logical as yours. There is saying in English... knowledge itself is of no value, it's the use of it that makes it valuable, you are simply genius!! kudos !!!
IT took almost a week to finish this whole video step by step. Thanks a lot. You clarified everything in a very clear way. Thanks a lot. Thanks for your clear accent and your bright coding sense.
Thanks, Fahad! Happy to hear that you found the video useful and well done on getting to the end!
Dear i sincerely like to appreciate your teaching skills, each video of your is so informative and you have made it simple to understand.
You are an amazing teacher. I wanted to get currency rates onto my excel sheet, and you are sent to me by God. You have explained it in exquisite detail. Even a child can follow.
Ummmmmmmmm
I have no more words..... how fast and best way to explain
First of all thank you very much!!! for the in depth knowledge on this section which made our life so easy with doing tedious tasks while working. Secondly we gained helpful knowledge on getting bots deployed at work for us :-)
You're awesome Sir!!!!
Amazing, the teaching style, the content, truly amazing. Thank you.
Waw, i am 30 years old and use youtube for quiet a while; such a helpful video never came across my path. You sir just rock many cheers for your help
Wow - this is great material. I've already learned so much and I'm only 1/2 way finished. I am working my own script while I watch. Thank you for putting this together and providing great explanations of the why behind what you are doing.
Mr Andrew, you did a nice work there. Your videos have an invaluable source of information.
This is incredibly informative and very well presented. Thank you Andrew Gould for such a great video!
U r a life saver! This is the best VBA tutorial that ever had !
Best teacher of VBA, thanks for amazing tutorial series.
Andrew, I'm as happy as a little boy on his birthday with this tutorial! I'm going to have some fun with this code!
You are awesome, I have learned mant things following your videos, they are very very easy to understand,
Exactly what I needed friend!! Damn, this is some awesome stuff!
Thanks as always...!
Great Tutorial. Proper organisation and perfect execution.
This is a brilliant video tutorial ! Concise, explanatory and well presented....well done ! I wish I had found this video 2 years ago when I started self-learning how to scrape websites. I have struggled in all that time...now, I hope to take some of these tips to good use.
@@WiseOwlTutorials if you are not confident, then what does that make me ha ha ! I have used my existing basic skills to create a website auto-login tool. You see, at my work place there are thousands of people who have unique Usernames & Passwords for over 10 different sites they have to log into. This is very very time consuming, not forgetting where they store their personal data. So I developed a tool that automates this process using similar coding. Currently, the estimated savings run into multi-millions, using such a simple approach. I am struggling with some coding problems though, I don't suppose you provide 1-2-1 advice by any chance?
Very detailed and easy to follow tutorial. Top quality!
Amazing video! I appreciate your patience and time. Just what I needed! Thank you so much !
I followed all the steps. I get the result on the worksheets when I run module 4 but not getting it when I use the userform to input the data.
What a fantastic and useful tutorial. Very well explained step by step. I love it!
Your explanation is superbly detailed!
Thank goodness I found this. I hate doing the charlie work, and this is my saving grace to taking the lazy way out.
Even though I know a lot about VBA on Excel itself, I never tried Web Scraping before. I started reading some documentation on the Internet about it and tried to automate some few things at my job, but I couldn't quite get the results I wanted, mostly because my lack of knowledge on HTML code and VBA procedures for this. After 30 minutes of this video I accomplished everything I needed without many problems. I finished the video and I'm a fan of yours already. Thank you and I will be watching your other tutorials that I'm sure they're worth it.
That's great Carlos! So happy to hear that the video helped you to achieve your aim. Many thanks for watching and for taking the time to leave a comment!
@@WiseOwlTutorials Thank you for your kind answer! I have a question... Is this site possible to Automate? srienlinea.sri.gob.ec/sri-en-linea/#/SriRucWeb/ConsultaRuc/Consultas/consultaRuc
Because I would like to type a lot of "RUC" Numbers and first of all, is not compatible with Internet Explorer and secondly, I don't see any queries going on, it just loads like the interior part of the window with information. Do you have any video with a webpage alike or somewhere I could read about it? Thank you again!
Thank you so much. I needed this information to help me out on my project.
This was truely amazing demonstration
Thanks!
Awesome, we can do exact same thing without spending too much money on QTP/UFT. Thank you so very much. :)
Eager to watch 48 asap.
Very useful and clear explanation. Great work
Simply outstanding!
Started learning VBA yesterday, so Im really poor. But you did an awesome job!!! Thank you!!
Thanks for the video, this is of great help!
For one of the pages I work with, I encounter the problem, that I have to log on (which runs fine with VBA), then a new screen appears and there is a selection to the left as well as a standard column on top of the page. when running a For Each HTMLA In HTMLAs loop, I can get the hyperlinks of the top of the page, but cannot access them to the left. Do you have any way of helping me? The page seems to be formatted with mostly only hyperlinks, two TR and one table (that covers both the top row as well as the left column). Thanks
Very competent explained. For me as a german with medium english know how it is well understandable! THANKS!
Mad respect for this bro!
This is a fantastic video!! i have learned a great deal here. Thank you.
Thanks.. Great video... was very helpful, will still take me some time to master this concept of Internet Objects, and HTML Classes, two things I'm not familiar with.
Great video! Thank you!
Just a small question: how can I place 2 tables side by side ?
Very clear and simple presentation, thank you!
Thanks for watching!
I love you WiseOwlTutorials!
We love you too! 😀
I've been looking everywhere for this
Hi Andrew,
Thank you so much for this knowledgeable video tutorial about scraping a web page through Excel VBA, could you please post a tutorial about, how can we manipulate, extract or put information in “Frames” in a web page, and how can we click on JavaScript button, that is calling a JavaScript function.
Kindly request to you, please post a video tutorial about above queries that would be really helpful.
Thanks, once again for this lovely tutorial
Loved your contents, Highly professional and Informative!
Thanks for watching, happy to hear you enjoyed it!
Excellent tutorial. Thanks
Thanks for sharing this. It’s really helpful. Do you know if there’s a way to download a Tableau workbook using this?
Magnificent....Great info...very useful..thanks a ton
Hi. Thank you for your introduction to web scrapping. It was extremely helpful in my own project where I have downloaded historical prices and dividends from two different pages and combined them to single query. The only problem I didn't managed to resolve is to how append master table with new pieces of information from additional queries. As excel allow me only to append to queries and save result as a new one I was ought to save new master and delete the old one after checking for accuracy of new information.
By the way I can share my results somehow to you if you are interested.
Wow.. Awesome Video, Sir !
Very much informative... Liked your approach... Thanks mate
Thank you for this ever so helpful information, perhaps you need to enter an "organised thinking" contest !!!
Thank you Dimitris! I had not heard of an organised thinking contest but I just Googled them and I am now going to waste hours trying to complete the questions!
Great tutorial, but when using XML method found that it don't retrieve tables controlled by java script. Where as IE method I can create a loop, allowing for extra content to load. is there a way in the XML method where this can be simulated?
As usual, superb 👍
Thank you, glad you enjoyed it!
Thank you so much for these wonderful tutorials! I had no idea Excel could do so much, and you do an excellent job of explaining things!
I'm curious, is there a way to use VBA to interact with web application written in javascript? Thanks again!
Hello sir. I just happen to watch your video a couples of weeks ago, and try it with some pages here at work but it gives me some kind of error on the Do While readystate_complete. It says “the object invoked has disconnected from its clients”. Do you know what is that ? Than you for the help
You are amazing! WiseOwnTutorial - Thanks for the tutorial
Thank you for watching!
Great Tutorial!!! Just on point!!!
Thanks Federico!
Thank you so much for the tutorial ( Love from INDIA)
I am do an automatic web scraping. The values are changing every hour. Is it possible that every hour the automated imported data get recorded on separate cells rather than replacing it?
Thanks for the video, just need to ask as i have ids under div, then fieldset, and then class. Not able to reach that part to find the element by id and select.... Im not a programmer but tried to follow you... But no success....i can send all the html code to look for...basically after submit on first page it opens another page on same page, that is where i need to select element by id to fill data from excel....see if you can help...
Andrew Gould, VBA king. Love your tutorial! Thank you! Would you do a tutorial on API, RESTful API, PowerQuery in the future?
Great Video. Learnt lot of new things. Thanks
Excuse me Andrew, but when I use the simple sub BrowseToSite, sometimes the internet page does not open in full screen on the pc. Other times it opens, but only the open page icon remains in the taskbar and is not displayed on the screen. How can I make the Internet page open to full screen when I run the macro? Thank you very much, you are the best!
Wow.. what a video, Awesome Sir
Thank you, Mahesh!
The Readystate Complete code works for the initial page load, but not for subsequent page loads. Can it not be used more than once in a sub routine?
Fantastic Video!!!! You are the best!
Thank you, Rebeca!
Hi Andrew,
Need ur help to understand as in the codes we have fixed the values for currency and amount. What if these are to be filled by user at time of navigation. How are we going to action this.
Pls help.
Wishes,
Amit
How do I choose what table I want to get the information from. I currently have a site with 14 tables.
What a great video tutorial! You're a genius!
I only have one question, sir. What code shall I use if the owner/creator of the site disabled the "button" and will only be activated when you manually type in or paste a value in search bar? Thank you so much!
One of the Best video....
Dear Andrew, first of all, thank you very much for the great video. I have got on question: If I click on tag and browse to the next site, how do I activate the new page to be able to click on -tags on the new page? Thank you in advance.
Is there any way to scrape websites that use dynamic HTML ? I was trying to scrape a web page for stock data (investing.com) but the site generates the additional query string needed from an autocomplete list which is generated dynamically.
How Can I make my complete vba a project run on a network such that, items sold by client 1 will show on client 2 instantly?
Thank you so much! That's exactly what I was looking for 🙂
Happy you found it useful, thanks for watching!
These videos are awesome, I do need help with one thing though, I need to remove the automatically detect settings in LAN settings>Advanced Settings> Internet Options when im accessing a site. Ive looked over the object library and cannot see an option, can you help, please?
dude you are awesome
Great tutorial Andrew. I learnt a lot thank you. I have tried these methods but found I can't get them to work on web pages that use javascript to update the page. I was trying to scrape the song Artist & Title from Radio webpages with a 'Player' that streamed content and was updated dynamically with javascript. Unboubtedly my inexperience so any chance of a tutorial to cover that type of page?
Is there any way of scraping the list of videos in a playlist or in the "Watch Later" page on CZcams?
Excellent.....Tons of Thanks....
You're very welcome, thanks for watching!
Andrew, outstanding video(s). I noticed since this one is three years old, that the HTML file, yours in particular has changed and some of the code does not work the way you wrote it long ago, is there a way for you to include a ./legacy html page that will ensure the scraping html to VB examples continue to work in perpetuity ? I am having the same problem with other authors as well.
Kudo's to your step by step approach though, very well done and thought out. I know the web page has changed since they do not match when I load a current copy.
what a great tutorial！
i did a automation for to download the files in internet explorer, while downloading it asking for open, save or cancel. i am stuck up with this situation. i save automatically without prompt message. kindly help on this... Thank you
I fetched the data from a url, which includes only plain text , in multiple lines, but when I use this code, it put everything in one cell, without line break, can someone help please to add each line in a cell?
XMLdoc.body.innerHTML = XMLpage.responseText
Range("B2").Value = XMLdoc.body.innerHTML
Can I do the 'reverse' of web scraping? Like upload some data from excel to a website.
At 38:38 there is the line HTMLA.getAttribute("classname") for retrieving attribute information but the attribute text in the HTML appears as "class". Using "class" returns Null but "classname" works. Why is this please?
This guys is Amazing!
Hi Andrew,
How do we adopt the code to Edge?
Thank you
father for vba excel------------->Andrew Gould
thank sir
Hello Sir,
i am trying to capture amazon prices using following code , but it isn't get worked, could u please give me any suggestion or hints for this - thanks
Sub amazonprices()
Dim xmlpage As New MSXML2.XMLHTTP60
Dim htmldoc As New MSHTML.HTMLDocument
Dim htmltables As MSHTML.IHTMLElementCollection
Dim htmltable As MSHTML.IHTMLElement
Dim rowhtml As MSHTML.IHTMLElement
Dim cellhtml As MSHTML.IHTMLElement
Dim spanhtml As MSHTML.IHTMLElement
xmlpage.Open "get", "www.amazon.in/dp/B01LZ8O3JZ?psc=1", False
xmlpage.send
htmldoc.body.innerHTML = xmlpage.responseText
Set htmltables = htmldoc.getElementsByTagName("table")
For Each htmltable In htmltables
For Each rowhtml In htmltable.getElementsByTagName("tr")
For Each cellhtml In htmltable.getElementsByTagName("td")
For Each spanhtml In htmltable.getElementsByTagName("span")
Debug.Print spanhtml.innerText
Next
Debug.Print cellhtml.innerText
Next
Debug.Print rowhtml.innerText
Next
Debug.Print htmltable.className
Next
End Sub
thank u sir for active response i mean it does not return the results(price of product)
Ur response to our query and other question that really makes you really
so special. I really like to work our on your suggestion and really
eager to share outcome - stay blessed and healthy
Hello Sir i worked as per ur suggestion but still not able
to get desire result could you please help me with below one thanks
Dim htmldoc As New MSHTML.HTMLDocument
Dim htmltables As MSHTML.IHTMLElementCollection
Dim htmltable As MSHTML.IHTMLElement
Dim rowhtml As MSHTML.IHTMLElement
Dim cellhtml As MSHTML.IHTMLElement
Dim spanhtml As MSHTML.IHTMLElement
xmlpage.Open "get",
"www.amazon.in/Campus-Sutra-Varsity-Sweatshirt-Pocket/dp/B013HP8XOE/ref=br_asw_pdt-4?pf_rd_m=A1VBAL9TL5WCBF&pf_rd_s=&pf_rd_r=FJFNB5DQH0Z8RA01A1JM&pf_rd_t=36701&pf_rd_p=ee232f31-b795-49ad-895e-336733526c66&pf_rd_i=desktop",
False
xmlpage.send
htmldoc.body.innerHTML = xmlpage.responseText
Set htmltables = htmldoc.getElementsByTagName("table")
For Each htmltable In htmltables
For Each rowhtml In htmltable.getElementsByTagName("tr")
For Each cellhtml In htmltable.getElementsByTagName("td")
' For Each spanhtml In htmltable.getElementsByTagName("span")
' Debug.Print spanhtml.innerText
' Next
' Debug.Print cellhtml.innerText
Range("a1").Value = cellhtml.innerText
Next
' Debug.Print rowhtml.innerText
Range("b1").Value = rowhtml.innerText
Next
'Debug.Print htmltable.className
Range("c1").Value = htmltable.innerText
Next
End Sub
thank us so much sir, ur given sugestion really works may Allah bless u for ur noble work in teaching, if i get a chance to come UK i really like to have handshake with u
Hii sir, is there any way to find the latest uploaded file by date using vba
Thank you so much! Great tutorial
Thanks for watching Mohammed!
Awesome tutorial!!!
Could someone tell me if it’s possible to populate those tables, rows and cells with data?
How can we do that?
Hello, i try use module 2 but have problems with script, for my templete is Input and i don't know how write in script , if you can help me please
is it possible to scrap data from Linkedin if I use the same kind of VBA coding?
I am getting the Run time error as "Object Required". Can you please help me with it.
It would be easy to get high grades in college if all professors are like Andrew Gould. He even created Flappy Owl in Excel-a Flappy Bird-inspired game. Although Excel-based games are not entirely a new concept but being able to do that is very impressive..
Thanks! I love the user name!
@@WiseOwlTutorials This is the video that got me into VBA programming and made me understand how this works when I watched this around 2016 or 2017. Prior to this, I really wanted to learn how to do this but I don't know where to start until I found this. So, thank you for doing these videos. I really love your content-Fast-paced, concise, and lots of real world examples; and because you put an extremely high standard in terms of video tutorials, the DOWNSIDE is I can't even watch other tutorials now, other than WiseOwl tutorials, without moving the slider way farther, almost near the end because I want to finish it immediately but with your tutorials my face is fixated near my screen from start to finish because I don't want to miss anything..
@@WiseOwlTutorials I'd like to support you guys in any way I can. So, I'd like to purchase one "Intro to Excel VBA" book. Do you happen to have an e-Book version of it? I know you have a paper back version of it but logistics would be a pain for me because I don't live in the UK, I live in the Philippines..
@@WiseOwlTutorials I hope in the future, you can make C language tutorial using Visual Studio. It's not Microsoft, so if it's not possible, VB.Net using VS would be great, too. I downloaded VS Community but I'm lost, I don't know where to start. I know you have C# with VS, but that's in my bucket list for now..
@@SpaghettiCode5 I'm very happy to hear that the videos have helped you so much and I really appreciate you taking the time to write these comments, thank you so much!