Excel VBA Introduction Part 47.1 - Browsing to Websites and Scraping a Web Page

Share
Embed
  • Published on Nov 13, 2016
  • By Andrew Gould
    Download files here www.wiseowl.co.uk/videos/excelvba/scraping-web-pages.htm
    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-gould/introduction-to-excel-vba/paperback/product-23301058.html
    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

Comments • 281

  • Antonio Spatuzzi
    Antonio Spatuzzi 18 days ago +1

    Dear WiseOwl, this video is just amazing. This is what i was looking for and tought me something completely new. I've tried to apply this technique to scrape tables from the web to excel and wiht wikipedia and other pages it works, but with this page (www.whoscored.com/Regions/108/Tournaments/5/Italy-Serie-A) it does not work at all. It can't find the tables I'm interested in and it does not show me the innertext of the table I'm interested in. Do you have any explaination why is that?
    I wish you a onderful evening.

    • WiseOwlTutorials
      WiseOwlTutorials  3 days ago

      +Antonio Spatuzzi That's great to hear! Well done on figuring it out and thanks for coming back to let us know! I'll try to take a look at the example you've pasted at some point this week and get back to you if I find anything. Thanks Antonio!

    • Antonio Spatuzzi
      Antonio Spatuzzi 3 days ago +1

      +WiseOwlTutorials my dear, thanks to your help I achieved my goal and I scrape what I needed to scrape. I didn't find any solution until now for Mac but for now it is ok. I moved to the scraping of the next website I need and I used the same code but of course changing the HTML classes and ids. I have only one problem that I can't figure out: most of time the code doesn't go till the end and Error run-91.
      I write you below the code, ptobably you can explain me why. I think the code is correct because a few times work but then next time I run it, doesn't work anymore.
      My aim is to take scrape the table "under-over" for all 3 tabs (Totale-Casa-Fuori). I wish you a wonderful weekend.
      Antonio
      Sub GetOptionUnderOver()
      Dim IE As New SHDocVw.InternetExplorer
      Dim HTMLDoc As MSHTML.HTMLDocument
      Dim TableOptionsLinks As MSHTML.IHTMLElementCollection
      Dim TableOptions As MSHTML.IHTMLElement
      Dim TableOptionLink As MSHTML.IHTMLElement
      IE.Visible = True
      IE.navigate "www.diretta.it/calcio/francia/ligue-1/classifiche/"

      Do While IE.readyState READYSTATE_COMPLETE Or IE.Busy
      Loop

      Set HTMLDoc = IE.document
      Set TableOptions = HTMLDoc.getElementById("tabitem-over_under")
      Set TableOptionsLinks = TableOptions.getElementsByTagName("a")

      For Each TableOptionLink In TableOptionsLinks
      Debug.Print TableOptionLink.href, TableOptionLink.innerText

      If TableOptionLink.href = "www.diretta.it/calcio/francia/ligue-1/classifiche/#over_under" And TableOptionLink.innerText = "Over/Under" Then
      TableOptionLink.Click
      Do While IE.readyState READYSTATE_COMPLETE Or IE.Busy
      Loop
      GetTableTotale HTMLDoc.getElementById("table-type-6-2.5")
      End If
      Next TableOptionLink
      End Sub
      Sub GetTableTotale(HTMLTable As MSHTML.IHTMLElement)

      Dim TableSection As MSHTML.IHTMLElement
      Dim TableRow As MSHTML.IHTMLElement
      Dim TableCell As MSHTML.IHTMLElement
      Dim TableRows As MSHTML.IHTMLElementCollection
      Dim RowNum As Long, ColNum As Integer

      Worksheets("Foglio2").Activate
      RowNum = 1

      For Each TableSection In HTMLTable.Children
      If LCase(TableSection.tagName) "tfoot" Then
      If LCase(TableSection.tagName) = "thead" Then
      Set TableRows = TableSection.getElementsByClassName("main")
      ElseIf LCase(TableSection.tagName) = "tbody" Then
      Set TableRows = TableSection.Children
      End If
      For Each TableRow In TableRows
      ColNum = 1
      For Each TableCell In TableRow.Children
      Cells(RowNum, ColNum).Value = TableCell.innerText
      ColNum = ColNum + 1
      Next TableCell
      RowNum = RowNum + 1
      Next TableRow
      End If
      Next TableSection
      End Sub

    • WiseOwlTutorials
      WiseOwlTutorials  15 days ago +1

      ​+Antonio Spatuzzi You're very welcome Antonio, I hope it was helpful! I think it should be possible to get what you need with a few changes to what I've done. I'll post a link in the video description today so that you can download the code I've written and then you can modify it as needed. I'm afraid I don't have any experience with how to this in a Mac environment so I can't really help with that part. Best of luck and let me know how you get on!

    • Antonio Spatuzzi
      Antonio Spatuzzi 15 days ago +1

      +WiseOwlTutorials I'm speechless. The video that you just did is just amazing. It is really an honor to me that you took so much care of my problem.
      I'm really sorry I didn't give many informations about what I wanted to do, so here it is:
      my aim is to scrape the table "wilde" that you can find in standings and the table "wilde" that you find in form.
      I'm trying to do that right now. My concern is now that I have to click on 3 times on links since I have to push first on wilde, then on form and then one last time on wilde. I wonder if I can do everything in one procedure or not and finally I would like to do everything not using Internet explorer since I'd like to use this code on windows and on Mac. I'll try my best and if I'm gonna stumble in any problem I won't hesitate to ask you.
      Thank you very much for your wondeful work.

    • WiseOwlTutorials
      WiseOwlTutorials  17 days ago +2

      Just in case it helps, I've made a video on how to get data from this site ru-clip.net/video/eq2ATGKN_jk/video.html
      Let me know if you need any more help!

  • THEGAMINGHELP101
    THEGAMINGHELP101 Month ago

    Do you have a video showing how to find an already open instance of internet explorer based on part of the URL instead of opening a new Instance of ie?

    • THEGAMINGHELP101
      THEGAMINGHELP101 27 days ago

      WiseOwlTutorials I figured out how to get a good print out of my data. However it is printing out all the text from the page as one tag. Is there a method to print each new word of a string in a new cell?
      Also do you have a video for clicking drop down menus to eventually click on a download link?

    • THEGAMINGHELP101
      THEGAMINGHELP101 29 days ago

      +WiseOwlTutorials Ok. Also another question. I am trying to implement something like the following. Navigate to an internal database entry a number in a search box and click a button to search (note when clicking button the URL is still the same). Doing the above works no problem using the IE method but using the faster method causes an error after clicking button, is this one of the cases it is not possible to use the fast method or is there a work around?
      Also another question.
      The data I am trying to get is within a table however here is the problem.
      HTML example,
      Table
      Table
      Table
      Table
      Tr
      Td(data I want)
      So as you can tell using the method in the video causes my data to get printed a million times and in odd ways. Do you have any suggestions?

    • WiseOwlTutorials
      WiseOwlTutorials  Month ago +1

      Hi, sorry no we don't have a video covering that technique. As usual, the guys at stackoverflow have us covered! stackoverflow.com/questions/21837844/vba-code-to-interact-with-specific-ie-window-that-is-already-open

  • CyberNaut WA
    CyberNaut WA Month ago

    Followed example, but found XML not work :( Why is it so??

    • WiseOwlTutorials
      WiseOwlTutorials  Month ago

      It could be that some URLs have changed since the video was recorded. For example, Wise Owl now uses HTTPS rather then HTTP.

  • Paul Sparrow
    Paul Sparrow Month ago

    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
      WiseOwlTutorials  25 days ago

      +Paul Sparrow That sounds like an impressive system, congrats! We don't have an official 1-2-1 service I'm afraid, our main work is delivering classroom-style training courses and I maintain this channel mostly in my spare time (I've had some time off work which is why there's been so much activity on the channel this week!). You're more than welcome to post questions here and I'll do my best to answer. I can also highly recommend StackOverflow.com for this sort of thing - they have a virtual army ready to answer any question you can think of!
      I hope that helps and thanks so much for your comments and for watching!

    • Paul Sparrow
      Paul Sparrow 25 days ago +1

      +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?

    • WiseOwlTutorials
      WiseOwlTutorials  Month ago

      Thanks Paul! I'll admit that it took a while to research this one and it's still a technique that I'm not completely confident with. i hope that at least some of it was useful, thanks for watching!

  • MrChucky1993
    MrChucky1993 Month ago

    nice video :D but i have a question: why do you use a Object and not a Array ? is that because the object have more information to carry and a array can´t handle that ?

    • WiseOwlTutorials
      WiseOwlTutorials  Month ago

      Thanks! Sorry but I'm not quite sure which part of the video you're asking about (apologies but it's been a while since I recorded this one!)

  • Khushi Gaur
    Khushi Gaur 2 months ago

    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...

  • Daniel Chang
    Daniel Chang 2 months ago

    i got an error -vba runtime error 214702891 access is denied

  • Scott Jarousky
    Scott Jarousky 2 months ago

    Good Video! I was thinking of loading multiple rows of data from a spreadsheet into an array, then looping through the array to rapidly enter data into a web app (say entering invoices or something repetitive). Once a full record is submitted to the website it may take a variable amount of time for the site to save before I move on to the next record to input. Should I do a "Do While ReadyState..." loop after a record is submitted, not worry about it, or is there something better than a "Do While ReadyState…" loop?

    • WiseOwlTutorials
      WiseOwlTutorials  29 days ago

      Thanks Scott! The short answer is that I'm not entirely sure! You might consider testing the Busy property as well as the ReadyState as suggested here stackoverflow.com/questions/23631621/excel-wait-for-webpage-to-open A similar suggestion is here www.vbaexpress.com/forum/showthread.php?55348-Best-Way-to-Stop-Macro-until-Webpage-has-Loaded

  • DestinyHnn
    DestinyHnn 2 months ago

    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

  • Alejandro Cerón
    Alejandro Cerón 2 months ago

    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

  • NICKSON KASONGO
    NICKSON KASONGO 2 months ago

    Amazing video.. can you please explain how to upload a file to any website using excel vba.

  • Missing No
    Missing No 2 months ago

    what if the website has divs and not tables?

  • spiderx01
    spiderx01 3 months ago

    I have somebody problem with Run-time error '-2147024891 (80070005)': Access is denied . Use MSXML2.ServerXMLHTTP60 instead of MSXML2.XMLHTTP60. The problem happens probably, because MSXML2.XMLHTTP60 is for website on the local network(intranet). So you can try add required site to trusted sites or intranet sites or use MSXML2.ServerXMLHTTP60. Trusted sites you can change in the internet options -> security -> choose local intranet or trusted sites -> click on sites -> advanced -> add your site.

  • Chris Schmid
    Chris Schmid 3 months ago

    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

  • john bensen
    john bensen 4 months ago

    Amazing !
    VBA is such a (with due to respect) a fucking language .
    But you made it very easy to work with .
    Thanks a lot .

  • Chris Raisin
    Chris Raisin 4 months ago

    You are a great presenter! One of the best I have viewed on the Internet....keep up this great work.

  • Govindpreet Singh
    Govindpreet Singh 4 months ago

    Dear i sincerely like to appreciate your teaching skills, each video of your is so informative and you have made it simple to understand.

  • ABHISHEK PATEL
    ABHISHEK PATEL 5 months ago

    I'm trying to click. A button on a web site but am unable to do so
    The HTML code is

  • Victhor Trautmann
    Victhor Trautmann 5 months ago

    If you're having trouble with the first loop, try InternetExplorerMedium instead of InternetExplorer. EX: Dim IE as New SHDocVw.InternetExplorerMedium

  • Gestão Office
    Gestão Office 6 months ago

    Olá !
    Gostaria de aprender webScraping de maneira profissional usando Excel?
    E o melhor sem necessidade de usar navegador, usando direto o protocolo HTTP?
    Conheça o curso de WebScraping com Excel VBA.
    Veja o vídeo no link abaixo.
    ru-clip.net/video/CpbhAra6xrE/video.html
    Abraços

  • Tom-Inge Nilsen
    Tom-Inge Nilsen 6 months ago +1

    By far the best video tutorial regarding web scraping with VBA!

  • Jim Smyth
    Jim Smyth 7 months ago

    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?

  • Wiel h01
    Wiel h01 7 months ago

    At the last if statement the only way it works when wou put www. in front of x-rates

  • Al-Amin Mollah
    Al-Amin Mollah 7 months ago

    Dear Mr. Andrew Gould,
    Your excel vedio are the best I have ever seen excel vedio in youtube.
    Could you please help me to solve below problems:
    Suppose, I searched giving the word "excel vba" in wiseowl page after navigating wiseowl.co.uk using excel vba code & after that I need Web page info from the loaded page if the new page loaded in 1) new tab 2) current tab. Please somebody give me solution.

  • ggsoerentsvendsen
    ggsoerentsvendsen 7 months ago

    I'm getting a bug on the "XMLPage.send" command - (doing the exact same exercise as in the video). when hovering above the yellow marked area I get this message -> "Object variable or With block variable not set". Can't figure out what is wrong in it. Anyone has an idea?

  • Dr. Anurag Singhal
    Dr. Anurag Singhal 7 months ago +2

    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.

  • Trupti Bobade
    Trupti Bobade 7 months ago

    I am getting the Run time error as "Object Required". Can you please help me with it.

  • kapil sharma
    kapil sharma 7 months ago

    awesome

  • sadfj ltie3
    sadfj ltie3 8 months ago +1

    Just an amazing series, crystal-clear explanation, lots of hands-on experience, and perfect pacing--bravo, and thanks for this!

  • Lekshey
    Lekshey 8 months ago +2

    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 !!!

  • Ernestas Raudys
    Ernestas Raudys 8 months ago

    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

  • Michal Elston
    Michal Elston 11 months ago

    How do I choose what table I want to get the information from. I currently have a site with 14 tables.

  • Rich K
    Rich K 11 months ago

    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?

  • JoergB
    JoergB 11 months ago

    Very competent explained. For me as a german with medium english know how it is well understandable! THANKS!

  • leela g
    leela g 11 months ago

    Hi Andrew,
    I need to enter username in a textbox, i used "Set username = HTMLDoc.getElementById("id1005")" line of code and then trying to give a value "username.Value = "abc"", when it is executing this line getting run time error 91.
    used the code same as you wrote in the video.
    please help me.

  • Amit Sharma
    Amit Sharma 11 months ago

    Watched the entire video..Seriously ..it's too good... only one help needed to let me know the user input method as mentioned in comments below..Pls.

  • Amit Sharma
    Amit Sharma 11 months ago

    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

  • Abhayjit Thind
    Abhayjit Thind 11 months ago

    Great Tutorial. Proper organisation and perfect execution.

  • Rey Reyes
    Rey Reyes 11 months ago

    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!

  • Neeta gupta
    Neeta gupta Year ago

    Hii sir, is there any way to find the latest uploaded file by date using vba

  • Artur Bensel
    Artur Bensel Year ago

    great video! thanks

  • Fred Durst
    Fred Durst Year ago

    Is there any way to click a Button while you choose the XML method? While using IE the click event works fine for me, when i try it like
    HTMLDoc.body.innerHTML = XMLPage.responseText
    HTMLDoc.getElementById("test").Click
    i got only a Runtime Error.

  • Alex Casha
    Alex Casha Year ago

    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?

  • Harold Kumar
    Harold Kumar Year ago

    Great Video but I'm getting conflicting results for the 2 different "find the amount of tables" methods for the following webpage:
    financials.morningstar.com/valuation/price-ratio.html?t=AAPL
    With IE I get 3 tables in the Immediate section, but with XML I get 0 tables in the Immediate section. Can anyone confirm or deny this? I checked the code a million times and I know the IE version is correct. Thanks to who ever can solve this first.

  • Crystal Yin
    Crystal Yin Year ago

    Looks like the x-rate.com website no longer lets people send xml references. If youre trying to browse to the xrate website via xml, it will give you an "access denied" error message. However, if you try it with any other website, e.g. finance.yahoo.com/quote/%5EGSPC/ the method will still work!

  • Tahor Sui Juris
    Tahor Sui Juris Year ago

    Excellent! How would one input to an incremental auto fill, autocomplete drop-down that does not have an index reference?

  • Emilio De Jesús Grullón

    Thank you so much!

  • Tyagi Munde
    Tyagi Munde Year ago +1

    You are most talented in the world

  • Dara Meybodi
    Dara Meybodi Year ago

    Hi, I was wondering what the difference between HTMLDocument and iHTMLDocument is. Or more generally, what's the difference between HTML and iHTML object?

  • Jeshan :D
    Jeshan :D Year ago

    Mad respect for this bro!

  • J Nada
    J Nada Year ago +1

    Amazing content, can't thank you enough

  • Arthur Manlutac
    Arthur Manlutac Year ago

    Hi Andrew, Thank you for your videos.
    Could you please help me with my codes below? I only shared part of my code where I need help with. I also changed the URL.
    Sub CreateIncidentTicket()
    Dim IE as InternetExplorerMedium 'I use this due to our IE settings.
    Dim ObjCollection as Object
    Set IE = New InternetExplorerMedium
    'PROBLEM 1: I want to automate the ticket creation and the details are in excel sheet. everytime I do, a NEW instance of
    'InternetExplorerMedium is created. Is there a way i can loop and scan my desktop and look for URL that contains part of '"itsm-webapp.mysite.com/arsys/forms/itsm-arapp.mysite.com"
    'and click the button as below?
    IE.Navigate "itsm-webapp.mysite.com/arsys/forms/itsm-arapp.mysite.com" 'i changed the company name to 'mysite'
    IE.Visible = True
    Do While IE.ReadyState READYSTATE_COMPLETE
    Loop
    Application.Wait (Now + TimeValue ("00:00:10"))
    ' PROBLEM 2: the browser is already in ready state but the page is loading something internally that's why i had to out 10seconds 'for it to load. so i need to loop until a specific text on the body appears or if a specific button appears.
    Set CreateBtn = IE.document.getElementById("WIN_1_301429000")
    CreateBtn.Click
    End Sub

  • GreenScreen_VideoEffects

    Great Video. Learnt lot of new things. Thanks

  • luis torres cereño

    Gracias amigo

  • Muhammad Ahsan Naeem

    Hello, Thanks for the tutorial.
    I am getting error at:
    XMLPage.send
    I am using the file from the link of description. In Module 4, Sub BrowseToExchangeRatesWithQueryStringAndXML() at line:
    XMLPage.send
    It gives following error:
    Access Denied Error.

    Please Help!

    • MrHorzel
      MrHorzel 11 months ago

      Yes, Simply change Dim XMLPage As New MSXML2.XMLHTTP60 to Dim XMLPage As New MSXML2.ServerXMLHTTP60 (in module6)

    • Germino Neves
      Germino Neves Year ago

      Hi, See if this can help you: social.msdn.microsoft.com/Forums/en-US/1abda1ce-e23c-4d0e-bccd-a323aa7f2ea5/access-is-denied-while-using-microsoftxmlhttp-to-get-a-url-link-in-vbscript-help?forum=xmlandnetfx

  • John Louie Gonzales
    John Louie Gonzales Year ago +1

    When i run the BrowseToExchangeRatesWithQueryStringAndXML(), for some reasons it has and error on "XMLPage.send" and says "Access Denied".

    • Jawali M
      Jawali M 7 months ago

      Hi John .. i have same VBA error on "XMLPage.send" saying "Access Denied" .. any luck trying to solve it ?

    • Nicholas Potts
      Nicholas Potts Year ago +1

      Pretty sure this is an IE issue, change the website to '' from '' and this should solve the issue.
      I imagine changing security settings in IE will resolve this also.

  • Rohith D
    Rohith D Year ago +1

    While using XML Object, i get access denied Error. I tried using the ServerXMLHTTP method, but couldnt work it out. Can you please help me here?
    Thanks for all the good work that u have done in providing us this valuable knowledge.

    • parky55
      parky55 3 months ago

      Hi guys
      I just encountered the same problem. However, this issue can easily be fixed by adding a "s" in the URL: httpS://
      Hope that helps.
      Cheers

    • Jawali M
      Jawali M 7 months ago +1

      .. i have same error here .. any luck trying to solve it ?
      "While using vba XML Object to scrap a page, i get access denied Error.".

  • Brandan Skoko
    Brandan Skoko Year ago

    Thanks but what what's if there is no href in the click

  • Master of None
    Master of None Year ago

    I have no more words..... how fast and best way to explain

  • Serge Kashlik
    Serge Kashlik Year ago

    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.

  • BUD VIN
    BUD VIN Year ago

    I have a trouble.I want get a data in website but when I shut down IE, it automatically reopens.Help me,please!
    ...
    IE.Quit
    Set IE = nothing

  • Do you know about This?

    Hi thanks for amazing videos could you please help me to how to click on
    Radio button using vba in internet explorer, please check below link
    for radio button.
    www.w3schools.com/html/tryit.asp?filename=tryhtml_form_radio

    • Do you know about This?
      Do you know about This? Year ago

      Thanks.

    • WiseOwlTutorials
      WiseOwlTutorials  Year ago

      You're welcome!
      The exact code you'll need to do this will depend on the web page you're using of course, but here are a couple of links with examples:
      stackoverflow.com/questions/15559408/using-vba-to-operate-radio-buttons-on-a-web-page
      www.mrexcel.com/forum/excel-questions/574836-select-radio-buttons-ie.html
      I hope it helps!

  • Do you know about This?

    Best teacher of VBA, thanks for amazing tutorial series.

  • Vinit Shah
    Vinit Shah Year ago

    Your videos have been really helpful so far but I do have one question.
    Would you please explain how to load xml pages for the websites that requires user to log in with User ID and Password?!
    Any help would be much appreciated!
    Thanks in advance!!

  • Dennis Yeadon
    Dennis Yeadon Year ago

    Hi Andrew,
    How do we adopt the code to Edge?
    Thank you

  • Joe Schaefer
    Joe Schaefer Year ago

    Thanks, i found your video very useful.I've had some success and failure on www.zillow.com/homedetails/5907-Medina-Dr-Killeen-TX-76542/79904797_zpid/?fullpage=trueFor some reason on this page, i find that there are some classes i cannot retrieve such as this one:$120,114It seems that a very simple method to get the inner text in this would be this: Set HTMLSections = HTMLDoc.getElementsByClassName("zestimate-value")
    However, HTMLSections is empty when i execute this. Can you have a look at this and give me any pointers?

  • Essam Adel
    Essam Adel Year ago

    Hi sir what i do if when right click on website but can't see view source?

  • Karim
    Karim Year ago

    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

  • Breo Pardo
    Breo Pardo Year ago

    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.

  • Adrian Salabanzi
    Adrian Salabanzi Year ago

    Thank You so much!

  • Harris Elliott
    Harris Elliott Year ago

    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?

  • Denis Curvelo
    Denis Curvelo Year ago

    I tried this 18:45 using "www.google.com", but was necessary to add another loop before the click button

  • Denis Curvelo
    Denis Curvelo Year ago

    Mr Andrew, you did a nice work there. Your videos have an invaluable source of information.

  • Adom360
    Adom360 Year ago

    Your way of loading website can sometimes stuck, I recommend to use for it:
    "Do While IE1.Busy
    Application.Wait DateAdd("s", 1, Now)
    Loop"
    Also you should add in the end IE.Quit.

  • Mauro Castagnera
    Mauro Castagnera Year ago

    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!

  • Mauro Castagnera
    Mauro Castagnera Year ago +8

    Andrew Gould = Genius!
    No other words are needed!

  • Guz Man
    Guz Man Year ago

    Great video!
    Maybe you can help me with this.
    I want to select items (options) in a HTML tag by name. Any idea how to do it?
    I'm using the HTML Object Library & Microsoft Internet Controls.
    Thanks a lot!

  • Andoni
    Andoni Year ago +5

    dude you are awesome

  • Kishor Palli
    Kishor Palli Year ago +2

    Hi Andrew,
    I want to fetch detail from web page, but the thing is I can't directly navigate to that web page, when I enter detail in one web page and submit it opens a new web page and there I would have required detail. How can I achieve this. In short how to give newly opened tab web page as the reference to the html document variable. Thanks.

    • Khushi Gaur
      Khushi Gaur 3 months ago

      Andrew, get video, but as said i m also facing similar issue .... Anything for this be good...

    • Khushi Gaur
      Khushi Gaur 3 months ago

      Grt vdo, but i have the same query as how to work on this type of web page .....let me know if anyone got through this...thanks

  • Chasing_the_Dragon

    Started learning VBA yesterday, so Im really poor. But you did an awesome job!!! Thank you!!

    • WiseOwlTutorials
      WiseOwlTutorials  Year ago

      Thanks! Hope you're enjoying learning and thanks for watching!

  • Alex Miller
    Alex Miller Year ago

    Can you please make a video going more in depth on how to search different websites and pull in the information to excel? I'm working on a macro that has to pull information from a website with several tables and none of them have class names.

    • WiseOwlTutorials
      WiseOwlTutorials  Year ago

      Hi Alex, I'd love to but there's barely time to create all the other videos I want to!

  • Jay Williams
    Jay Williams Year ago

    Hi I was wondering do you (or anybody ) know how to scrape from a webpage that displays more of the web page each time you scroll to the bottom? At the moment I can only scrape from the page that is displayed before it is scrolled , I hope I have made that clear. Thanks in advance and great videos btw

    • Jay Williams
      Jay Williams Year ago

      WiseOwlTutorials hi there thanks for your reply and information. That helped and I have got it to work now. Thanks again :)

    • WiseOwlTutorials
      WiseOwlTutorials  Year ago +1

      Hi Jay, it depends on exactly how the web page generates its dynamic content (javascript is a likely culprit). Here's one possible solution stackoverflow.com/questions/42577014/wait-for-window-to-reload-when-scrolling-web-page-in-vba
      I hope it helps!

  • Zhichao Peng
    Zhichao Peng Year ago

    U r a life saver! This is the best VBA tutorial that ever had !

  • Harold McBroom
    Harold McBroom Year ago

    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.

  • Tahor Sui Juris
    Tahor Sui Juris Year ago

    how to press the button without using IE MSHTML?
    Below is the VBA code in XMLHTTP.
    Dim ele As Object
    Set ele = Doc.getElementsByName("action")(0)
    Debug.Print ele.Name
    ele.Click

  • Nathan Green
    Nathan Green Year ago

    This is incredibly informative and very well presented. Thank you Andrew Gould for such a great video!

  • Nandyz TV
    Nandyz TV Year ago

    I would like to know whether I can scrap a webpage having dynamic content using this ? For example in url ==> www.nseindia.com/live_market/dynaContent/live_watch/equities_stock_watch.htm. In this case if I want to retrieve the Open,High,Low,Close values for each stock how can I achieve that. When I do a Inspect element I can find the value in the Inspect Element section but when I open the source code the value is not available. Suggestion or sample code for above scenario will be very much helpful.

    • WiseOwlTutorials
      WiseOwlTutorials  Year ago

      The code shown in the video and included in the sample file that you can download will achieve this. If you need a description of how this works, it's all in the video!

      Sub BrowseToNSEIndia()
      Dim IE As New SHDocVw.InternetExplorer
      Dim HTMLDoc As MSHTML.HTMLDocument

      IE.Visible = True
      IE.navigate "www.nseindia.com/live_market/dynaContent/live_watch/equities_stock_watch.htm"

      Do While IE.ReadyState READYSTATE_COMPLETE
      Loop

      Set HTMLDoc = IE.Document

      ProcessHTMLPage HTMLDoc

      End Sub
      Sub ProcessHTMLPage(HTMLPage As MSHTML.HTMLDocument)
      Dim HTMLTable As MSHTML.IHTMLElement
      Dim HTMLTables As MSHTML.IHTMLElementCollection
      Dim HTMLRow As MSHTML.IHTMLElement
      Dim HTMLCell As MSHTML.IHTMLElement
      Dim RowNum As Long, ColNum As Integer

      Set HTMLTables = HTMLPage.getElementsByTagName("table")

      For Each HTMLTable In HTMLTables

      Worksheets.Add
      Range("A1").Value = HTMLTable.className
      Range("B1").Value = Now

      RowNum = 2
      For Each HTMLRow In HTMLTable.getElementsByTagName("tr")
      'Debug.Print vbTab & HTMLRow.innerText

      ColNum = 1
      For Each HTMLCell In HTMLRow.Children
      Cells(RowNum, ColNum) = HTMLCell.innerText
      ColNum = ColNum + 1
      Next HTMLCell

      RowNum = RowNum + 1
      Next HTMLRow

      Next HTMLTable

      End Sub

  • Guz Man
    Guz Man Year ago

    Hi Andrew, great video!!
    Maybe you can help me out with this problem.
    Using Internet Explorer with the HTML Object Library.
    I have to access a website, I have to input the credentials (user name and password). Then, using different tabs I have to open different links. (The URL is not a string, can't use string & string).
    How can I open a new tab, each one with a different subroutine, calling IE only once?
    I call the HTML Object library in a subroutine and in the same one I enter the web credentials. But I want the code to access each TAB to be in a different subroutine.
    Do you have any idea how to do this?
    THANKS! I enjoy your videos a lot :)

    • WiseOwlTutorials
      WiseOwlTutorials  Year ago

      Hi, this page has a few ideas that you can use www.ozgrid.com/forum/showthread.php?t=174692
      I hope that helps!

  • Samadhan P
    Samadhan P Year ago

    I am getting below Error in company environment. Can you please help me to resolve this error. I tried to get all full access and also, downloaded all .dll file but this error didn't get resolved. Whenever I am using .document and I am getting same error.
    Set HTMLDoc = IE.document getting error - Class does not support Automation or does not support expected interface.

    • WiseOwlTutorials
      WiseOwlTutorials  Year ago

      Hi, I'm not sure why you're receiving that error. One quick thing to check is that you've declared the variable with the correct class:
      Dim HTMLDoc As MSHTML.HTMLDocument
      Alternatively, you could try downloading the sample file from the link in the video description to check that this works.

  • Essam Adel
    Essam Adel Year ago

    I want cantact with you my phone no 0096566615090
    For ask you about open website please

  • Matheus Almeida
    Matheus Almeida Year ago

    hey guys, how to retrieve the value of a single cell in this HTML Table? In the examples shown, we loop the whole table to get the value of all cells, but how to target just a specific cell?

    • WiseOwlTutorials
      WiseOwlTutorials  Year ago

      Hi Matheus, how would you uniquely identify a single cell? Does it have a unique name or ID or value?

  • Prakash Rawat
    Prakash Rawat Year ago

    I am having some issue scraping data from websites, the data is inside a Iframe. I will appreciate If you can help me out how we can read Iframe, and it's elements. Thanks in advance...

    • WiseOwlTutorials
      WiseOwlTutorials  Year ago

      Hi Prakash, you could start with these links
      stackoverflow.com/questions/44902558/accessing-object-in--using-vba
      stackoverflow.com/questions/31383280/access--from-vba-correctly
      stackoverflow.com/questions/16699267/vba-ie-automation-read-
      I hope that helps to point you in the right direction!

  • Prakash Rawat
    Prakash Rawat Year ago +4

    You are awesome, I have learned mant things following your videos, they are very very easy to understand,

  • malcolm gregor
    malcolm gregor Year ago

    This is an amazing tutorial for both beginners and advanced users who forgot and want to remember. Great video thanks.

  • YUCHEN LIANG
    YUCHEN LIANG Year ago

    I got type mismatched for HTMLDoc=IE.document

  • shannon mcnaron
    shannon mcnaron Year ago

    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.

    • WiseOwlTutorials
      WiseOwlTutorials  Year ago

      You're very welcome Shannon. I'm happy to hear that you're enjoying it and finding it useful at the same time! Thank you for watching!

  • Goki Kazama
    Goki Kazama Year ago

    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.

  • MrXceller
    MrXceller Year ago

    Andrew Gould, VBA king. Love your tutorial! Thank you! Would you do a tutorial on API, RESTful API, PowerQuery in the future?

    • WiseOwlTutorials
      WiseOwlTutorials  Year ago

      :D thanks! I've used API functions in a couple of videos already and have plans to create at least one video explaining how to use API functions in general. We do have a separate tutorial series on using Power BI tools in Excel, there's a short one introducing the basics of Power Query ru-clip.net/video/9htG32tgEF8/video.html and a longer one going into a little more detail ru-clip.net/video/e56Z1wCoxy4/video.html
      I hope that helps!

  • Indian Mechanic
    Indian Mechanic Year ago

    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.

  • Raven Uch
    Raven Uch Year ago

    Dear Andrew
    It's such a great video and I learned a lot from it. But I have some issue, Could We click on the Message from Webpage? My macro error once the Massage pop up on WEB page.
    Can we write a code to send "KEY ENTER" into Web??