HomeОбразованиеRelated VideosMore From: Dinesh Kumar Takyar

Get web page data into Excel using VBA

502 ratings | 162884 views
Our Excel training videos on YouTube cover formulas, functions and VBA. Useful for beginners as well as advanced learners. New upload every Thursday. For details you can visit our website: http://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: http://www.exceltrainingvideos.com/complete-automation-of-getting-web-page-data-into-excel-worksheet-using-vba/ Interesting Links: http://www.tushar-mehta.com/publish_train/xl_vba_cases/vba_web_pages_services/index.htm http://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: http://amzn.to/2kDP35V If you are from India you can get this book here: http://amzn.to/2jzJGqU
Html code for embedding videos on your blog
Text Comments (93)
Krishnendu (2 months ago)
Grabbing data in Node js is much easier than this.
Dinesh Kumar Takyar (2 months ago)
Urs Bumann (5 months ago)
You opened my eyes, thank you!
Sudhakar Prathipati (7 months ago)
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 (10 months 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 (1 year ago)
Hi sir what i do if when right click on website but can't see view source?
Dinesh Kumar Takyar (1 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 (1 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 (1 year ago)
web page is not opening in india as yu shown in video
Essam Adel (1 year ago)
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 <input name="CivilIdAdd" class=textbox" id="CivilIdAdd" Onkeypress"fireButtonClick('civil_exist_btn').focus ();" type="text" maxlength="12" value=""></input> And button source is <input name="civil_exist_btn" calss="cssButtonSmall" Id="civil_exist_btn" onclick="resetmsginadd(); return validatemyform();;if(typeofwindow.clearformhiddenprograms_myform!='underfined') {Clearformhiddenparams_myform('myform');}" type="submit" Value="..."></input>
Dinesh Kumar Takyar (1 year ago)
Search https://www.exceltrainingvideos.com
Abdullah Quhtani (1 year ago)
Could you upload the get-data-into-excel-using-vba.xlsm to your site or send it to my email plz
Indian Mechanic (1 year ago)
Sir, very detailed and easy to understand! Thank you so much!
Prakash Praaku (1 year 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 (1 year ago)
RAVI ROSHAN (1 year ago)
Hi sir, why the dot operator doesn't work when using (.getElementsByname/.getElementsByid) how to make it work. Thanks
Dinesh Kumar Takyar (1 year ago)
Get the book Excel 2016 Power Programming with VBA: http://amzn.to/2kDP35V If you are from India you can get this book here: http://amzn.to/2jzJGqU Or visit https://www.exceltrainingvideos.com to learn more for free.
RAVI ROSHAN (1 year 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 (1 year ago)
getElementsByName returns a collection of elements. Did you mean getElementByid?
Santosh Singh (1 year ago)
How to convert word file to pdf.Please help me
Dinesh Kumar Takyar (1 year ago)
Save the Word file as PDF!
Santosh Singh (1 year 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 (1 year ago)
Thank you!
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 (2 years ago)
Check out this link: http://www.exceltrainingvideos.com/scraping-web-page-tables-data-using-vba/ Or search http://www.exceltrainingvideos.com
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 (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 (2 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 (2 years ago)
You can view this link for help: http://www.exceltrainingvideos.com/tag/how-to-follow-link-on-web-page-automatically-with-vba/ Alternatively, you can search my website exceltrainingvideos.com
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...
Dinesh Kumar Takyar (2 years ago)
+SANGAMESH MANNAPUR Watch my other videos.
animartis (2 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 (2 years ago)
Hello. I have 1 question for your. can I grab emails of subscribers from web?
Aayushi Khandelwal (3 years ago)
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 (3 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 (3 years ago)
Sir,        The automatic button click is not working in https://maps.google.co.in/ . Please help. Thanking you,  Palash Laha
mayank goyal (3 years ago)
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 (2 years ago)
+mayank goyal  What was the solution to you problem please, because I have the same problem. Regards Wiel Hecker
Govarthan Palanisamy (3 years ago)
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 (3 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 (3 years ago)
+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 (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 (4 years ago)
nice resolution.... :P
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 http://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 (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 (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 <div id="reportStatusDiv">     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.     <a onclick="clearStatusDiv(toId);" href="http://**URL**></a>      to download the Operational Summary report. </div> Thanks in advance for your help
vamsi ramakrishna (4 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 http://www.jobs.com/ Can you help me on how to resolve this issue.. Thank you
vamsi ramakrishna (4 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 (4 years ago)
Very useful and very detailed explanation. Thanks for the video. Waiting for  more
Bao Dung Le (4 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!
Dinesh Kumar Takyar (4 years ago)
+Bao Dzung Le Item is treated as an array.
staaldak (4 years ago)
Please change the misleading name of this video.
soo ro (3 years ago)
+staaldak What you suggest : )
Dinesh Kumar Takyar (4 years ago)
R Griff (4 years ago)
How do I pull a picture from a company webpage.  In other words, I have a product ID and I want to search the company's webpage to match the product mini picture with the product ID.  I will need to do an automatic search list as I have over 1000K product ID's to match product mini picture.  
Fand gando (4 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 (4 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 (4 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 (3 years ago)
+extperplexa Where you able to fix this error. I am plagued by the same error.
alex D (4 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​​​​​ <div id="dvPowerUserFinder"> <b><a style="float: Right; color: Black; margin: 5px 10px 0px 0px;" target="_blank" href="PowerUserFinder.aspx">Power User Finder</a></b> </div> <div id="pagecontent"> <div id="formelements"> <div id="login"> <fieldset> <legend>LOGIN</legend> <table cellpadding="0" cellspacing="6" border="0" align="center" style="margin-left: 30px"> <tr valign="top"> <td style="width: 150px"> Please login below. </td> <td style="width: 200px"> <span class="error">( * Required fields. )</span> </td> </tr> <tr valign="top"> <td> <label> <span class="message">*</span> Domain \ User Name:</label> &nbsp; </td> <td> <input name="ctl00$ContentPlaceHolder1$txtUserName" type="text" onchange="javascript:setTimeout('__doPostBack(\'ctl00$ContentPlaceHolder1$txtUserName\',\'\')', 0)" onkeypress="if (WebForm_TextBoxKeyHandler(event) == false) return false;" id="ctl00_ContentPlaceHolder1_txtUserName" tabindex="1" type="text" /> </td> </tr> <tr valign="top"> <td> <label> <span class="message">*</span> Password: </label> </td> <td> <input name="ctl00$ContentPlaceHolder1$txtPassword" type="password" id="ctl00_ContentPlaceHolder1_txtPassword" tabindex="2" type="password" /> </td> </tr> <tr valign="top"> <td> <label> <span class="message">*</span> Role: </label> </td> <td> <select name="ctl00$ContentPlaceHolder1$drpRoles" id="ctl00_ContentPlaceHolder1_drpRoles" tabindex="3" style="width:175px;">
Matteo Giani (4 years ago)
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 (5 years ago)
I have replicated the code which you have used but the field properties were updated and button is not getting clicked  <button type="submit" class="btn">Search Now</button></div> I tried with both  .document.getElementsByType("submit").Click .document.getElementsByClassname("btn").Click both of this are not working please help me out
samir abaza (4 years ago)
this works Dim jobbutton jobbutton = .document.GetElementsByTagName("button") 'MsgBox jobbutton '.Document.GetElementByID("JobsButton").Click jobbutton.Click
JMeyN40 (5 years ago)
AWSOME!! TYVM. I was helpfull indeed!! 
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 http://www.mcx-sx.com/markets/Currency/Historical-Data/Pages/TradeHistory.aspx http://www.nseindia.com/products/content/equities/indices/historical_index_data.htm http://www.mcxindia.com/SitePages/BhavCopy.aspx looking forward to get reply from you...thanking you in advance
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 ??
David E Phelps (5 years ago)
I would like for you to contact me [email protected] I have a web site I'm trying to get data from but the data is in a picture format.. Thank you.
Dinesh Kumar Takyar (5 years ago)
Record a macro and check if it is possible to go to deeper pages.
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 (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 [email protected]
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 (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 (5 years ago)
Check out the links in the description. Also note that all websites don't allow data extraction.
ra_ul (5 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 (5 years ago)
Visit my website familycomputerclub-dot-com
Sanjeev Kumar (5 years ago)
hi, i want to import data from 99acres.com in excel can anyone help me how can i do it.
TheZlayMan (5 years ago)
Thank you, sir. Simple code and excelent explanation.
James Vance (5 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 (5 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 (5 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 (5 years ago)
" ISN'T IT " ... Cracks me up every time HAHA
mani lara (5 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: [email protected] my skype id johnlara51 i hop i get touch with you
Mandeep Chauhan (5 years ago)
Juliano R Manrique (5 years ago)
Really useful tips!
vivek krishna (5 years ago)
Thank you, very helpful.
Abi Saravanamuttu (5 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 (5 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 (5 years ago)
How do we import data running to some 60 pages into a excel sheet ?
Laxmi Enterprises (5 years ago)
Thanks for this video ... i need ur help..(its imp for me.) My contact no is 07504175530.. plz if u can ..
patuljica88 (5 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 (5 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 (5 years ago)
very informative..Thanks!
LoeblComServices (6 years ago)
Great job, thanks

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.