Recent Posts

Archive

Tags

Intro Web Scraping with Excel VBA

Excel has a few different ways to import data from various sources, including web content. If you visit the DATA TAB, found within the Excel ribbon, you'll find various import features.

The GET & TRANSFORM tools provide a quick, easy and powerful method of importing data. But, when dealing with web content, you may find using a little VBA magic is better suited to extract the data you are looking for.

Below you will find a quick introduction to using Excel VBA to scrap data from a web page.

My Blog won't let me upload an Excel file with macros, so you will find the code used below...

Sub CountryPopList() ' declare the variables Dim ieObj As InternetExplorer Dim htmlEle As IHTMLElement Dim i As Integer ' initialize i to one i = 1 ' create and get access to an instance of IE Set ieObj = New InternetExplorer ieObj.Visible = True ieObj.navigate "https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_population" ' give the webpage some time to load all content Application.Wait Now + TimeValue("00:00:05") ' loop through all the rows in the table For Each htmlEle In ieObj.document.getElementsByClassName("wikitable")(0).getElementsByTagName("tr") With ActiveSheet .Range("A" & i).Value = htmlEle.Children(0).textContent .Range("B" & i).Value = htmlEle.Children(1).textContent .Range("C" & i).Value = htmlEle.Children(2).textContent .Range("D" & i).Value = htmlEle.Children(3).textContent .Range("E" & i).Value = htmlEle.Children(4).textContent End With i = i + 1 Next htmlEle End Sub

FOLLOW

  • Facebook

CONTACT

ADDRESS

Pleasant Grove, UT

©2017 by Office Newb