Recent Posts



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 "" ' 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