Excel VBA Introduction Part 47.2 - Scraping Website Tables and Clicking Links

[@pradeeprawatvlogs8358]

Wow actually i was looking for this topic I don’t know how to thank you. You are my coach, mentor, guide and inspiration thank you so much sir 🙏🙏🙏🙏🇮🇳

[@ramakantjoshi6551]

Wow!.... Sir... You are MASTER.. You have great skill of TEACHING. Will pray to GOD for your health & prosperity🙏👏🙌

[@WiseOwlTutorials]

Thank you for the kind words Ramakant!

[@michaelmcnerney4393]

Thank you for this excellent and patient discourse on this hard to find (for me) topic

[@WiseOwlTutorials]

My pleasure Michael, I'm happy to hear that you found it useful! Just in case it's of interest we have a playlist which groups all of our videos on this topic kzbin.info/aero/PLNIs-AWhQzcl3xKvF8sVL4sWRWICj_clM You might find the videos on working with Google Chrome useful - I find it much easier than IE!

[@noisearchitect]

Your tutorials are the best that I have seen. Thank you for taking your time to teach us.

[@charliesk6239]

Absolute Gold Boss! Love your videos so much I think I am addicted. Cheers!

[@SujoyDebnath1]

You are really a great man....Thank u for teaching us...

[@edsonmatheus7976]

Hi Andrew, big thanks for the nice work. I tried to do this same example with XLM method but i get this error: "Run-time error '438": Object doesn't support this property or method. The error occured in the line: Set TableRows = HTMLTable.getElementsByClassName(TableName & "-general-header") Do you have any ideia what causes this?

[@edwinmiranda4005]

Very helpful and well organized video!

[@WiseOwlTutorials]

Thanks!

[@krn14242]

Wow. Great video Andrew. Intense.

[@dominikba91]

Hi Andrew, thanks for the video :) I have a note that has nothing to do with the good content. Maybe you already noticed it yourself, but in the video with the number 47.1 your voice sounds a lot clearer.

[@WiseOwlTutorials]

Hi there, you're welcome! Yeah, there's two and a half years between when those two videos were released, I've been through a few equipment and venue changes since then! I think that the older video has a lot of background noise which the newer videos don't have. But you're right, the volume is definitely louder in the older one!

[@shaileshsharma668]

I hope you are doing well Thanks for updating such kind videos I am requesting. Could you please upload some videos of PDF automation

[@WiseOwlTutorials]

Hi Shailesh! I'm sorry but it's unlikely that we'll have any videos on PDF automation in the near future.

[@eldarmammadov7872]

It gives me error on this line telling object variable is not Set "Set HTMLTables = HTMLDiv.getElementsByTagName("table") ". I changed it to "Set HTMLTables = HTMLDoc.getElementsByTagName("table") Now it works, why it was so?

[@oneworldtwowheels7747]

Hey Andrew, Good Tutorial, Brother please keep doing the good job. Amazing stuff on your channel. Though I have a question, I tried to webscrape an ecommerce site which is dynamically loaded and i was successfull able to get the text which i needed (Product description, sale and regular price etc) but can i also download/fecth the images in excel of those product and if yes, how it can be done. Can you advice on it, thank you for your support.

[@WiseOwlTutorials]

Hi! You can use the URLDownloadToFile function as described here kzbin.info/www/bejne/gIHIq6WNrNumgK8 Or use the technique shown in the answer here stackoverflow.com/questions/42184990/downloading-images-from-url-and-renaming I hope it helps!

[@dereklauder7]

Great video, wondering if there's a way to close ie after the code has exeuted?

[@WiseOwlTutorials]

Hi Derek, you can apply the Quit method to close IE. I hope it helps!

[@Jrohit12]

Really like the videos. But have a question. Is there a command to set element value or to set a value attribute from Excel sheet?

[@bobnathon243]

Hello Andrew Sir, how are you. Sir when is your Birthday, pls tell. We will feel very happy to celebrate our world's best Teacher.

[@WiseOwlTutorials]

Ahh thank you!

[@KyleRichter23]

Thank you so much, would you mind grouping these together as a playlist on KZbin channel’s Playlist menu like you do for your other videos? Many thanks again!

[@4thehell]

Thank you Andrew for this great video, please Upload more video on Web scrapping with combo box click

[@rhapses007]

Hello Andrew, Thank you for the video. It really helped me in my daily routine work. And, can we take screenshot of active webpage and paste in out look email.

[@WiseOwlTutorials]

