Web Scraping using Excel VBA Macros and Internet Explorer

I learnt today how to do web scraping using VBA Macros. Web scraping means getting Textual Data from web pages. The following VBA code may help anyone.

It takes Website URL from Sheet1 of Excel Application where this Macro would reside. Website URLs are placed in a column the first cell of which is named “WebsiteURL” so it is accessed as a Named Cell. Make sure not to include anything else in the column below website URLs.  All the textual data is appended in a new Word Document and also saved in a text file in same folder as the Excel Sheet containing the macro. Note that The website you are scraping data from does not open up a popup and does not require secure authentication. Otherwise you can also incorporate authentication and popup ignoring code for IE Browser Object .

IE Logo
Sub DownloadAndSaveText()

Dim tempWordApp As New Word.Application
tempWordApp.Visible = True
Dim tempWordDoc As Word.Document
Set tempWordDoc = tempWordApp.Documents.Add(Visible:=True)
Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = False
Dim Url As String
'INITIALIZATION
Dim lastlinkrow As Long, firstlinkrow As Long, linkcolumn As Long, i As Long
lastlinkrow = Worksheets(1).Range("WebsiteURL").End(xlDown).Row
firstlinkrow = Worksheets(1).Range("WebsiteURL").Row
linkcolumn = Worksheets(1).Range("WebsiteURL").Column
For i = firstlinkrow To lastlinkrow
Url = Worksheets(1).Cells(i, linkcolumn).Value
'GET WEB PAGE
IE.navigate Url
Do While IE.readyState <> 4
    DoEvents
Loop

IE.Document.execCommand "SelectAll", False
tempWordDoc.Content.Text = tempWordDoc.Content.Text & vbCrLf & IE.Document.Selection.createRange().Text

Next

tempWordDoc.SaveAs ActiveWorkbook.Path & "\WebScrapedText", FileFormat:=wdFormatText
'tempWordDoc.Close
'tempWordApp.Quit
IE.Quit

End Sub
Excel Automation
Advertisements

11 thoughts on “Web Scraping using Excel VBA Macros and Internet Explorer”

  1. That is the appropriate blog for anybody who desires to seek out out about this topic. You notice a lot its nearly exhausting to argue with you (not that I actually would need…HaHa). You definitely put a brand new spin on a topic thats been written about for years. Nice stuff, simply great!

  2. Youre so cool! I dont suppose Ive learn anything like this before. So nice to find any individual with some original ideas on this subject. realy thanks for beginning this up. this web site is something that’s needed on the net, somebody with somewhat originality. useful job for bringing one thing new to the web!

  3. We’re a group of volunteers and opening a new scheme in our community. Your web site provided us with valuable information to work on. You have done an impressive job and our whole community will be grateful to you.

  4. especially prefer this Web Scraping using Excel VBA Macros and Internet Explorer Ahsan Nabi's Blog i’d guess that you is wishing for the marquez website.

  5. Pretty element of content. I just stumbled upon your
    site and in accession capital to assert that I get actually loved account your weblog posts.
    Any way I’ll be subscribing on your augment and even I fulfillment you get right of entry to consistently fast.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Data Journalism: Natural Language Processing, Predictive Analytics, Data Science

Advertisements
%d bloggers like this: