FOLLOW

CONTACT

ADDRESS

Pleasant Grove, UT

©2017 by Office Newb

UdemyNewYearPromo.jpg
PaparazziAd-01.jpg

Intro Web Scraping with Excel VBA

December 20, 2018

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

 

Please reload

Recent Posts

Please reload

Archive

Please reload

Tags

Please reload