How to Automate Data Scraping using Excel Macros

Share
Embed
  • Published on Aug 6, 2018
  • In this tutorial we would learn the following:
    1.Automated Data Scraping from Web pages
    2.Fetching data from Web pages and writing into excel sheets

Comments • 83

  • Abdul Imran
    Abdul Imran 3 days ago

    i am getting error 438: object does not support this method error... plz help

  • Elizabeth James
    Elizabeth James 3 days ago

    this is super! thank you exactly what I needed to know. Very clear and organized. Good instructional design. Thank you

  • Rahul Jagtap Patil
    Rahul Jagtap Patil 26 days ago

    Hi,
    I have created macro from Web to excel
    It's working fine but
    Date comes wrong in excel.
    Dmy is in Web but excel gets MDY.
    Please help me to resolve this issue

    • Automation Made Easy
      Automation Made Easy  22 days ago

      Your welcome

    • Rahul Jagtap Patil
      Rahul Jagtap Patil 22 days ago

      +Automation Made Easy I used cdate and its resolved.
      Thanks for your help 🙏

    • Automation Made Easy
      Automation Made Easy  23 days ago

      Update your code such that if 01/11/2019 is read as 1/11/2019. I guess the zero for single digit days is creating the problem.

    • Rahul Jagtap Patil
      Rahul Jagtap Patil 26 days ago

      +Automation Made Easy
      Code
      This workbook. Sheets(my data). Range(A1).value = doc. Elementbyid(###). Innertext
      Innertext is 01/11/2019
      Result is 11/01/2019 in A1
      If innertext is 25/04/2019
      Result is correct.
      Some dates recognized as dates, some dates not recognized

    • Automation Made Easy
      Automation Made Easy  26 days ago

      need to see your code to suggest a solution.

  • Kamal Yousuf
    Kamal Yousuf Month ago

    How do I use active selection method. E.g. I want to select a cell to show only the 3rd row and not populate all the rows?

  • dragon ark
    dragon ark Month ago

    Which programming language should I learn for automation?

    • Automation Made Easy
      Automation Made Easy  Month ago

      If you are doing automation using excel macros, VBA is the default language.
      For general automation you may use Java,C#, Python

  • VENKATESH MAHANTHY
    VENKATESH MAHANTHY Month ago +1

    Bro .. love u from this video..... It helped me a lot In automation... N u have explained everything in detail... though m not having ny knowledge in programming language.. I could easily understand😘

    • Automation Made Easy
      Automation Made Easy  Month ago

      you can email me at automationandagile@gmail.com

    • VENKATESH MAHANTHY
      VENKATESH MAHANTHY Month ago

      +Automation Made Easy Bro.. have a site from where I need geo co-ordinates to be fetched... So b4 that an address should be copied from Excel and pasted inside the search box... But here its not happening.. ends up in showing my own address location after pasting the address.. could you plz help me with this..?? I can send the complete details. Please let me know the email id so that I can drop in all the details.
      Thanks in advance

    • Automation Made Easy
      Automation Made Easy  Month ago

      Glad you found it helpful.

  • k K
    k K 2 months ago

    i tried to code but it shows run time error 429 and activeX component cant create object ...i even enabled in references tab..

  • TEJAS NIMBALKAR
    TEJAS NIMBALKAR 2 months ago

    Hello,
    what code to write if a value is not found and move to next value with mentioning "error" in the cell?

  • Raj Kumar
    Raj Kumar 2 months ago

    how to use it in mozila firefox

  • navya chinnari
    navya chinnari 3 months ago +1

    Hi Sir,
    Is it possible to do with same logic in Selenium?

  • B&M Product
    B&M Product 3 months ago

    Hi! I really like the video. it helped me a lot. But I have a problem. With "getelementbyid" I fill the box on the website. But the writing is deleted after 1 second. What is the reason of this? Please help me.

  • Tyler 0312
    Tyler 0312 3 months ago

    • Automation Made Easy
      Automation Made Easy  3 months ago

      Its difficult to comment without looking at your code.

    • Tyler 0312
      Tyler 0312 3 months ago

      +Automation Made Easy It's dynamic which is why I think I'm having issues. Trying to run a findAll("") command and then running a len("") command always returns a 0 items found in python. As for VBA, I set up the script for it to bring me to a specific page, enter something into the search bar, click the search bar and then extract the specific row of data, but when I get to that part the web site coding seems to break down. I can't find any tables to import and the ById or ByClass doesn't find the the specific data set which definitely has an Id="Y_2" and a class name which I can't remember. If this doesn't make any sense, I apologize.

    • Automation Made Easy
      Automation Made Easy  3 months ago

      I havent seen morning star. What issues you are facing? is the website dynamic? or is it difficult to uniquely identify objects?

    • Tyler 0312
      Tyler 0312 3 months ago

      +Automation Made Easy Have you ever scraped data off of morning star? I've tried it two different ways using VBA and Python, but something about the pages coding seems off. I can never seem to get my specific set of information off the page. Any insight would be awesome.

    • Automation Made Easy
      Automation Made Easy  3 months ago

      Your welcome

  • Saurabh Srivastava
    Saurabh Srivastava 3 months ago

    sir nice video. but sir how to extract email capcha in code vba excel plz sir. plzzzzzz

  • Abul Sheikh
    Abul Sheikh 4 months ago

    I watched the video. The explanation seemed very clear. However, when I tried to do it, I could not follow. I did not see IDs "from" and "to" or class "Icon_1_". It is possible that the webpage has been re-coded recently, or I just failed to execute. I did type the VBA code and Excel entries verbatim. Did not work. Any help from the community will be appreciated.

    • Automation Made Easy
      Automation Made Easy  4 months ago

      The site has changed substantially since the time tutorial was created and hence you are not able to see the elements which were there at the time when tutorial was created.

  • Jossi Ossa
    Jossi Ossa 4 months ago +1

    WOW! You are the only one that can explain this correct! Thank you very much sir! You are the Excel Harry Potter :-)

    • Automation Made Easy
      Automation Made Easy  4 months ago

      Glad, you found it helpful. You can email me if you have any specific automation requriement

  • geak hackers
    geak hackers 5 months ago

    I AM TRYING TO TO AUTOMATE DATA ENTRY FROM EXCEL TO GOOGLE FORM
    IT IS GIVING ME AN ERROR
    RUN TIME ERROR '-2147467259(80004005)'
    AUTOMATION ERROR
    UNSPECIFIED ERROR
    Sub data()

    Dim IE As Object
    Dim doc As HTMLDocument
    Set IE = CreateObject("internetexplorer.application")
    IE.Visible = True


    IE.navigate "docs.google.com/forms/d/e/1FAIpQLScnS2d6PCPSmUyz1_yXRRkZw4RYmobsHb-g-k73lannZOzzcA/formResponse " 'web navigation


    Set doc = IE.document


    doc.getElementsByName("entry.2005620554")(0).Value = Workbooks("book1").Sheets("macro1").Range("B2" )

    End Sub

  • GS Shyam
    GS Shyam 6 months ago +1

    How to overcome captcha while entering user name and password

  • Social Service Coordination Council

    If I download Specific word like (student name) then //What`s the Code// Below my IE.Browser code is

    While Your code is
    strVal = doc.getElementsByClassName("uccResultAmount").Item(0).innerText
    ThisWorkbook.Sheets("Data").Range("d" & rowNo).Value = strVal

    • Social Service Coordination Council
      Social Service Coordination Council 6 months ago

      +Automation Made Easy Not showing ‍any error, Still my project is not working. what can i do.

    • Automation Made Easy
      Automation Made Easy  6 months ago

      Try this doc.getElementById("studname").OuterText
      But I feel innertext should also work. What error were you getting when you tried innertext.

    • Social Service Coordination Council
      Social Service Coordination Council 6 months ago

      +Automation Made Easy Ashu Vhai, Below My IE.Browser code is

      Something I mistake like disabled=""
      Please Send me the code again, because it`s not working, what you sent. PLZ Help me.

    • Automation Made Easy
      Automation Made Easy  6 months ago

      strVal = doc.getElementById("studname").innerText
      ThisWorkbook.Sheets("Data").Range("d" & rowNo).Value = strVal

  • khaled shorbagy
    khaled shorbagy 6 months ago

    very good work , how can close every web site it opened by code after writing in excel

    • Automation Made Easy
      Automation Made Easy  6 months ago

      Refer the tutorial in the link given below:
      ru-clip.net/video/vnIsNa4de7M/video.html

    • khaled shorbagy
      khaled shorbagy 6 months ago

      dear ashu auto ;macro run now but add one row data only
      i want add row no 1 then row no 2 then row no 3 etc
      from data sheet to web form

    • khaled shorbagy
      khaled shorbagy 6 months ago +1

      +Automation Made Easy good thanks, you are perect man
      it now working

    • Automation Made Easy
      Automation Made Easy  6 months ago

      Replace line Dim IE As Object with Dim IE As InternetExplorerMedium and
      Set IE = CreateObject("InternetExplorer.Application") with Set IE = New InternetExplorerMedium

      Note: On the macro wndow go to Tool\References and make sure Microsoft HTML Object library and Microsoft Internet Controls are selected.

    • khaled shorbagy
      khaled shorbagy 6 months ago

      ​+Automation Made Easy it working scince month but it stop working two days suddenly
      I'm not sure why this suddenly stopped working. .... But I keep getting the error, message:"Method 'Document' of object "IWebBrowser2' failed"
      and somtime error message: "automation error the object invoked has disconnected from its clients vba"
      please help me to run macro

  • Fiona M
    Fiona M 6 months ago

    Great tutorial! Is it possible to set the range to run until the last populated cell row?

    • Automation Made Easy
      Automation Made Easy  6 months ago

      Send your excel file at easilyautomateashu@gmail.com. I will update and send you back.

    • Fiona M
      Fiona M 6 months ago

      Ashu Auto Thanks for your reply, I did try that but get an error at the line
      For i = 1 to Count

    • Automation Made Easy
      Automation Made Easy  6 months ago

      Yes. Add below lines of code in your macro code:
      ThisWorkbook.Sheets("Plan1").Range("AZ1") = "=countA(A:A)"
      Count = ThisWorkbook.Sheets("Your sheet Name").Range("AZ1").Value
      For i= 1 to Count
      Next
      Notes:
      .1. I have used the cell AZ1, You can use any unused cell. Preferably on the extreme right hand side.
      2. I have applied formula on column 'A', You should use the column with most populated rows. In most cases it would be the first column.
      Let me know if you have any other questions.

  • Harish Thakur
    Harish Thakur 7 months ago +4

    Nice tutorial.... everything works perfectly on my first try.

  • pancux670
    pancux670 7 months ago +1

    Sir.. its awsome. Pls provide excel macro in description. So that we can use it

  • Madhukar Jadhav
    Madhukar Jadhav 7 months ago

    Hey hi....really interesting things u doing with Excel.
    I would like to know if this kind of data scrapping is possible with websites that require login or data is password protected?

    • Automation Made Easy
      Automation Made Easy  7 months ago

      i do not have access to zerodha site, so i cannot comment without analyzing it.

    • Madhukar Jadhav
      Madhukar Jadhav 7 months ago

      actually its dropdown list

    • Madhukar Jadhav
      Madhukar Jadhav 7 months ago

      hey hi thanks for reply
      i could use getElementById on Fb log in page but
      as i said
      could not use it in zerodha kite website even after log in, by using id element

      pls help

    • Automation Made Easy
      Automation Made Easy  7 months ago

      yes it is possible. you will have login using macro code and from there you can fetch the data.

  • Mind Buzzing Articles
    Mind Buzzing Articles 7 months ago

    great work

  • Veerabhaktula Sudeep
    Veerabhaktula Sudeep 7 months ago

    What if there is no id in the input tag?
    Which attribute has to be used?

    • Pratham Ambre
      Pratham Ambre 3 months ago

      Can we use type or class? Name is missing

    • Automation Made Easy
      Automation Made Easy  7 months ago

      Any attribute through which the object can be uniquely identified. You can use the 'name' attribute as well.

  • J M RAO ASSOCIATES
    J M RAO ASSOCIATES 8 months ago

    instead of getting data from all rows like 2 to 5, how to get data from specific row??

    • Automation Made Easy
      Automation Made Easy  8 months ago

      If macros are used coding is mandatory. Without coding you may use excel formulae.

    • J M RAO ASSOCIATES
      J M RAO ASSOCIATES 8 months ago

      without going to coding part..
      selection of range shall be possible in excel with command button or with anything else like that??

    • Automation Made Easy
      Automation Made Easy  8 months ago

      You will have to mention the Range in your code as given below:
      ThisWorkbook.Sheets("Sheet2").Range("B4").Value
      B is the column and 4 is the Row number.
      Let me know if you have any other questions.

  • Akash Gupta
    Akash Gupta 9 months ago

    Runtime error 429 ... Solution for that please.
    Active x component cant create object

    • Automation Made Easy
      Automation Made Easy  9 months ago

      On your developer window go to Tools > References menu and check if Microsoft Excel Object Library and the Microsoft Office Object Library are selected.