Hi! If you're using Selenium Basic for VBA you can just use the TakeScreenshot method. If you're using IE then it's a bit more complicated stackoverflow.com/questions/45551849/how-to-take-screenshot-of-webpage-and-paste-it-in-a-word-document We have a video which shows how to create Outlook emails using the Word editor which shows how to paste into the email. I hope it helps!

[@nuhzakir8896]

Thank you for the video and sharing your knowledge.

[@salmantradinglive]

Very Useful Information. But can you help me with the dropdown options

[@abhisheksaraswat6753]

Hi, Andrew, Please upload more videos on web scraping and Advanced function of VBA

[@linavargas3318]

Hola Como puedo hacer para hacer Click en una fila de una tabla de un sitio web, en la que al pasar el puntero del mouse sobre ella, se resalta esta fila. Tengo un proyecto de automatizacion VBA en curso pero no puedo hacer esto y me siento frustrada. He intentado con el getelementsbyclass y relaciono el nombre "class" que le corresponde para luego hacer clic pero no funiona. Creo que esa tabla usa javascript. Puedes orientarme?

[@jackOfAllTrades93]

Hi Andrew, first of all, I greatly appreciate your content as I have learned a great deal from it. I was working on a project a about year ago that required me scrape very specific pages and tables, from sec.gov, that had many parent-child links. Though VBA was not really suitable for the volume of data that I was dealing with, at the time I didn’t have the skill set to use anything else. That being said, I quickly found that IE automation is quite slow and so I searched for VBA alternatives for longer than I care to admit, lol. What I discovered was the MSXML2.ServerXMLhttp.6.0 object, which turned out to be blazing fast. I think that would be a great video topic for this channel if you are up to it. Cheers!

[@jackOfAllTrades93]

WiseOwlTutorials Ah, indeed you have! I agree, selenium is excellent. I look forward to the videos!

[@sashatv138]

Thank you, Andrew!

[@Morphineck]

Man, this was a god like display of programming and teaching skills. Excellent work. Question: Is there a resource like a dictionary to kind of guide you which functions to call depending on what you're trying to do? Or is there no workaround and you have to patiently read all the related documentation? For example, there was a function that you knew existed but wasn't listed on the available functions, how did you find that? Or is that kind of knowledge only available to demigods with supercomputers as brains? Much respect brother, you really are a master on this.

[@WiseOwlTutorials]

Hi Chezare, that's very kind of you to say, thank you! There is a form of dictionary built in to the Visual Basic Editor - the Object Browser. You can press F2 or choose View | Object Browser to see it. You can see a list of Classes on the left hand side. You can select a Class to see which Methods and Properties you can apply to it. In this video I used the IHTMLElement Class for many of the variables. In other programming languages, the I at the start of IHTMLElement is used to indicate an Interface, which is sort of like a blueprint for a Class. The Interface contains all of the basic Methods and Properties for a particular type of item. A Class implements an Interface and will provide access to all of the Methods and Properties defined in the Interface. The Class can also define its own unique methods and properties that aren't defined in the Interface. You can see the documentation for IHTMLElement here docs.microsoft.com/en-us/previous-versions/office/developer/office-2003/aa219328(v%3Doffice.11) I used IHTMLElement simply for convenience because it can be used to refer to any HTML element on a page, whether it's a button, a table or even the head section. The MSHTML library also has many Classes for referring to specific types of HTML element. In the Obejct Browser you'll find things like HTMLTable and HTMLButtonElement. Variables declared using these Classes will only accept references to specific types of HTML element. The IntelliSense will also show more Methods and Properties because the Class expands on the basic list defined in the Interface. To answer your question: it certainly isn't knowledge that's available only to certain people! It can just take quite a long time to find it when you're not quite sure where to begin looking! Sadly, there is no substitute for time and experience with this sort of thing - the only difference between us is that I've been doing it longer :D Apologies for the long answer, but I hope that helps. Happy learning, my friend!

[@Morphineck]

@@WiseOwlTutorials Thank you so much for your reply man, you're truly a legend. Have an awesome week.

[@BijuVarghese]

is it possible to wait for next line of code to execute till jquery complete its execution in vba ? when i pass IE.ReadyState it always gives 4 (means ready) but table on the web page doesn't populate.

[@weesiang5645]

Hi Andrew, thank you for this video and the detailed procedure in writing the vba code. Could I ask why i would enter the error "Automation error, Unspecified error" even after specifying the check on the readystate? Thank you!

[@DM-py7pj]

It might be a security setting / firewall problem. Helpful to specify on which line error occurs.

