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:
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/
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
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
But none of those work
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"
();" 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')
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,
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.
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.
. 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
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"
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"
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?
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...
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
For Each ele In .document.all
Select Case ele.classname
RowCount = RowCount + 1
sht.Range("A" & RowCount) = ele.innertext
sht.Range("B" & RowCount) = ele.innertext
sht.Range("C" & RowCount) = ele.innertext
sht.Range("D" & RowCount) = ele.innertext
Set ObjIE = Nothing
.VerticalAlignment = xlTop
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
Columns("D:D").ColumnWidth = 50
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..."
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
Thanks for the reply.
I have one requirement , could you help me with that.
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.
+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!
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.
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?
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
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.
Thanks in advance for your help
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..
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)
i = i + 1
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.
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?
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
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
<b><a style="float: Right; color: Black; margin: 5px 10px 0px 0px;" target="_blank"
href="PowerUserFinder.aspx">Power User Finder</a></b>
<table cellpadding="0" cellspacing="6" border="0" align="center" style="margin-left: 30px">
<td style="width: 150px">
Please login below.
<td style="width: 200px">
<span class="error">( * Required fields. )</span>
<span class="message">*</span> Domain \ User Name:</label>
<span class="message">*</span> Password:
<input name="ctl00$ContentPlaceHolder1$txtPassword" type="password" id="ctl00_ContentPlaceHolder1_txtPassword" tabindex="2" type="password" />
<span class="message">*</span> Role:
<select name="ctl00$ContentPlaceHolder1$drpRoles" id="ctl00_ContentPlaceHolder1_drpRoles" tabindex="3" style="width:175px;">
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!!
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
both of this are not working please help me out
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....??????
looking forward to get reply from you...thanking you in advance
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.
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
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
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
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...
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!
The Pornhub team is always updating and adding more porn videos every day. Its all here and 100% free porn. We have a huge free DVD selection that you can download or stream. Pornhub is the most complete and revolutionary porn tube site. We offer streaming porn videos, downloadable DVDs, photo albums, and the number 1 free sex community on the net. Were always working towards adding more features that will keep your love for porno alive and well. Send us feedback if you have any questions/comments.
From the depths of the Internet, from all over the world, we have collected the sweetest episodes of teen debauchery! Young, innocent and alluring, those babes are the greatest treasure a man can have in his bedroom. Reality porn videos that we share expose the hottest 18 years old teens in mouthwatering scenes of anal, group and lesbian sex. Brunettes and blondes, good girls and salted sluts, big boobs and small tits, tight pussies and wide assholes, footjobs and blowjobs - everything is possible with the gorgeous teens. Watch rude and wild sex, leaving teen pussies glowing red from hard pounding, assholes gaping after thick cocks, mouths oozing with cum when they take the cumshot! Check, how deep can those little angels fall in pursuit of money! Learn how to pick them up on the streets of your city and convince them to show their titties and suck you cock! Dozens of dozens of real 18-year-old teenager stories and scenes are waiting for you in the high-quality videos, depicting the young girls in hot action!