Webpage Scraper VBA

This is a website scraper; it will download an entire webpages text and insert it into cells on a sheet. Excel

In Cell C1 there is drop down with the relevant options news, weather, sport etc. The user would select which page they require to scrap and the VBA would run the following code and paste the results (webpage) onto Sheet1 starting in Cell A1.

Bold parts to be changed for your needs.

Sub WebsiteScraper()

Sheets(“Sheet1“).Select

With ActiveSheet.QueryTables.Add(Connection:= _

“URL;http://www.bbc.co.uk/” & Range(“C1”) & “/” _

, Destination:=Range(“$A$1“))

‘ .CommandType =

.Name = “BBC

.FieldNames = True

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.BackgroundQuery = True

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.WebSelectionType = xlEntirePage

.WebFormatting = xlWebFormattingNone

.WebPreFormattedTextToColumns = True

.WebConsecutiveDelimitersAsOne = True

.WebSingleBlockTextImport = False

.WebDisableDateRecognition = False

.WebDisableRedirections = False

.Refresh BackgroundQuery:=False

End With

End Sub

Results look like this –

This is a simple example but can be adapted for your own requirements, then once the raw data is in Excel this can be manipulated into pivot tables and graphs, especially if the website you need is number heavy.

Enjoy