Scrape or Pull Data Automatically from Websites into Excel

Home More Videos


You can scrape, pull or get data from websites into Excel by performing a few simple steps.
1. record a macro to find out how one or many tables or data can be scraped from the website
2. Study the code carefully
3. Create an Excel sheet containing the links that get you the data from the appropriate web pages
4. Automate the process using a loop that creates a) New worksheets b) Automatically changes the link to the web pages that have the required data

Study the Excel VBA code and watch the training video below to see how the automation of scraping of websites is implemented:

Sub adds()
For x = 1 To 5
Worksheets("states").Select
Worksheets("states").Activate
mystr = "URL;http://quickfacts.census.gov/qfd/states/01000.html"
mystr = Cells(x, 1)
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = x
With ActiveSheet.QueryTables.Add(Connection:= mystr, Destination:=Range( "$A$2"))
'CommandType = 0
.Name = "01000_1"
.FieldNames = True
.RowNumbers = False 
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "3,4,5"  '---> Note: many tables have been selected for import from the website
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Next x
End Sub




Watch the video on youtube