[@81flame]

You are wonderfull person.Thanks. I ask one question. Your code "Part 47.2 - Scraping Website Tables and Clicking Links" doesn't work İnternet Explorer. You must use Microsoft Edge or Chrome May you made again according to Chrome, please? I don't success to chage CHROME :( 😔😔😔😔

[@WiseOwlTutorials]

Hi! You can use this playlist for videos on using Chrome with VBA kzbin.info/aero/PLNIs-AWhQzcl3xKvF8sVL4sWRWICj_clM I hope it helps!

[@MonirulIslam-ch8is]

great!!!

[@WiseOwlTutorials]

Thanks!

[@petardzopalic3220]

Can you please help me ,on my project in Google Chrome Inspector I see inner text example 9,923,167.09 , but when I want to see inner text in VBA td innertext is " ". What I missing ? Simply VBA cant see Ghrome Inspector inner text vaules.

[@janezklun]

Thank you for this great video, cheers

[@komaldhirmalani9464]

I have a problem clicking on a button in the desired website.....i am able to enter the text in textbox but i am not able to click on submit button

[@phoenixmoto5238]

Sir Andrew this works with internet explorer but if use selenium to scrap this website in chrome ,even if we click links table is not accessible. Why this is happening sir?. Please make a video on selenium for this same website whoscored.com.

[@AshokKumar-eu4dd]

Hi Andrew , it's one of best vba video , i have been following videos and learned lot about vba . Recently i have seen ur outlook videos . i need a help about outlook . As daily work i have multiple email accounts in work life and receiving excel attachment with date wise. there are region wise worksheet in attachment . i need to update region wise records in dashboard. ex : today i got 200 records in attachment sheet this number i have to update in dashboard .can you please make video for requirement.

[@ravisuryawanshi2516]

Hi sir, Can you please make entire series on this, i really need more knowledge on this as website changes are different...

[@alexliu5305]

it seems whoscored.com launched a new function to block the web scraping. is there any way to teach our vba to override it?

[@UbeWanCamote]

What if the table I want to pull data from does not have a table id? The tag above the table has an id but the inside that doesn't. The structure looks like this Info to be pulled ... /tbody> .

[@WiseOwlTutorials]

Hi there, you can parse the HTML returned in a variety of ways, each webpage will be different. If you can't get a reference to a specific element using its ID, you can try getting a reference to a collection of elements of the same type using the GetElementsByTagName("table") or GetElementsByClassName("ClassNameGoesHere") You can see a few examples of parsing the HTML of different web pages in this video kzbin.info/www/bejne/p3abo2qJbpqiisk I hope it helps!

[@cristiandragan5398]

I am also trying to scrape the standings table and print it in Excel. However, when I try to run my code it apparently just doesn't recognize the tags in the table body. I have even tried to copy your code from the video and run it and I get the same issue. Using several Debug.Print statements I have deduced that it seems to recognize all the other tags, including the tags in the table head, but nothing shows up for the table body. I have spent a couple hours trying to figure this out but I can't see what the problem could be, any help would be appreciated.

[@WiseOwlTutorials]

Hi Cristian, it looks like the design of the website has changed since this video was recorded to rely on javascript to generate the body of the table. Unfortunately, this means the code written at the time isn't going to work any longer. I suspect that a different approach is required, perhaps using the Selenium wrapper to provide more control (and choice) over the web browser github.com/florentbr/SeleniumBasic/releases/tag/v2.0.9.0 Sorry that you wasted your time on this!

[@cristiandragan5398]

@@WiseOwlTutorials No worries! It was still a good learning experience and I'm glad that at least I wasn't doing anything wrong on my end haha

[@ruipereira6637]

I having a problem reading Portuguese Char like "í" -> comes "?", any chance to do a video for that situation

[@DM-py7pj]

where? In the worksheet or the immediate window or somewhere else?

[@tobiewaldeck7105]

I don't know if you know about querySelector and querySelectorAll. I learned this scraping from you and it was very helpful, but I didn't know there may be a method for extracting using css like HTMLDoc.querySelectorAll(".a-price"). Videos about this would be very helpful. This is with the method I'm using without being able to click in a browser. The variable also has to be dim as an object and not IHTMLElementCollection

[@WiseOwlTutorials]

Hi Tobie! We have a video which covers CSS selectors (as well as XPath and other techniques) which you might find useful kzbin.info/www/bejne/oqOadHmQeq5lj6M It uses Selenium and Google Chrome for the examples but the CSS selector part will still be relevant for the querySelectorAll method. I hope it helps!

[@sanjaykanojia3336]

Hello sir, how to choose value in drop down list in web scraping..

[@DM-py7pj]

depends if it is a option element within parent select. If that is the case you can use .SelectedIndex = n (where n is desired option index) on the Select element, or use ie.document.querySelector("option[value="optionattributevaluegoeshere").Selected = True. There are more methods but these are the most common.

[@morganclendennin1283]

Andrew, this is awesome!! This has been so help in terms of learning more about the diferent ways that data can be extracted when there's links you have to click on to get there. I'm having an issue with a website called www.marketingscents.com where I need to log in to the website to be to access the information that I need. I am able to come up with code that allows me to click on the login button but when i get to the login screen, the username and password fields are both input fields that I can't seem to be able to loop through. I can loop through the div tag because they have the same div tag class name but it won't give me any useful information for being able to find the correct input box and enter any information into it. I've tried using techniques from both this video and the VBA Introduction 47.1 video as well with no luck...

[@WiseOwlTutorials]

Hi Morgan, you could try a slightly different approach for this: Sub TestLogin() Dim ie As SHDocVw.InternetExplorerMedium Dim doc As MSHTML.HTMLDocument Dim LoginForm As MSHTML.HTMLFormElement Dim UserNameInput As MSHTML.HTMLInputElement Dim PasswordInput As MSHTML.HTMLInputElement Set ie = New SHDocVw.InternetExplorer ie.Visible = True ie.navigate "www.marketingscents.com/index?page=login-new" Do While ie.readyState READYSTATE_COMPLETE And ie.Busy Loop Set doc = ie.document Set LoginForm = doc.getElementById("js-login-form") Set UserNameInput = LoginForm.getElementsByClassName("form-control input-lg")(0) Set PasswordInput = LoginForm.getElementsByClassName("form-control input-lg")(1) UserNameInput.Value = "my user name" PasswordInput.Value = "my password" LoginForm.submit End Sub I don't have an account to test the next stage but this code successfully enters a user name and password and submits the form. For me this returns an incorrect login details page but with valid credentials should take you to the next stage. Hope it helps!

[@gauravjain5159]

Hello Andrew, If you dont mind I have another question. Are there some website which are protected from being scrapped via VBA ? like table mentioned under following site, I cant able to extract the table from below site, www.bseindia.com/markets/Equity/EQReports/mktwatchR.html?filter=Gainer*group$all$A

[@WiseOwlTutorials]

I think in this case the website is taking time to load the table and your code which references it is running before the table is ready. This is something that's easier to control with SeleniumBasic using Google Chrome as you can set a wait time. I've linked to the Selenium videos in my other answer, I hope it helps!

[@AbhisheakSaraswat]

Upload more video on Web scrapping

[@81flame]

If you make this example according to Chrome, Iwill be very happy. Because Internet Explorer doesn't support for website. Please help me.😇

[@WiseOwlTutorials]

Hi! We do have a bunch of Chrome VBA videos in this playlist starting at part 57.1 kzbin.info/aero/PLNIs-AWhQzcl3xKvF8sVL4sWRWICj_clM I hope it helps!

[@81flame]

@@WiseOwlTutorials I try to change to Chrome this website, but Ididn't do ☹

[@MrQuinteiro]

Hi Big Boss, it's possible make this using xmlhttp request?

[@WiseOwlTutorials]

Hi! Here's our video on xmlhttp requests kzbin.info/www/bejne/iGHboXeCr7uWq9k and here's our full playlist of web-scraping videos kzbin.info/aero/PLNIs-AWhQzcl3xKvF8sVL4sWRWICj_clM I hope it helps and thanks for watching!

[@MrQuinteiro]

@@WiseOwlTutorials hi Sir!! It's possible to make scraping with xml Http request in to pages 100% powered with JavaScript?

[@WiseOwlTutorials]

@@MrQuinteiro Hi Joilson! I don't know if it's impossible but it would certainly be very difficult! The XML HTTP request would return the client-side javascript code but it wouldn't have any way to execute that code. I don't know how you could execute the code without using a web browser but I'm no expert on this! If I needed to do this I'd use a technique involving a web browser- either Internet Explorer with Microsoft's built-in Internet Controls library, or even better, Google Chrome using the SeleniumBasic library. I hope that helps!

[@daitn]

Thank you very much. Can i ask you some questions, please? How to double click on IE.document.getelementbyID("something"). Thank for your attention. Hope you have a nice day.

[@WiseOwlTutorials]

Hi, I'm not sure but this might help stackoverflow.com/questions/49949177/double-click-button-internet-explorer-excel-vba

[@KhalilYasser]

Very awesome tutorial. You are really a legend. I think using selenium would be easier for scraping tables. If you have time in the future, please post some tutorials for how to use selenium to scrape the tables. Best Regards

[@kyorajesh1]

​@@WiseOwlTutorials you are great thanks for your videos.waiting for selenium with VBA.

[@gauravjain5159]

Hello Andrew, Thanks for your tutorials. They are supper helpful. I have 1 simple issues where I am not able to proceed further, 1. Login into www.utimf.com/portal/login. UserID and Password goes to the site using XML VBA or IE Object, however text is not visible until I perform any keystroke. Once its visible and if i manually click the Login Button then webpage does not recognize the text and if I manually delete any letter and type the same via keyboard, then it works fine.

[@WiseOwlTutorials]

Hi Gaurav! I can't test this as I don't have an account but I wonder if you might have more success using Google Chrome rather than IE. We have some videos on using Chrome in this playlist starting at part 57.1 kzbin.info/aero/PLNIs-AWhQzcl3xKvF8sVL4sWRWICj_clM I hope that helps!

[@balajithite123]

Andrew, this is awesome!! But I have a one issue. I want to extract all tables under links at left hand side in excel worksheet from the website www.iplt20.com/stats/2019 but i am not able to extract it. Please help me in this.

[@DM-py7pj]

Set linksElements = ie.document.getElementsByClassName("side-menu-child-list")(0).getElementsByTagName("a") then loop that and extract the href attributes and store them in an array. Loop that array and .Navigate to current iteration value (href), page load wait on each page, extract table if present.

[@rolsondeleferme6825]

Greetings and thank you for your wonderful tutorials. U really helped the majority of us I think. I face the following problem in my code. In the given url: "us.soccerway.com/", there are some tables that are already expanded and some which are not. In the latter, there is a nested "href" reference, with the respective url, which redirects to the championship table. Though this is programmatically false, since I am seeking for today's game of this championship, and not a new page. how could I enable the expanding process although there is no "onclick" attribute to initiate? Thank you

[@WiseOwlTutorials]

Hi Rolson, I'm sorry but I don't have an immediate answer for this one. I'm going to have to put this on my list to look at when I have some more free time!

[@NareshKumar-qc8zc]

Hi , That was beautiful explanation and I am struggling to code for the below scenario.could you please help me on this? I want to trigger onchange event on dropdown from macro? Website : www.nseindia.com/option-chain#optionchain_equity ex : If we select banknifty on View Options Contracts for: that triggers onchange. how can we achieve this programatically? Is there any video that you already placed related to this concept then Could you help me in finding that video?

[@kabounga75]

really it's amazing thank you for your efforts unfortunately i couldn't apply what i have seen in my project i want to get information from www.cairo-airport.com/en-us/Flights/Flight-Information i need to select tomorrow departure and arrival but i couldn't i will be highly appreciated if you help for this

[@WiseOwlTutorials]

Hi Ashraf, you might find it easier to use the Selenium Basic library to work with Google Chrome to do this. I recently started a series which explains how to use Selenium and you can see the first video here kzbin.info/www/bejne/fKDbiJasj9-MjM0 Here's a basic sample procedure which uses Google Chrome to get the flight information you need. I hope it helps! Private cd As Selenium.ChromeDriver Sub OpenChromeFullScreen() Dim FlightTable As Selenium.TableElement Set cd = New Selenium.ChromeDriver cd.Start cd.Get "www.cairo-airport.com/en-us/Flights/Flight-Information" 'click the link to see tomorrow's flights cd.FindElementByCss("#to > a").Click 'wait for the specified number of milliseconds for the table to refresh cd.Wait 500 'get the contents of the table Set FlightTable = cd.FindElementByCss(".table.table-striped.fl-table").AsTable 'copy the table contents to a new worksheet FlightTable.ToExcel ThisWorkbook.Worksheets.Add.Range("A1") End Sub

[@kabounga75]

@@WiseOwlTutorials thank you for your reply i wiil do according to yiur advice

[@KhalilYasser]

Mr. Andrew: Do you mind posting the code in the video so as to save some time for others? Here's a link : pastebin.com/naazbqBA And if you mind, please delete this reply Regards

[@KhalilYasser]

@@WiseOwlTutorials Thanks a lot for your reply. You can review the code to correct if there are any errors.