Get web page data into Excel using VBA

Share
Embed
  • Published on Nov 10, 2012
  • Our Excel training videos on RU-clip cover formulas, functions and VBA. Useful for beginners as well as advanced learners. New upload every Thursday.
    For details you can visit our website:
    www.exceltrainingvideos.com/complete-automation-of-getting-web-page-data-into-excel-worksheet-using-vba/
    In this video we show the complete automation of how to get data into an Excel worksheet using VBA.
    1. We first study the website and find out the elements we'll need to access a form and the subsequent results. When you study the web page's HTML source code you'll note that the actual results are wrapped up in DIV containers.
    2. Next we write the VBA code
    We use the getElementById method to get a reference to a single object and the getElementsByTagName method to get a collection of all the elements. Next we loop through all the elements and get the text properties or data ('innertext') of all the elements we wish to have.
    Our code instantiates our web browser (Internet Explorer) and navigates to the URL of our choice and then helps to get or extract the data using events.
    We also ensure that the code is placed in appropriate columns and rows so that any further analysis is made easy.
    Finally we use a recorded macro to format the data to make it more presentable to the human eye.
    You can view the complete code at: www.exceltrainingvideos.com/complete-automation-of-getting-web-page-data-into-excel-worksheet-using-vba/
    Interesting Links:
    www.tushar-mehta.com/publish_train/xl_vba_cases/vba_web_pages_services/index.htm
    officevbavsto-en.blogspot.com.br/2012/06/vba-internet-acessing-web-pages-through_15.html?m=1
    Get the book Excel 2016 Power Programming with VBA: amzn.to/2kDP35V
    If you are from India you can get this book here: amzn.to/2jzJGqU

