Complete Automation of Getting Web Page Data into Excel Worksheet Using VBA

In this video we show the complete automation of how to get data into an Excel worksheet using VBA.
1. We first study the website and find out the elements we’ll need to access a form and the subsequent results. When you study the web page’s HTML source code you’ll note that the actual results are wrapped up in DIV containers.
2. Next we write the VBA code We use the getElementById method to get a reference to a single object and the getElementsByTagName method to get a collection of all the elements. Next we loop through all the elements and get the text properties or data (‘innertext’) of all the elements we wish to have. Our code instantiates our web browser (Internet Explorer) and navigates to the URL of our choice and then helps to get or extract the data using events. We also ensure that the code is placed in appropriate columns and rows so that any further analysis is made easy. Finally we use a recorded macro to format the data to make it more presentable to the human eye. Note: If the class names or other HTML code changes on a web page you’ll need to modify your VBA code accordingly to scrape the data correctly.
The macro code to get web page data into an Excel worksheet using VBA is given below:
Sub test()
Dim eRow As Long
Dim ele As Object
Set sht = Sheets(“Sheet1”)
RowCount = 1
sht.Range(“A” & RowCount) = “Title”
sht.Range(“B” & RowCount) = “Company”
sht.Range(“C” & RowCount) = “Location”
sht.Range(“D” & RowCount) = “Description”
eRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Set objIE = CreateObject(“InternetExplorer.Application”)
myjobtype = InputBox(“Enter type of job eg. sales, administration”)
myzip = InputBox(“Enter zipcode of area where you wish to work”)
With objIE
.Visible = True
.navigate “”
Do While .Busy Or _
.readyState <> 4
Set what = .document.getElementsByName(“q”)
what.Item(0).Value = myjobtype
Set zipcode = .document.getElementsByName(“where”)
zipcode.Item(0).Value = myzip
Do While .Busy Or _
.readyState <> 4
For Each ele In .document.all
Select Case ele.classname
Case “Result”
RowCount = RowCount + 1
Case “Title”
sht.Range(“A” & RowCount) = ele.innertext
Case “Company”
sht.Range(“B” & RowCount) = ele.innertext
Case “Location”
sht.Range(“C” & RowCount) = ele.innertext
Case “Description”
sht.Range(“D” & RowCount) = ele.innertext
End Select
Next ele
End With
Set objIE = Nothing
End Sub
Sub Macro1()

‘ Macro1 Macro
‘ Formatting imported data

With Selection
.VerticalAlignment = xlTop
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Columns(“D:D”).ColumnWidth = 50
End Sub

Watch the training video below to see how the process of complete automation of data collection from a web page into an Excel spreadsheet is implemented:

Watch the video on youtube

Further reading:
Excellent article on web services using VBA