Get web page data into Excel using VBA
- 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:
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/
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