Comments • 93

  • سامي العيدروس

    You are a great person

  • Krishnendu
    Krishnendu 9 months ago

    Grabbing data in Node js is much easier than this.

  • Urs Bumann
    Urs Bumann 11 months ago

    You opened my eyes, thank you!

  • Sudhakar Prathipati

    Hi Sir, i want to import some reports from the dashboard to excel , which was created for project monitoring. The dashboard is protected with 'User Name' & 'Password' and i am having the same.

  • Emilio Tzum
    Emilio Tzum Year ago

    I'm on a site that has several tabs. i can click on it but each clickable tab has no id, name or class.
    It just has:
    Title ("Music Tab")
    I have tried
    IE.Document.getElementsByTitle("ElementName")(0).click
    IE.Document.getElementsByTitle("ElementName")(0).submit
    IE.Document.getElementsByTitle("ElementName")(0).select
    IE.Document.getElementsByTitle("ElementName").click
    IE.Document.getElementsByTitleName("ElementName")(0).click
    But none of those work

  • Essam Adel
    Essam Adel Year ago

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

    • Dinesh Kumar Takyar
      Dinesh Kumar Takyar  Year ago

      Can't do much if it's not your website. You can use Google Chrome to view the source or Microsoft Edge.

  • Zulkarnaen
    Zulkarnaen Year ago

    sir.... i have 600 company names, and i need to find website name and profile that each companies....how to get excel VBA..

  • santosh loka
    santosh loka Year ago

    web page is not opening in india as yu shown in video

  • Essam Adel
    Essam Adel Year ago +1

    Sir , can you help me after open website I want check for id from excel to website
    Source in website is
    Text box in website this is source

  • Abdullah Quhtani
    Abdullah Quhtani 2 years ago

    Could you upload the get-data-into-excel-using-vba.xlsm to your site or send it to my email plz

  • Indian Mechanic
    Indian Mechanic 2 years ago

    Sir, very detailed and easy to understand! Thank you so much!

  • Prakash Praaku
    Prakash Praaku 2 years ago

    Hello sir,
    how can we get mail content data into excel file using VBA, the mail content format will be like, Date, Company Name, Total Expenses, Spending Date, Location.
    These are the headers, and below this there will be some 20 rows of data,
    How to get this into Excel and save in a specific folder with the mail subject line name automatically,
    This will be a great help sir,
    Regards,
    Prakash,
    Bangalore

  • abdul khalek
    abdul khalek 2 years ago

    hi

  • RAVI ROSHAN
    RAVI ROSHAN 2 years ago

    Hi sir,
    why the dot operator doesn't work when using (.getElementsByname/.getElementsByid)
    how to make it work.
    Thanks

    • Dinesh Kumar Takyar
      Dinesh Kumar Takyar  2 years ago

      Get the book Excel 2016 Power Programming with VBA: amzn.to/2kDP35V
      If you are from India you can get this book here: amzn.to/2jzJGqU
      Or visit www.exceltrainingvideos.com to learn more for free.

    • RAVI ROSHAN
      RAVI ROSHAN 2 years ago

      thanks for the reply, sir i mean when i start typing IntExpl.Document.getElements... why dont i see a drop down list of getelementsbyid getelementsbyname and so on, sorry if my question is silly i am new to vba.

    • Dinesh Kumar Takyar
      Dinesh Kumar Takyar  2 years ago

      getElementsByName returns a collection of elements.
      Did you mean getElementByid?

  • Santosh Singh
    Santosh Singh 2 years ago

    How to convert word file to pdf.Please help me

  • Santosh Singh
    Santosh Singh 2 years ago

    . Accepts a set of input parameters
    • Selects one of the two provided Word templates
    • Replaces the marked fields in the templates with the provided values in input parameters
    • Saves the generated document as Word and PDF in a given folder

  • Stiff Wit
    Stiff Wit 2 years ago

    Thank you!

  • Priyatma Kumari
    Priyatma Kumari 2 years ago

    hello Sir,I have a html page and when I view source, I find many tables under tagname 'table', I want innertext to be printed which is under tagname td. How can I achieve this.

    • Dinesh Kumar Takyar
      Dinesh Kumar Takyar  2 years ago

      Check out this link: www.exceltrainingvideos.com/scraping-web-page-tables-data-using-vba/
      Or search www.exceltrainingvideos.com

  • Vikash Kumar
    Vikash Kumar 2 years ago

    Hi Sir, Could you please let me know that how can i copy linked in search results, and after click next button and again copy search results, via vba codes. i have identified the div id of source page "results-container"

  • UniqueIndia
    UniqueIndia 2 years ago

    Hi Sir, Could you please let me know that how can i copy linked in search results, and aftre click next button and again copy search results. via vba codes. i have identified the div id of source page "results-container"

  • Sami Uddin
    Sami Uddin 3 years ago

    dear Sir how can I automate the process in such a way that when results are in a big number and are not visible on one page only, and the page provides pagination to follow the results, pages one by one?

    • Dinesh Kumar Takyar
      Dinesh Kumar Takyar  3 years ago

      You can view this link for help: www.exceltrainingvideos.com/tag/how-to-follow-link-on-web-page-automatically-with-vba/
      Alternatively, you can search my website exceltrainingvideos.com

  • SANGAMESH MANNAPUR
    SANGAMESH MANNAPUR 3 years ago

    Hello Sir,I am beginner for creating macro, Can you please suggest which video is preferred to start.Along with I have two spread sheet in to compare and find the record is existing in master file or not. If not then need to update the data in master file, If the record is available it is just ignore it.Note: two spread sheets are in two different format ex: .Xls and XLM etc...

  • animartis
    animartis 3 years ago

    Thanks for the video, very helpful. I have successfully filled out a web form, but when I click SUBMIT it doesn't recognize that the form has been filled out. It gives me an error on every text box and/or combo box saying that the field is empty.I believe it is using javascript. Any suggestions?

  • Denka Mkrtchyan
    Denka Mkrtchyan 3 years ago

    Hello. I have 1 question for your. can I grab emails of subscribers from web?

  • Aayushi Khandelwal
    Aayushi Khandelwal 4 years ago +3

    Sir,Can you please suggest any way how to extract data that is spread over many pages .Even in the same website jobs.com results come in many pages

  • Aayushi Khandelwal
    Aayushi Khandelwal 4 years ago

    I followed all your steps and I created 2 modules in Excel having the following content.Everything went fine .I also changed the classes name
    Module 1
    For Each ele In .document.all
    Select Case ele.classname
    Case "CardView"
    RowCount = RowCount + 1
    Case "JobTitle"
    sht.Range("A" & RowCount) = ele.innertext
    Case "Company"
    sht.Range("B" & RowCount) = ele.innertext
    Case "Location"
    sht.Range("C" & RowCount) = ele.innertext
    Case "Preview"
    sht.Range("D" & RowCount) = ele.innertext
    End Select
    Next ele
    End With
    Macro1
    Set ObjIE = Nothing
    End Sub

    Module 2
    Sub Macro1()
    Columns("A:D").Select
    Selection.Columns.AutoFit
    With Selection
    .VerticalAlignment = xlTop
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    End With
    Range("D1").Select
    Columns("D:D").ColumnWidth = 50
    Columns("A:D").Select
    Selection.Rows.AutoFit
    End Sub

  • Palash Laha
    Palash Laha 4 years ago

    Sir,
    The automatic button click is not working in maps.google.co.in/ . Please help.
    Thanking you,
    Palash Laha

  • mayank goyal
    mayank goyal 4 years ago +1

    Hello Sir, thanks a lot for the wonderful explanation. But when I run the script I get an error which says "Runtime error, Automation error, unspecified error". I don't know why.. Do you have any idea? This error comes when it comes onto the line "Do while .Busy..."

    • Wiel h01
      Wiel h01 3 years ago

      +mayank goyal What was the solution to you problem please, because I have the same problem. Regards Wiel Hecker

  • Govarthan Palanisamy
    Govarthan Palanisamy 4 years ago +1

    HI Sir,
    I Tried the above code, was getting error for the the "JobSearch" button. The used the code given by vamsi ramakrishna in the below comments. Now i am able to input data and click the buton. Even the webpage is displaying the search results. But the problem i have now is those results data is not getting populated in the excel. Can you please help me out in this...
    Thanks In Advance
    Govarthan

    • Govarthan Palanisamy
      Govarthan Palanisamy 4 years ago

      Hi sir,
      Thanks for the reply.
      I have one requirement , could you help me with that.
      Requirment is.
      I have sheet1 with dB quieries like "source query","target query". I have written code to run this query and fetch satay from dB and populate in new sheets of same workbook with respective names like sheet2 as source data ( which will have data for the source query) like this same trot target as well. And sheet5 will have the comparison results. Till this everything is coded. This is sheet1 will have multiple rows so the process I have mentioned above will runn I'm a loop for as much rows sheet1 have. So I want for every loop I want to take the copy of source satay ssheet , target datasheet and comparison sheet and save it in a new excel with name result1, result2 .... Etc. I need help in looping and saving the results... I am new for vba....
      Thanks in advance.
      Regards,
      Govarthan

    • Dinesh Kumar Takyar
      Dinesh Kumar Takyar  4 years ago +1

      @Govarthan Palanisamy The code to extract or scrape data from a web page depends on the web page. Mostly web pages or websites are not static creatures. If the web page content and code change, you need to take this into account!

  • HollywoodCreeper
    HollywoodCreeper 4 years ago

    I wrote a long thing in a comment box at the www.rollingstone.com website using Firefox for macbook as the browser. Everything was going slowly and a then a window popped up saying a script was not responding or was busy and I am wondering if it is possible to recover the text I wrote in the box? I downloaded a hxD editor, but I just get stuff on the right about the URL. There were no things in the upper left like 'file' for Firefox, but now they are there but not responding. Firefox is totally not responding, but there are a lot of other firefox browser windows minimized that are open and still at the bottom of the screen, but also not responding. How do you get into Firefox and recover the text? While I was writing the text on the Rolling Stone website, the writing within the tab would show that it had to connect to the internet to enter the text. When I went to the site I entered the text, it was not saved on the site. I can see some of the text I wrote on the frozen Firefox screen, but nothing moves. I am sure the text I wrote it still there if I could get through. How do I get into the Firefox webpage? All the other applications are working on my MacBook.

  • TheSkyPug
    TheSkyPug 4 years ago +1

    nice resolution.... :P

  • Don Garry
    Don Garry 4 years ago

    Hello Dinesh, thank you for your video which I have working on your sample site. The part I'm struggling with is when I go to wwwb.autotrader.ca/ to try to filter the search results programmatically. Would it be possible for you to look at that site and let me know if it would be possible to scrape data from it?

  • Remo Lontok
    Remo Lontok 4 years ago

    Hello, Can you help me? I have a project in school, can i use that in different website? like yahoo, google, yellow pages? Thank you sir

  • Mohan Krishna V
    Mohan Krishna V 4 years ago

    Hello Dinesh Sir,Need a help from you..I am working on a automation to download a report.I get stuck when I reach this step
    When ever I click on a button the website processes a report and gives me a link..I need to download the file i get from the hyper link.
    This is how the html code looks when its getting processed

    Processing ..
    As soon as the processing is complete that means the report is ready and below code becomes visible in html.and this is the link I need to use to download the report.

  • vamsi ramakrishna
    vamsi ramakrishna 5 years ago

    Hi.. It is very helpful video.
    But when I followed the tutorial, I found that Id for the Submit button is missing in the web page www.jobs.com/
    Can you help me on how to resolve this issue..
    Thank you

    • vamsi ramakrishna
      vamsi ramakrishna 5 years ago

      Resolved.
      Used the below script to click the submit button.
      Dim objCollection As Object
      Dim objElement As Object
      Set objCollection = ObjIE.document.getElementsByTagName("button")
      i = 0
      While i < objCollection.Length
      If objCollection(i).Type = "submit" Then
      ' "Search" button is found
      Set objElement = objCollection(i)
      End If
      i = i + 1
      Wend
      objElement.Click

  • vamsi ramakrishna
    vamsi ramakrishna 5 years ago

    Very useful and very detailed explanation. Thanks for the video. Waiting for more

  • Bao Dung Le
    Bao Dung Le 5 years ago

    Thank you for the very helpful video.
    I have some questions:
    - what is item(0)?
    - don't you have to declare what and zipcode variables?
    Thank you!

  • staaldak
    staaldak 5 years ago

    Please change the misleading name of this video.

  • Fand gando
    Fand gando 5 years ago

    This code doesn't work even with the button modification the website is not helpful for this specific piece of code :(

  • Sai Pavan Yaraguti
    Sai Pavan Yaraguti 5 years ago

    Is there an alternative to VBA in which this could be done? It seems to be an old language. I bet there would have been more languages constructed upon this.

  • extperplexa
    extperplexa 5 years ago

    I need help with your code. After creating the Internet Explorer Object, whenever I try to access the document, e.g. objIE.document.getElementsByName("q"), I get ERROR 438 - Object doesnt support property or method.
    I've alredy included libraries HTML Object Library and Internet Controls.
    What must I do?
    Thanks!

    • Señor Sawdust
      Señor Sawdust 3 years ago

      +extperplexa Where you able to fix this error. I am plagued by the same error.

  • alex D
    alex D 5 years ago

    Hi Dr Takyar,
    Thank you for providing the example on how to auto login via VBA url.
    I’m running into an issue with my code I identify the fields that I need. When I enter these values to the code that you provide it doesn’t populate the boxes. I hope you can be able to help me resolve this issue.
    I sincerely appreciate your assistance
    Alex
    I enter this to the VBA script.
    HTMLDoc.all.UserName.Value = "RNBKXX"
    HTMLDoc.all.Password.Value = "XXXXXX" 'Enter your password here
    HTMLDoc.all.role.Value = "Spoc" 'Role
    Below is the IE code​​​​​

    Power User Finder





    LOGIN



    Please login below.


    ( * Required fields. )





    * Domain \ User Name:
     








    * Password:









    * Role:

  • Matteo Giani
    Matteo Giani 5 years ago +1

    Awesome! Can I just ask you.... Imagine on jobs.com you also want the data PHONE and EMAIL and imagine these are shown only if you open the link of every single ad. Now, as it would take loads of time running the script on every si gle listing page would that be possible using this script? I'm thinking of doing this on olx.com thank you so much!!

  • Sravan Kumar
    Sravan Kumar 5 years ago

    I have replicated the code which you have used but the field properties were updated and button is not getting clicked
    Search Now
    I tried with both
    .document.getElementsByType("submit").Click
    .document.getElementsByClassname("btn").Click
    both of this are not working please help me out

    • samir abaza
      samir abaza 5 years ago

      this works
      Dim jobbutton
      jobbutton = .document.GetElementsByTagName("button")
      'MsgBox jobbutton
      '.Document.GetElementByID("JobsButton").Click
      jobbutton.Click

  • JMeyN40
    JMeyN40 5 years ago

    AWSOME!! TYVM. I was helpfull indeed!!

  • Praveen kumar begwani
    Praveen kumar begwani 5 years ago

    Sir i saw few of your videos and they are quite good....since i am new for any programming languages.... i want you to do me a favour ..... i want to extract historical prices from following links to my excel with specific name and expiry....can you help me with....??????
    links are
    www.mcx-sx.com/markets/Currency/Historical-Data/Pages/TradeHistory.aspx
    www.nseindia.com/products/content/equities/indices/historical_index_data.htm
    www.mcxindia.com/SitePages/BhavCopy.aspx

    looking forward to get reply from you...thanking you in advance

  • stockmarketsinindia
    stockmarketsinindia 5 years ago

    Is there any way out to place some dynamic data collected from a website through excel can be reproduced on any other different websites ? If possible, then, how ??

  • Dinesh Kumar Takyar
    Dinesh Kumar Takyar  5 years ago

    Record a macro and check if it is possible to go to deeper pages.

  • Sanjoy Banerjee
    Sanjoy Banerjee 5 years ago

    Excellent tutorial.. perhaps for the first time I am appreciating VB, which I always considered to inferior than Python especially for this... I would try to learn more on this.. However just few questions, what if the pages are two level deep ? and how to automate navigation.

  • Soumya jyoti guru
    Soumya jyoti guru 5 years ago

    Sir my major concern is on how do we pass date picker values, I tried the way below :
    Set Start_Date = doc.getElementsByName("ctl00_ContentPlaceHolder1_SearchFilter_PickerFrom_picker")
    Start_Date.Item(0).Value = DateSerial(2013, 7, 23)
    the way i am passing the date parameters appears to be like this
    7/23/2013 but i need it in this format July 23, 2013....
    sir can i get connected to you, this is my mail id
    somu.guru86@gmail.com

  • Dinesh Kumar Takyar
    Dinesh Kumar Takyar  5 years ago

    Go to familycomputerclubdotcom. Do a search from home page. There must be at least 2 videos on the subject. Replace dot with . in the link above.

  • Soumya jyoti guru
    Soumya jyoti guru 5 years ago

    I have a date picker which i need to update through vba....can you explain ,how can i achieve it???thanks in advance

  • Dinesh Kumar Takyar
    Dinesh Kumar Takyar  5 years ago

    Check out the links in the description. Also note that all websites don't allow data extraction.

  • ra_ul
    ra_ul 6 years ago

    Sir,
    The data which we need to copy to an excel sheet, if that data in the website is not in the form of a table, maybe some description, can we copy that using macros?

  • Dinesh Kumar Takyar
    Dinesh Kumar Takyar  6 years ago

    Visit my website familycomputerclub-dot-com

  • Sanjeev Kumar
    Sanjeev Kumar 6 years ago

    hi, i want to import data from 99acres.com in excel can anyone help me how can i do it.

  • TheZlayMan
    TheZlayMan 6 years ago

    Thank you, sir.
    Simple code and excelent explanation.

  • James Vance
    James Vance 6 years ago

    Just fwi, if anyone is getting an error with this
    eRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    changing it to
    eRow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    fixed it for me.
    Disclaimer: I changed code elsewhere

  • Dinesh Kumar Takyar
    Dinesh Kumar Takyar  6 years ago

    Depending on what you are trying to do you need to code carefully. Have a look at the links in the description.

  • Borja Chapero
    Borja Chapero 6 years ago

    I am trying to replicate your macro with the two moduls, however I do not suceed in getting the macro properly.
    What mistake am I making?
    Fdbchp

  • Ash Finkter
    Ash Finkter 6 years ago

    " ISN'T IT " ... Cracks me up every time HAHA

  • mani lara
    mani lara 6 years ago

    Sir I am John from bangalore I want some help in extracting data into excel from JSP link
    what is taks here Gateway database through date filter options for filter data and export to excel..... can i have your email id ? or contacts.... my email id is: excelexpert59@gmail.com my skype id johnlara51 i hop i get touch with you

  • Mandeep Chauhan
    Mandeep Chauhan 6 years ago

    Excellent!!!

  • Juliano R Manrique
    Juliano R Manrique 6 years ago

    Really useful tips!

  • vivek krishna
    vivek krishna 6 years ago

    Thank you, very helpful.

  • Abi Saravanamuttu
    Abi Saravanamuttu 6 years ago

    Thank you for the video! Exactly what I was looking for. I am currently using this method to extract information from a shares and investments website.

  • Neo Bisht
    Neo Bisht 6 years ago

    Greetings,
    Hi sir,
    I want to know how can i make folders in my 'C drive or share drive' through using VBA coding, For example; suppose i open excel 2007 and i entered few names like 'John, Martin, and Nelson in my excel sheet; So using vba i want that all three respective name folder should be created in my "C drive" automatically. Waiting for your video on this, Thanks for your help in advance...

  • Srinivasa Sarma
    Srinivasa Sarma 6 years ago

    How do we import data running to some 60 pages into a excel sheet ?

  • Laxmi Enterprises
    Laxmi Enterprises 6 years ago

    Thanks for this video ...
    i need ur help..(its imp for me.)
    My contact no is 07504175530..
    plz if u can ..

  • patuljica88
    patuljica88 6 years ago

    Great vid, but you only go trough first page, and you have more results than one page, how do you gat all results? Thanks

  • The Turdinator
    The Turdinator 6 years ago

    Hello, great vid, I have learned a lot and have one of my own macros working perfectly! I've made a macro to get Merriam-Webster's word of the day and definitions and what not, but I've run into a snag with the "Examples" section. There are two things, Examples then Trivia, that use the same tags. How would I get the second instance and ONLY the second instance of this? Any help would be greatly appreciated!
    Cheers!

  • matt ryan
    matt ryan 6 years ago

    very informative..Thanks!

  • LoeblComServices
    LoeblComServices 6 years ago

    Great job, thanks